starrocks [Feature] Recursive Queries

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:

https://github.com/StarRocks/starrocks/issues/34616
https://github.com/StarRocks/starrocks/issues/16213

Has been asked for in the past:

https://github.com/StarRocks/starrocks/issues/16445#issuecomment-1731183360

Oct 01 '24 16:10 rchui

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.