[jira] [Commented] (SPARK-24497) ANSI SQL: Recursive query

["Daniel Mateus Pires (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-24497?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17039498#comment-17039498 ] 

Daniel Mateus Pires commented on SPARK-24497:
---------------------------------------------

Hey! the PR linked to this issue has merge conflicts and reviewers didn't come back to it for another round of reviews, just wanted to notify on this thread that this feature would be very useful :+1:

> ANSI SQL: Recursive query
> -------------------------
>
>                 Key: SPARK-24497
>                 URL: https://issues.apache.org/jira/browse/SPARK-24497
>             Project: Spark
>          Issue Type: Sub-task
>          Components: SQL
>    Affects Versions: 3.0.0
>            Reporter: Yuming Wang
>            Priority: Major
>
> 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
(v8.3.4#803005)

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