support hierarchical queries

[scsldb]

Feature Request

Is your feature request related to a problem? Please describe:

Describe the feature you'd like:

like Oracle Hierarchical Queries

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[ghost]

The more standard way to do this is with a recursive CTE. See: #6824

[zz-jason]

After going through the examples listed in Oracle Hierarchical Query and MySQL Recursive Common Table Expression, I agree with @nullnotnil that they can both achieve the same application requirements. This answer from stack overflow also provides several alternatives and explanations about how to achieve a hierarchical query.

From Wikipedia's explanation of Hierarchical and recursive queries in SQL:

In standard SQL:1999 hierarchical queries are implemented by way of recursive common table expressions (CTEs). Unlike Oracle's earlier connect-by clause, recursive CTEs were designed with fixpoint semantics from the beginning.[1] Recursive CTEs from the standard were relatively close to the existing implementation in IBM DB2 version 2.[1] Recursive CTEs are also supported by Microsoft SQL Server (since SQL Server 2008 R2),[2] Firebird 2.1,[3] PostgreSQL 8.4+,[4] SQLite 3.8.3+,[5] IBM Informix version 11.50+, CUBRID, MariaDB 10.2+ and MySQL 8.0.1+,[6]. Tableau has documentation describing how CTEs can be used. TIBCO Spotfire does not support CTEs, while Oracle 11g Release 2's implementation lacks fixpoint semantics.

I think it's better to support recursive common table expression other this connect by feature in Oracle since recursive CTE is both in SQL-99 standard and widely implemented and used by database and database applications.

[zz-jason]

There is also a proposal about recursive CTE: #19253

[zz-jason]

I'm going to reject and close this feature request. feel free to reopen it if the current solution can not fulfill the application requirements.