This is an automated email from the ASF dual-hosted git repository.
vldpyatkov pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/master by this push:
new e20453758e4 IGNITE-29053 SQL Calcite: Handle ORDER BY in recursive CTE
(#13589)
e20453758e4 is described below
commit e20453758e4dcde264fb73a0d53c1a927647726f
Author: Vladislav Pyatkov <[email protected]>
AuthorDate: Thu Sep 17 16:21:41 2026 +0300
IGNITE-29053 SQL Calcite: Handle ORDER BY in recursive CTE (#13589)
---
.../query/calcite/prepare/IgniteSqlValidator.java | 3 +
.../calcite/prepare/RecursiveCteRewriter.java | 51 ++++++++++++---
.../integration/RecursiveCteIntegrationTest.java | 72 ++++++++++++++++++++++
.../calcite/planner/RecursiveCtePlannerTest.java | 41 ++++++++++++
4 files changed, 160 insertions(+), 7 deletions(-)
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
index 0edb3a3602f..8b671e53d85 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/IgniteSqlValidator.java
@@ -476,6 +476,9 @@ public class IgniteSqlValidator extends SqlValidatorImpl {
/** {@inheritDoc} */
@Override protected SqlNode performUnconditionalRewrites(SqlNode node,
boolean underFrom) {
+ if (node instanceof SqlWithItem)
+ RecursiveCteRewriter.rewriteOrderBy((SqlWithItem)node);
+
if (node instanceof SqlOrderBy) {
SqlOrderBy orderBy = (SqlOrderBy)node;
diff --git
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/RecursiveCteRewriter.java
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/RecursiveCteRewriter.java
index b1092153308..2338fada982 100644
---
a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/RecursiveCteRewriter.java
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/RecursiveCteRewriter.java
@@ -27,11 +27,14 @@ import org.apache.calcite.sql.SqlKind;
import org.apache.calcite.sql.SqlLiteral;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlOrderBy;
import org.apache.calcite.sql.SqlSelect;
import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
+import org.apache.ignite.internal.processors.cache.query.IgniteQueryErrorCode;
+import org.apache.ignite.internal.processors.query.IgniteSQLException;
-/** Infers an omitted RECURSIVE keyword before the validator registers WITH
scopes. */
+/** Normalizes recursive CTEs before the validator registers WITH scopes. */
class RecursiveCteRewriter {
/** FROM operators whose first operand is a table reference. */
private static final Set<SqlKind> FROM_WRAPPERS = EnumSet.of(
@@ -39,9 +42,39 @@ class RecursiveCteRewriter {
SqlKind.LATERAL, SqlKind.PIVOT, SqlKind.UNPIVOT,
SqlKind.MATCH_RECOGNIZE
);
- /** */
- private RecursiveCteRewriter() {
- // No-op.
+ /**
+ * Ignores sorting of the recursive UNION before Calcite wraps it in a
SELECT, hiding the recursive scope.
+ * Row limiting cannot be discarded because it changes the result.
Ordinary CTEs retain their ordering.
+ */
+ static void rewriteOrderBy(SqlWithItem item) {
+ if (!(item.query instanceof SqlOrderBy) ||
!hasRecursiveReference(item))
+ return;
+
+ SqlOrderBy orderBy = (SqlOrderBy)item.query;
+
+ if (orderBy.orderList.isEmpty())
+ return;
+
+ if (orderBy.fetch != null || orderBy.offset != null) {
+ throw new IgniteSQLException(
+ "Unsupported recursive CTE: ORDER BY with FETCH, LIMIT or
OFFSET is not supported",
+ IgniteQueryErrorCode.UNSUPPORTED_OPERATION
+ );
+ }
+
+ item.query = orderBy.query;
+ }
+
+ /** Finds a self-reference in the recursive operand before ORDER BY has
been rewritten. */
+ private static boolean hasRecursiveReference(SqlWithItem item) {
+ SqlNode qry = withoutOrderBy(item.query);
+
+ return qry.getKind() == SqlKind.UNION &&
references(((SqlCall)qry).operand(1), item.name, false);
+ }
+
+ /** Returns the query expression inside an optional ORDER BY wrapper. */
+ private static SqlNode withoutOrderBy(SqlNode qry) {
+ return qry instanceof SqlOrderBy ? ((SqlOrderBy)qry).query : qry;
}
/**
@@ -71,11 +104,15 @@ class RecursiveCteRewriter {
SqlWithItem item = (SqlWithItem)withNode;
boolean shadows = item.name.names.equals(name.names);
+ SqlNode qry = withoutOrderBy(item.query);
+
+ // ORDER BY normalization runs before nested items have had
their recursive flags inferred.
// A recursive item shadows the outer name in its recursive
term, but not in its seed.
- SqlNode qry = shadows && item.recursive.booleanValue() &&
item.query.getKind() == SqlKind.UNION
- ? ((SqlCall)item.query).operand(0) : item.query;
+ SqlNode visibleQry = shadows && qry.getKind() == SqlKind.UNION
+ && (item.recursive.booleanValue() ||
hasRecursiveReference(item))
+ ? ((SqlCall)qry).operand(0) : item.query;
- if (references(qry, name, false))
+ if (references(visibleQry, name, false))
return true;
// This item is visible in subsequent items and in the WITH
body.
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java
index 5352cdc3f40..6ca5e893449 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/integration/RecursiveCteIntegrationTest.java
@@ -60,6 +60,78 @@ public class RecursiveCteIntegrationTest extends
AbstractBasicIntegrationTest {
}
}
+ /** Checks explicit and implicit recursion with ORDER BY inside and
outside the CTE. */
+ @Test
+ public void testRecursiveCteWithOrderBy() {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ assertQuery("WITH " + keyword + "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")
+ .ordered()
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .check();
+ }
+ }
+
+ /** FETCH, LIMIT and OFFSET cannot be combined with sorting of the
recursive UNION. */
+ @Test
+ public void testRecursiveCteOrderByWithRowLimitingIsRejected() {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ for (String limit : new String[] {"FETCH FIRST 2 ROWS ONLY",
"LIMIT 2", "OFFSET 1 ROW"}) {
+ assertThrows("WITH " + keyword + "numbers(n) AS (SELECT 1
UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3 ORDER BY n " +
limit + ") SELECT n FROM numbers",
+ IgniteSQLException.class,
+ "Unsupported recursive CTE: ORDER BY with FETCH, LIMIT or
OFFSET is not supported");
+ }
+ }
+ }
+
+ /** A non-recursive CTE retains sorting and row limiting even in a WITH
RECURSIVE clause. */
+ @Test
+ public void testNonRecursiveCteOrderByWithRowLimiting() {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ assertQuery("WITH " + keyword + "numbers(n) AS (SELECT 1 AS n
UNION ALL SELECT 3 UNION ALL " +
+ "SELECT 2 ORDER BY n DESC FETCH FIRST 2 ROWS ONLY) SELECT n
FROM numbers ORDER BY n")
+ .ordered()
+ .returns(2)
+ .returns(3)
+ .check();
+ }
+ }
+
+ /** An inner recursive CTE hides an ordinary outer CTE's name during early
recursion detection. */
+ @Test
+ public void testOrderByWithNestedCteShadowing() {
+ assertQuery("WITH numbers(n) AS (SELECT 9 AS n UNION ALL " +
+ "SELECT n FROM (WITH 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 DESC FETCH FIRST 2 ROWS ONLY) SELECT n FROM numbers
ORDER BY n")
+ .ordered()
+ .returns(3)
+ .returns(9)
+ .check();
+ }
+
+ /** Row limiting of a recursive CTE's consumer remains supported. */
+ @Test
+ public void testRecursiveCteOrderByWithOuterRowLimiting() {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ assertQuery("WITH " + keyword + "numbers(n) AS (SELECT 1 UNION ALL
" +
+ "SELECT n + 1 FROM numbers WHERE n < 5 ORDER BY n) " +
+ "SELECT n FROM numbers ORDER BY n DESC FETCH FIRST 2 ROWS
ONLY")
+ .ordered()
+ .returns(5)
+ .returns(4)
+ .check();
+ }
+ }
+
/** */
@Test
public void testEmployeeHierarchy() {
diff --git
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/RecursiveCtePlannerTest.java
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/RecursiveCtePlannerTest.java
index 66c687d631a..d1700071af1 100644
---
a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/RecursiveCtePlannerTest.java
+++
b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/planner/RecursiveCtePlannerTest.java
@@ -34,6 +34,7 @@ import
org.apache.ignite.internal.processors.query.calcite.prepare.IgnitePlanner
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteIndexScan;
import
org.apache.ignite.internal.processors.query.calcite.rel.IgniteRecursiveTableScan;
import
org.apache.ignite.internal.processors.query.calcite.rel.IgniteRepeatUnion;
+import org.apache.ignite.internal.processors.query.calcite.rel.IgniteSort;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteTableScan;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteUnionAll;
import org.apache.ignite.internal.processors.query.calcite.rel.IgniteValues;
@@ -85,6 +86,46 @@ public class RecursiveCtePlannerTest extends
AbstractPlannerTest {
.and(input(1,
hasChildThat(isInstanceOf(IgniteRecursiveTableScan.class)))));
}
+ /** Sorting the recursive UNION must not introduce a sort in either
recursive branch or above it. */
+ @Test
+ public void testRecursiveCteOrderByIsIgnored() throws Exception {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ for (String direction : new String[] {"ASC", "DESC"}) {
+ for (String union : new String[] {"UNION ALL", "UNION
DISTINCT"}) {
+ assertPlan("WITH " + keyword + "numbers(n) AS (SELECT 1 "
+ union +
+ " SELECT n + 1 FROM numbers WHERE n < 3 ORDER BY n " +
direction +
+ ") SELECT n FROM numbers", new
IgniteSchema(DEFAULT_SCHEMA),
+ isInstanceOf(IgniteRepeatUnion.class)
+
.and(hasChildThat(isInstanceOf(IgniteSort.class)).negate())
+ .and(input(1,
hasChildThat(isInstanceOf(IgniteRecursiveTableScan.class)))));
+ }
+ }
+ }
+ }
+
+ /** Row limiting together with ORDER BY must fail explicitly rather than
be silently discarded. */
+ @Test
+ public void testRecursiveCteOrderByWithRowLimitingIsRejected() throws
Exception {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ for (String limit : new String[] {"FETCH FIRST 2 ROWS ONLY",
"LIMIT 2", "OFFSET 1 ROW"}) {
+ String sql = "WITH " + keyword + "numbers(n) AS (SELECT 1
UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3 ORDER BY n " +
limit + ") SELECT n FROM numbers";
+
+ try (IgnitePlanner planner = plannerCtx(sql, new
IgniteSchema(DEFAULT_SCHEMA)).planner()) {
+ SqlNode node = planner.parse(sql);
+
+ ValidationException err =
(ValidationException)GridTestUtils.assertThrows(log,
+ () -> planner.validate(node),
ValidationException.class,
+ "Unsupported recursive CTE: ORDER BY with FETCH, LIMIT
or OFFSET is not supported");
+
+ assertTrue(sql, err.getCause() instanceof
IgniteSQLException);
+ assertEquals(sql,
IgniteQueryErrorCode.UNSUPPORTED_OPERATION,
+ ((IgniteSQLException)err.getCause()).statusCode());
+ }
+ }
+ }
+ }
+
/** The inferred flag must be set before Calcite registers CTE scopes,
including nested WITH clauses. */
@Test
public void testImplicitRecursiveFlags() throws Exception {
