Vladislav Pyatkov created IGNITE-29053:
------------------------------------------
Summary: SQL Calcite: Handle ORDER BY in recursive CTE
Key: IGNITE-29053
URL: https://issues.apache.org/jira/browse/IGNITE-29053
Project: Ignite
Issue Type: Improvement
Reporter: Vladislav Pyatkov
h3. Motivation
A recursive CTE with an _ORDER BY_ clause applied to its _UNION_ fails
validation in the Calcite SQL engine:
{code}
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 3
ORDER BY n
)
SELECT n
FROM numbers
ORDER BY n;
{code}
The same issue occurs when the RECURSIVE keyword is omitted.
The query fails with:
{noformat}
Object 'NUMBERS' not found
{noformat}
Calcite rewrites the internal ORDER BY into a wrapping SELECT, preventing
correct recursive CTE recognition and scope resolution.
h3. Definition of done
* Accept and ignore ORDER BY applied to the recursive UNION when no
row-limiting clause is present. The example should return 1, 2, 3.
* Reject an internal ORDER BY combined with FETCH, LIMIT, or OFFSET with
UNSUPPORTED_OPERATION and a clear error message.
* Apply the same behavior with and without the RECURSIVE keyword.
* Preserve ordering and row limiting in non-recursive CTEs and in queries
consuming the recursive CTE.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
