[jira] [Created] (SPARK-24497) Support recursive SQL query

["Yuming Wang (JIRA)" <ji...@apache.org>]

Yuming Wang created SPARK-24497:
-----------------------------------

             Summary: Support recursive SQL query
                 Key: SPARK-24497
                 URL: https://issues.apache.org/jira/browse/SPARK-24497
             Project: Spark
          Issue Type: New Feature
          Components: SQL
    Affects Versions: 2.4.0
            Reporter: Yuming Wang


h3. *Examples*

Here is an example for {{WITH RECURSIVE}} clause usage. Table "department" represents the structure of an organization as an adjacency list.
{code:sql}
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
{code}
 
 To extract all departments under A, you can use the following recursive query:
{code:sql}
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;
{code}
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]

 



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org