Vladislav Pyatkov created CALCITE-7815:
------------------------------------------
Summary: Support the SQL-standard SEARCH clause in recursive CTEs
Key: CALCITE-7815
URL: https://issues.apache.org/jira/browse/CALCITE-7815
Project: Calcite
Issue Type: New Feature
Components: core
Reporter: Vladislav Pyatkov
Discussing and implementing the SQL-standard _SEARCH DEPTH FIRST_ and _SEARCH
BREADTH FIRST_ clauses in recursive common table expressions.
_SEARCH_ adds an ordering column to the CTE result. The enclosing query can
order by that column to obtain depth-first or breadth-first output; the clause
does not itself guarantee the physical evaluation order.
Example:
{code:sql}
WITH RECURSIVE
edges(src, dst) AS (
VALUES ('A', 'B'), ('A', 'C'), ('B', 'D')
),
walk(node) AS (
VALUES ('A')
UNION ALL
SELECT e.dst
FROM walk w JOIN edges e ON e.src = w.node
)
SEARCH DEPTH FIRST BY node SET traversal_order
SELECT node
FROM walk
ORDER BY traversal_order;
{code}
Expected output: A, B, D, C. Replacing _DEPTH FIRST_ with _BREADTH FIRST_
should produce A, B, C, D.
The work should cover parsing, validation, and execution.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
