| # recursive_queries.iq - recursive queries using WITH CTE |
| # |
| # Licensed to the Apache Software Foundation (ASF) under one or more |
| # contributor license agreements. See the NOTICE file distributed with |
| # this work for additional information regarding copyright ownership. |
| # The ASF licenses this file to you under the Apache License, Version 2.0 |
| # (the "License"); you may not use this file except in compliance with |
| # the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| # |
| !use scott |
| !set outputformat mysql |
| |
| WITH RECURSIVE FactorialCTE(n, a) AS ( |
| SELECT 1, 1 |
| UNION ALL |
| SELECT n + 1, a * (n + 1) |
| FROM FactorialCTE |
| WHERE n < 11 |
| ) |
| SELECT a FROM FactorialCTE WHERE n = 10; |
| +---------+ |
| | A | |
| +---------+ |
| | 3628800 | |
| +---------+ |
| (1 row) |
| |
| !ok |
| |
| WITH RECURSIVE FibonacciCTE(n, a, b) AS ( |
| SELECT 1, 0, 1 |
| UNION ALL |
| SELECT n + 1, b, a + b |
| FROM FibonacciCTE |
| WHERE n < 10 |
| ) |
| SELECT a FROM FibonacciCTE; |
| +----+ |
| | A | |
| +----+ |
| | 0 | |
| | 1 | |
| | 1 | |
| | 13 | |
| | 2 | |
| | 21 | |
| | 3 | |
| | 34 | |
| | 5 | |
| | 8 | |
| +----+ |
| (10 rows) |
| |
| !ok |
| |
| WITH RECURSIVE CumulativeSumCTE(n, a) AS ( |
| SELECT 1, 10 |
| UNION ALL |
| SELECT n + 1, a + (n + 1) |
| FROM CumulativeSumCTE |
| WHERE n < 20 |
| ) |
| SELECT a FROM CumulativeSumCTE; |
| +-----+ |
| | A | |
| +-----+ |
| | 10 | |
| | 100 | |
| | 114 | |
| | 12 | |
| | 129 | |
| | 145 | |
| | 15 | |
| | 162 | |
| | 180 | |
| | 19 | |
| | 199 | |
| | 219 | |
| | 24 | |
| | 30 | |
| | 37 | |
| | 45 | |
| | 54 | |
| | 64 | |
| | 75 | |
| | 87 | |
| +-----+ |
| (20 rows) |
| |
| !ok |
| |
| WITH RECURSIVE employees(emp_id, emp_name, manager_id) as ( |
| select * from (values |
| (1, 'CEO', NULL), |
| (2, 'Manager 1', 1), |
| (3, 'Manager 2', 1), |
| (4, 'Employee 1', 2), |
| (5, 'Employee 2', 2)) |
| ), |
| OrgHierarchy(emp_id, emp_name, manager_id) AS ( |
| SELECT emp_id, emp_name, manager_id |
| FROM employees |
| WHERE manager_id IS NULL |
| UNION ALL |
| SELECT e.emp_id, e.emp_name, e.manager_id |
| FROM employees e |
| INNER JOIN OrgHierarchy h ON e.manager_id = h.emp_id |
| ) |
| SELECT * FROM OrgHierarchy; |
| +--------+------------+------------+ |
| | EMP_ID | EMP_NAME | MANAGER_ID | |
| +--------+------------+------------+ |
| | 1 | CEO | | |
| | 2 | Manager 1 | 1 | |
| | 3 | Manager 2 | 1 | |
| | 4 | Employee 1 | 2 | |
| | 5 | Employee 2 | 2 | |
| +--------+------------+------------+ |
| (5 rows) |
| |
| !ok |
| |
| with RECURSIVE files(id,name, parent_id) as ( |
| select * from (values |
| (1, 'Root', NULL), |
| (2, 'Folder 1', 1), |
| (3, 'File 1', 2), |
| (4, 'Folder 2', 1), |
| (5, 'File 2', 4)) |
| ), |
| FileSystemCTE(id, name, parent_id) AS ( |
| SELECT id, name, parent_id |
| FROM files |
| WHERE parent_id IS NULL |
| UNION ALL |
| SELECT f.id, f.name, f.parent_id |
| FROM files f |
| JOIN FileSystemCTE c ON f.parent_id = c.id |
| ) |
| SELECT id, name FROM FileSystemCTE; |
| +----+----------+ |
| | ID | NAME | |
| +----+----------+ |
| | 1 | Root | |
| | 2 | Folder 1 | |
| | 3 | File 1 | |
| | 4 | Folder 2 | |
| | 5 | File 2 | |
| +----+----------+ |
| (5 rows) |
| |
| !ok |
| |
| |
| with RECURSIVE hierarchy(id, name, parent_id) as ( |
| select * from (values |
| (1, 'Root', NULL), |
| (2, 'Node 1', 1), |
| (3, 'Node 2', 1), |
| (4, 'Node 1.1', 2), |
| (5, 'Node 1.2', 2)) |
| ), |
| HierarchyCTE(id, name, parent_id, depth) AS ( |
| SELECT id, name, parent_id, 1 as depth |
| FROM hierarchy |
| WHERE parent_id IS NULL |
| UNION ALL |
| SELECT h.id, h.name, h.parent_id, hc.depth + 1 |
| FROM hierarchy h |
| JOIN HierarchyCTE hc ON h.parent_id = hc.id |
| ) |
| SELECT id, name, depth FROM HierarchyCTE; |
| +----+----------+-------+ |
| | ID | NAME | DEPTH | |
| +----+----------+-------+ |
| | 1 | Root | 1 | |
| | 2 | Node 1 | 2 | |
| | 3 | Node 2 | 2 | |
| | 4 | Node 1.1 | 3 | |
| | 5 | Node 1.2 | 3 | |
| +----+----------+-------+ |
| (5 rows) |
| |
| !ok |
| |
| |
| with RECURSIVE hierarchy(id, name, parent_id) as ( |
| select * from (values |
| (1, 'Root', NULL), |
| (2, 'Node 1', 1), |
| (3, 'Node 2', 1), |
| (4, 'Node 1.1', 2), |
| (5, 'Node 1.2', 2)) |
| ), |
| AncestorsCTE(id, name, parent_id) AS ( |
| SELECT id, name, parent_id |
| FROM hierarchy |
| WHERE id = 5 |
| UNION ALL |
| SELECT h.id, h.name, h.parent_id |
| FROM hierarchy h |
| JOIN AncestorsCTE a ON h.id = a.parent_id |
| ) |
| SELECT id, name FROM AncestorsCTE WHERE id <> 5; |
| +----+----------+ |
| | ID | NAME | |
| +----+----------+ |
| | 1 | Root | |
| | 2 | Node 1 | |
| +----+----------+ |
| (2 rows) |
| |
| !ok |
| |
| with RECURSIVE graph(source_node, target_node) as ( |
| select * from (values |
| ('A', 'B'), |
| ('B', 'C'), |
| ('B', 'D'), |
| ('C', 'E')) as foo |
| ), |
| PathCTE(node, path) AS ( |
| SELECT source_node, cast(source_node as varchar) |
| FROM graph |
| WHERE source_node = 'A' |
| UNION ALL |
| SELECT g.target_node, cast(path || ',' || g.target_node as varchar) |
| FROM graph g |
| JOIN PathCTE p ON g.source_node = p.node |
| ) |
| SELECT * FROM PathCTE WHERE node = 'D'; |
| +------+-------+ |
| | NODE | PATH | |
| +------+-------+ |
| | D | A,B,D | |
| +------+-------+ |
| (1 row) |
| |
| !ok |
| |
| with RECURSIVE graph(source_node, target_node) as ( |
| select * from (values |
| ('A', 'B'), |
| ('B', 'C'), |
| ('B', 'D'), |
| ('C', 'E')) as foo |
| ), |
| PathCTE(node, path) AS ( |
| SELECT source_node, cast(source_node as varchar) |
| FROM graph |
| WHERE source_node = 'A' |
| UNION ALL |
| SELECT g.target_node, cast(path || ',' || g.target_node as varchar) |
| FROM graph g |
| JOIN PathCTE p ON g.source_node = p.node |
| ), |
| CTE(node, path) as ( |
| SELECT node, path from PathCTE |
| UNION ALL |
| SELECT node, path from PathCTE |
| ) |
| SELECT * FROM CTE WHERE node = 'D'; |
| +------+-------+ |
| | NODE | PATH | |
| +------+-------+ |
| | D | A,B,D | |
| | D | A,B,D | |
| +------+-------+ |
| (2 rows) |
| |
| !ok |
| |
| # Following tests are sql version of EnumerableRepeatUnionTests.java |
| |
| WITH RECURSIVE delta(n) AS |
| ( |
| VALUES (1) |
| UNION ALL SELECT n+1 |
| FROM delta |
| WHERE n < 10 ) |
| SELECT * |
| FROM delta; |
| +----+ |
| | N | |
| +----+ |
| | 1 | |
| | 10 | |
| | 2 | |
| | 3 | |
| | 4 | |
| | 5 | |
| | 6 | |
| | 7 | |
| | 8 | |
| | 9 | |
| +----+ |
| (10 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE aux(i) AS |
| ( |
| VALUES (0) |
| UNION SELECT MOD((i+1), 10) |
| FROM aux |
| WHERE i < 10 ) |
| SELECT * |
| FROM aux; |
| +---+ |
| | I | |
| +---+ |
| | 0 | |
| | 1 | |
| | 2 | |
| | 3 | |
| | 4 | |
| | 5 | |
| | 6 | |
| | 7 | |
| | 8 | |
| | 9 | |
| +---+ |
| (10 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE aux(i, j) AS |
| ( |
| VALUES (0, |
| 0) |
| UNION SELECT MOD((i+1), 10), |
| j |
| FROM aux |
| WHERE i < 10 ) |
| SELECT * |
| FROM aux; |
| +---+---+ |
| | I | J | |
| +---+---+ |
| | 0 | 0 | |
| | 1 | 0 | |
| | 2 | 0 | |
| | 3 | 0 | |
| | 4 | 0 | |
| | 5 | 0 | |
| | 6 | 0 | |
| | 7 | 0 | |
| | 8 | 0 | |
| | 9 | 0 | |
| +---+---+ |
| (10 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE delta(n, fact) AS |
| ( |
| VALUES (0, |
| 1) |
| UNION ALL SELECT n+1, |
| (n+1)*fact |
| FROM delta |
| WHERE n < 7 ) |
| SELECT * |
| FROM delta; |
| +---+------+ |
| | N | FACT | |
| +---+------+ |
| | 0 | 1 | |
| | 1 | 1 | |
| | 2 | 2 | |
| | 3 | 6 | |
| | 4 | 24 | |
| | 5 | 120 | |
| | 6 | 720 | |
| | 7 | 5040 | |
| +---+------+ |
| (8 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE t_out(n) AS |
| (WITH RECURSIVE t_in(n) AS |
| ( |
| VALUES (1) |
| UNION ALL SELECT n+1 |
| FROM t_in |
| WHERE n < 9 ) SELECT n |
| FROM t_in |
| UNION ALL SELECT n*10 |
| FROM t_out |
| WHERE n < 100 ) |
| SELECT n |
| FROM t_out; |
| +-----+ |
| | N | |
| +-----+ |
| | 1 | |
| | 10 | |
| | 100 | |
| | 2 | |
| | 20 | |
| | 200 | |
| | 3 | |
| | 30 | |
| | 300 | |
| | 4 | |
| | 40 | |
| | 400 | |
| | 5 | |
| | 50 | |
| | 500 | |
| | 6 | |
| | 60 | |
| | 600 | |
| | 7 | |
| | 70 | |
| | 700 | |
| | 8 | |
| | 80 | |
| | 800 | |
| | 9 | |
| | 90 | |
| | 900 | |
| +-----+ |
| (27 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE aux(i) AS |
| (WITH RECURSIVE aux1(i) AS |
| ( |
| VALUES (1) |
| UNION ALL SELECT i+1 |
| FROM aux1 |
| WHERE i < 11 ) SELECT * |
| FROM aux1) |
| SELECT * |
| FROM aux; |
| +----+ |
| | I | |
| +----+ |
| | 1 | |
| | 10 | |
| | 11 | |
| | 2 | |
| | 3 | |
| | 4 | |
| | 5 | |
| | 6 | |
| | 7 | |
| | 8 | |
| | 9 | |
| +----+ |
| (11 rows) |
| |
| !ok |
| |
| |
| WITH RECURSIVE a(x) AS |
| (SELECT 1), |
| b(y) AS |
| (SELECT x |
| FROM a |
| UNION ALL SELECT y + 1 |
| FROM b |
| WHERE y < 2 ), |
| c(z) AS |
| (SELECT y |
| FROM b |
| UNION ALL SELECT z * 4 |
| FROM c |
| WHERE z < 4 ) |
| SELECT * |
| FROM a, |
| b, |
| c; |
| +---+---+---+ |
| | X | Y | Z | |
| +---+---+---+ |
| | 1 | 1 | 1 | |
| | 1 | 1 | 2 | |
| | 1 | 1 | 4 | |
| | 1 | 1 | 8 | |
| | 1 | 2 | 1 | |
| | 1 | 2 | 2 | |
| | 1 | 2 | 4 | |
| | 1 | 2 | 8 | |
| +---+---+---+ |
| (8 rows) |
| |
| !ok |
| |
| |
| # End recursive_queries.iq |
