[Feature] Recursive Queries

[rchui]

Feature request

Is your feature request related to a problem? Please describe.
There are many use cases in which tables are self referential and the only way to evaluate the contents of the table is to iteratively recurse through the table. This is only possible in SQL with a recursive SQL statement otherwise you must resort to external processing or acknowledge limitations in SQL implementation. This is a very common batch processing use case for hierarchical which StarRocks should support.

Describe the solution you'd like
Support the following SQL syntax:

WITH RECURSIVE < cte-name > AS (
    ...
)

Describe alternatives you've considered
N/A

Additional context
This is supported by almost all major modern databases:

• mysql: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
• postgres: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE
• clickhouse: https://clickhouse.com/docs/en/sql-reference/statements/select/with#recursive-queries
• snowflake: https://docs.snowflake.com/en/user-guide/queries-cte#recursive-ctes-and-hierarchical-data

Past issues have been marked as stale and closed:

• [Feature] With recursive syntax support #34616
• Support Recursive CTE #16213

Has been asked for in the past:

• StarRocks Roadmap 2023 #16445 (comment)

[john-schwegler-vectorsolutions]

This is a significant gap in functionality to replace other SQL-based query systems. Needing to traverse a hierarchy (e.g. employee-manager chains) is a relatively common need.

[Peschido]

+1

[edfarina2]

+1

[rkinwork]

+1