Vladislav Pyatkov created IGNITE-29038:
------------------------------------------
Summary: Fix recursive CTE subqueries and multiple consumers
Key: IGNITE-29038
URL: https://issues.apache.org/jira/browse/IGNITE-29038
Project: Ignite
Issue Type: Bug
Reporter: Vladislav Pyatkov
Recursive CTE (IGNITE-27822) support currently has two blocking issues.
h3. A self-reference inside a subquery may return an incorrect result instead
of an error:
{code}
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT (SELECT n + 1 FROM numbers)
FROM (VALUES (0))
)
SELECT n
FROM numbers
FETCH FIRST 3 ROWS ONLY;
{code}
Such queries are not supported by most RDBMSs. Ignite should reject them with a
descriptive IgniteSQLException.
h3. Referencing the same recursive CTE multiple times in the outer query fails
because consumers share the recursion state:
{code}
WITH RECURSIVE numbers(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM numbers WHERE n < 3
)
SELECT l.n, r.n
FROM numbers l
JOIN numbers r ON l.n = r.n;
{code}
This is supported by other RDBMSs and should return (1,1), (2,2), and (3,3).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
