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

["gabrywu (Jira)" <ji...@apache.org>]

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

gabrywu commented on SPARK-24497:
---------------------------------

this is a useful feature, when will it be merged to main branch?

> 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.1.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.20.10#820010)

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