[SPARK-24497] ANSI SQL: Recursive query

• Type: Sub-task
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 08/Jun/18 13:31
• Updated: 24/Sep/24 10:42

Description

Examples

Here is an example for WITH RECURSIVE clause usage. Table "department" represents the structure of an organization as an adjacency list.

CREATE TABLE department (
    id INTEGER PRIMARY KEY,  -- department ID
    parent_department INTEGER REFERENCES department, -- upper department ID
    name TEXT -- department name
);

INSERT INTO department (id, parent_department, "name")
VALUES
     (0, NULL, 'ROOT'),
     (1, 0, 'A'),
     (2, 1, 'B'),
     (3, 2, 'C'),
     (4, 2, 'D'),
     (5, 0, 'E'),
     (6, 4, 'F'),
     (7, 5, 'G');

-- department structure represented here is as follows:
--
-- ROOT-+->A-+->B-+->C
--      |         |
--      |         +->D-+->F
--      +->E-+->G

 
To extract all departments under A, you can use the following recursive query:

WITH RECURSIVE subdepartment AS
(
    -- non-recursive term
    SELECT * FROM department WHERE name = 'A'

    UNION ALL

    -- recursive term
    SELECT d.*
    FROM
        department AS d
    JOIN
        subdepartment AS sd
        ON (d.parent_department = sd.id)
)
SELECT *
FROM subdepartment
ORDER BY name;

More details:

http://wiki.postgresql.org/wiki/CTEReadme

https://info.teradata.com/htmlpubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1141-160K/lqe1472241402390.html

 

Issue Links

• blocks: SPARK-28453 Support recursive view syntax
• blocks: SPARK-28731 Support limit on recursive queries
• is duplicated by: SPARK-42836 Support for recursive queries
• relates to: SPARK-33459 Commonly used Teradata extension syntax
• links to: [Github] Pull Request #29210 (peter-toth)
• links to: GitHub Pull Request #23531
• links to: GitHub Pull Request #40744