github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/recursive_cte/recursive_cte.result (about)

     1  drop table if exists t1;
     2  create table t1(a bigint primary key, b int unique key);
     3  insert into t1 values (1, 2), (3, 4), (5, 6);
     4  drop table if exists t3;
     5  create table t3(a bigint, b int);
     6  insert into t3 values (1, 2), (3, 4), (5, 6);
     7  drop table if exists t2;
     8  create table t2(a bigint, b int);
     9  insert into t2 values (1, 2), (3, 4), (5, 6);
    10  with recursive c as (select a from t1 union all select a+1 from c where a < 2) select * from c order by a;
    11  a
    12  1
    13  2
    14  3
    15  5
    16  with recursive c as (select a from t1 union all select a+1 from c where a < 200) select * from c;
    17  recursive level out of range
    18  with recursive c as (select a from t1 union all select c.a+1 from c, t1 as k1, t1 as k2 where c.a = k1.a and c.a = k2.a) select * from c order by a;
    19  a
    20  1
    21  2
    22  3
    23  4
    24  5
    25  6
    26  with recursive c as (select t1.a from t1, t2, t3 where t1.a = t2.a or t1.a = t3.a union all select a+1 from c where a < 6) select count(*) from c;
    27  count(*)
    28  60
    29  with recursive c as (select t1.a from t1, t2, t3 where t1.a = t2.a or t1.a = t3.a union all select c.a+1 from c, t2, t3 where c.a = t2.a and c.a = t3.a and c.a < 6) select count(*) from c;
    30  count(*)
    31  30
    32  with recursive c as (select t1.a from t1 union all select c.a+1 from c, t3 where c.a = t3.a and c.a < 2) select count(*) from c;
    33  count(*)
    34  4
    35  with recursive c as (select a from t1 union all select a+1 from c where a < 3 union all select a+1 from c where a < 4) select count(*) from c;
    36  count(*)
    37  14
    38  with recursive c as (select a from t1 union all select a+1 from c where a < 4) select * from c order by a;
    39  a
    40  1
    41  2
    42  3
    43  3
    44  4
    45  4
    46  5
    47  with recursive c as (select a from t1 union all select a+1 from c where a < 3) select * from c order by a;
    48  a
    49  1
    50  2
    51  3
    52  3
    53  5
    54  with recursive c as (select t1.a, 0 as level from t1 union all select t1.a, 0 as level from t1 join t2 on t1.a = t2.a where t1.a = 1 union all select c.a, c.level + 1 from c join t3 on c.a = t3.a where c.level < 6) select count(*) from c;
    55  count(*)
    56  28
    57  with recursive c as (select t1.a, 0 as level from t1 union all select t1.a, 0 as level from t1 join t2 on t1.a = t2.a where t1.a = 1 union all select c.a, c.level + 1 from c join t3 on c.a = t3.a where c.level < 6) select count(*) from c;
    58  count(*)
    59  28
    60  with recursive c as (select t1.a, 0 as level from t1 union all select t1.a, 0 as level from t1 join t2 on t1.a = t2.a where t1.a = 1 union all select c.a, c.level + 1 from c join t3 on c.a = t3.a where c.level < 6) select count(*) from c;
    61  count(*)
    62  28
    63  CREATE TABLE Person(ID int, Name VARCHAR(30), Mother INT, Father INT);
    64  INSERT Person VALUES(1, 'Sue', NULL, NULL),(2, 'Ed', NULL, NULL),(3, 'Emma', 1, 2),(4, 'Jack', 1, 2),(5, 'Jane', NULL, NULL),(6, 'Bonnie', 5, 4),(7, 'Bill', 5, 4);
    65  WITH recursive Generation (ID) AS (SELECT Mother FROM Person WHERE Name = 'Bonnie' UNION SELECT Father FROM Person WHERE Name = 'Bonnie' UNION ALL SELECT Person.Father FROM Generation, Person WHERE Generation.ID=Person.ID UNION ALL SELECT Person.Mother FROM Generation, Person WHERE Generation.ID=Person.ID) SELECT Person.ID, Person.Name, Person.Mother, Person.Father FROM Generation, Person WHERE Generation.ID = Person.ID order by person.ID;
    66  id    name    mother    father
    67  1    Sue    null    null
    68  2    Ed    null    null
    69  4    Jack    1    2
    70  5    Jane    null    null
    71  CREATE TABLE employees_hierarchy (id INT PRIMARY KEY, name VARCHAR(50),manager_id INT);
    72  INSERT INTO employees_hierarchy (id, name, manager_id) VALUES(1, 'Alice', NULL), (2, 'Bob', 1),(3, 'Charlie', 1),(4, 'David', 2),(5, 'Eve', 2),(6, 'Frank', 3);
    73  WITH RECURSIVE employee_hierarchy_cte (id, name, manager_id, level) AS (SELECT id, name, manager_id, 0 FROM employees_hierarchy WHERE name = 'Alice' UNION ALL SELECT e.id, e.name, e.manager_id, eh.level + 1 FROM employees_hierarchy AS e JOIN employee_hierarchy_cte AS eh ON e.manager_id = eh.id) SELECT name, level FROM employee_hierarchy_cte;
    74  name    level
    75  Alice    0
    76  Bob    1
    77  Charlie    1
    78  David    2
    79  Eve    2
    80  Frank    2
    81  WITH RECURSIVE employee_hierarchy_cte (id, name, manager_id, level) AS (SELECT id, name, manager_id, 0 FROM employees_hierarchy WHERE name = 'Alice' UNION ALL SELECT e.id, e.name, e.manager_id, eh.level + 1 FROM employees_hierarchy AS e JOIN employee_hierarchy_cte AS eh ON e.manager_id = eh.id) SELECT t.name, t.level FROM employee_hierarchy_cte as t;
    82  name    level
    83  Alice    0
    84  Bob    1
    85  Charlie    1
    86  David    2
    87  Eve    2
    88  Frank    2