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 bae6b26c3de IGNITE-29043 SQL Calcite: Support recursive CTEs without
the RECURSIVE keyword (#13562)
bae6b26c3de is described below
commit bae6b26c3deb2b6bfe480254b2563f6422160819
Author: Vladislav Pyatkov <[email protected]>
AuthorDate: Wed Sep 9 17:04:23 2026 +0300
IGNITE-29043 SQL Calcite: Support recursive CTEs without the RECURSIVE
keyword (#13562)
---
.../query/calcite/prepare/IgniteSqlValidator.java | 3 +
.../calcite/prepare/RecursiveCteRewriter.java | 118 +++++++++++++++++++++
.../integration/RecursiveCteIntegrationTest.java | 26 +++++
.../calcite/planner/RecursiveCtePlannerTest.java | 109 +++++++++++++++++++
4 files changed, 256 insertions(+)
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 5f31f4d64a5..0edb3a3602f 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
@@ -510,6 +510,9 @@ public class IgniteSqlValidator extends SqlValidatorImpl {
node = super.performUnconditionalRewrites(node, underFrom);
+ if (node instanceof SqlWithItem)
+ RecursiveCteRewriter.inferRecursion((SqlWithItem)node);
+
if (config() instanceof Config && ((Config)config()).sqlNodeRewriter()
!= null)
node = ((Config)config()).sqlNodeRewriter().rewrite(this, 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
new file mode 100644
index 00000000000..b1092153308
--- /dev/null
+++
b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/prepare/RecursiveCteRewriter.java
@@ -0,0 +1,118 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.prepare;
+
+import java.util.EnumSet;
+import java.util.List;
+import java.util.Set;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlJoin;
+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.SqlSelect;
+import org.apache.calcite.sql.SqlWith;
+import org.apache.calcite.sql.SqlWithItem;
+
+/** Infers an omitted RECURSIVE keyword 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(
+ SqlKind.AS, SqlKind.TABLE_REF, SqlKind.EXTEND, SqlKind.SNAPSHOT,
SqlKind.TABLESAMPLE,
+ SqlKind.LATERAL, SqlKind.PIVOT, SqlKind.UNPIVOT,
SqlKind.MATCH_RECOGNIZE
+ );
+
+ /** */
+ private RecursiveCteRewriter() {
+ // No-op.
+ }
+
+ /**
+ * Called from the validator's existing bottom-up rewrite, so nested WITH
items have already been processed.
+ * Only UNION can define a recursive CTE in Calcite. Other CTEs and
explicitly recursive items need no scan.
+ * The seed uses the enclosing scope; only a reference in the right UNION
operand can refer to this item.
+ */
+ static void inferRecursion(SqlWithItem item) {
+ if (!item.recursive.booleanValue() && item.query.getKind() ==
SqlKind.UNION
+ && references(((SqlCall)item.query).operand(1), item.name, false))
+ item.recursive = SqlLiteral.createBoolean(true,
item.recursive.getParserPosition());
+ }
+
+ /** Finds the first unqualified table reference, respecting nested WITH
scopes and ignoring column names. */
+ private static boolean references(SqlNode node, SqlIdentifier name,
boolean from) {
+ if (node == null)
+ return false;
+
+ if (node instanceof SqlIdentifier)
+ // Match Calcite's WithRecursiveScope: parser casing is already
applied, including quoted identifiers.
+ return from && ((SqlIdentifier)node).names.equals(name.names);
+
+ if (node instanceof SqlWith) {
+ SqlWith with = (SqlWith)node;
+
+ for (SqlNode withNode : with.withList) {
+ SqlWithItem item = (SqlWithItem)withNode;
+ boolean shadows = item.name.names.equals(name.names);
+
+ // 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;
+
+ if (references(qry, name, false))
+ return true;
+
+ // This item is visible in subsequent items and in the WITH
body.
+ if (shadows)
+ return false;
+ }
+
+ return references(with.body, name, false);
+ }
+
+ if (node instanceof SqlJoin) {
+ SqlJoin join = (SqlJoin)node;
+
+ return references(join.getLeft(), name, true)
+ || references(join.getRight(), name, true)
+ || references(join.getCondition(), name, false);
+ }
+
+ List<SqlNode> operands;
+
+ if (node instanceof SqlCall)
+ operands = ((SqlCall)node).getOperandList();
+ else if (node instanceof SqlNodeList)
+ operands = (SqlNodeList)node;
+ else
+ return false;
+
+ for (int i = 0; i < operands.size(); i++) {
+ SqlNode operand = operands.get(i);
+ boolean childFrom = node instanceof SqlSelect
+ ? operand == ((SqlSelect)node).getFrom()
+ : from && i == 0 && FROM_WRAPPERS.contains(node.getKind());
+
+ if (references(operand, name, childFrom))
+ return true;
+ }
+
+ return false;
+ }
+}
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 be0a8aa3039..8989bad03de 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
@@ -31,6 +31,32 @@ public class RecursiveCteIntegrationTest extends
AbstractBasicIntegrationTest {
/** Number of invocations of a non-deterministic function. */
private static final AtomicInteger nonDeterministicCallCnt = new
AtomicInteger();
+ /** Explicit and inferred recursion must produce the same rows. */
+ @Test
+ public void testOptionalRecursiveKeyword() {
+ for (String keyword : new String[] {"", "RECURSIVE "}) {
+ assertQuery("WITH " + keyword + "seed(n) AS (SELECT 1), numbers(n)
AS (" +
+ "SELECT n FROM seed UNION ALL SELECT n + 1 FROM numbers WHERE
n < 3), " +
+ "result AS (SELECT * FROM numbers) SELECT * FROM result")
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .check();
+
+ assertQuery("SELECT * FROM (WITH " + keyword + "\"Numbers\"(n) AS
(" +
+ "SELECT 1 UNION ALL SELECT x.n + 1 FROM \"Numbers\" x WHERE
x.n < 3) " +
+ "SELECT * FROM \"Numbers\")")
+ .returns(1)
+ .returns(2)
+ .returns(3)
+ .check();
+
+ assertThrows("WITH " + keyword + "numbers(n) AS (" +
+ "SELECT 1 UNION SELECT n + 1 FROM numbers WHERE n < 3) SELECT
* FROM numbers",
+ IgniteSQLException.class, "only UNION ALL is supported");
+ }
+ }
+
/** */
@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 658c1431bda..769b8233558 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
@@ -17,10 +17,17 @@
package org.apache.ignite.internal.processors.query.calcite.planner;
+import java.util.ArrayList;
+import java.util.List;
import org.apache.calcite.rel.core.Exchange;
import org.apache.calcite.rel.core.Spool;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlNode;
+import org.apache.calcite.sql.SqlWithItem;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.util.SqlBasicVisitor;
import
org.apache.ignite.internal.processors.query.calcite.exec.exp.IgniteScalarFunction;
+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;
@@ -69,6 +76,108 @@ public class RecursiveCtePlannerTest extends
AbstractPlannerTest {
.and(input(0, isInstanceOf(IgniteValues.class)))
.and(input(1,
hasChildThat(isInstanceOf(IgniteRecursiveTableScan.class))))
);
+
+ assertPlan(sql.replace("WITH RECURSIVE", "WITH"), schema,
isInstanceOf(IgniteRepeatUnion.class)
+ .and(input(1,
hasChildThat(isInstanceOf(IgniteRecursiveTableScan.class)))));
+ }
+
+ /** The inferred flag must be set before Calcite registers CTE scopes,
including nested WITH clauses. */
+ @Test
+ public void testImplicitRecursiveFlags() throws Exception {
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT x.n + 1 FROM numbers x WHERE x.n < 3) SELECT * FROM
numbers", true);
+
+ assertRecursiveFlags("WITH seed(n) AS (SELECT 1), numbers(n) AS
(SELECT n FROM seed UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3), result AS (SELECT * FROM
numbers) SELECT * FROM result",
+ false, true, false);
+
+ assertRecursiveFlags("SELECT * FROM (WITH numbers(n) AS (SELECT 1
UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 3) SELECT * FROM numbers)",
true);
+
+ assertRecursiveFlags("WITH \"Numbers\"(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n + 1 FROM \"Numbers\" WHERE n < 3) SELECT * FROM
\"Numbers\"", true);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n + 1 FROM (SELECT * FROM numbers) x WHERE n < 3) SELECT *
FROM numbers", true);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT (SELECT n + 1 FROM numbers WHERE n < 3)) SELECT * FROM
numbers", true);
+ }
+
+ /** Identifiers in expressions, aliases and qualified table names must not
enable recursion. */
+ @Test
+ public void testOrdinaryCteFlags() throws Exception {
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT n FROM numbers)
SELECT * FROM numbers", false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT n FROM numbers UNION
ALL SELECT 2) " +
+ "SELECT * FROM numbers", false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT numbers FROM (VALUES (2)) x(numbers)) SELECT * FROM
numbers", false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT numbers.n FROM (VALUES (2)) numbers(n)) SELECT * FROM
numbers", false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n FROM PUBLIC.numbers) SELECT * FROM numbers", false);
+
+ assertRecursiveFlags("WITH \"numbers\"(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n FROM NUMBERS) SELECT * FROM \"numbers\"", false);
+ }
+
+ /** Inner CTEs hide the outer name only where they are visible. */
+ @Test
+ public void testNestedCteFlags() throws Exception {
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT * FROM (WITH numbers(n) AS (SELECT 2) SELECT * FROM
numbers)) SELECT * FROM numbers",
+ false, false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT * FROM (WITH numbers(n) AS (SELECT 2), x AS (SELECT * FROM
numbers) SELECT * FROM x)) " +
+ "SELECT * FROM numbers", false, false, false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT * FROM (WITH numbers(n) AS (SELECT 2 UNION ALL SELECT n +
1 FROM numbers WHERE n < 3) " +
+ "SELECT * FROM numbers)) SELECT * FROM numbers", false, true);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n + 1 FROM (WITH numbers(n) AS (SELECT n FROM numbers
WHERE n < 3) " +
+ "SELECT * FROM numbers)) SELECT * FROM numbers", true, false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n + 1 FROM (WITH x AS (SELECT n FROM numbers WHERE n < 3),
numbers(n) AS (SELECT 9) " +
+ "SELECT * FROM x)) SELECT * FROM numbers", true, false, false);
+
+ assertRecursiveFlags("WITH numbers(n) AS (SELECT 1 UNION ALL " +
+ "SELECT n + 1 FROM (WITH numbers(n) AS (SELECT n FROM numbers
WHERE n < 2 UNION ALL " +
+ "SELECT n + 1 FROM numbers WHERE n < 2) SELECT * FROM numbers))
SELECT * FROM numbers", true, true);
+ }
+
+ /** Validates real SQL and checks flags on parsed WITH items in pre-order.
*/
+ private void assertRecursiveFlags(String sql, boolean... expected) throws
Exception {
+ IgniteSchema schema = createSchema(createTable("NUMBERS",
IgniteDistributions.single(), "N", Integer.class));
+
+ try (IgnitePlanner planner = plannerCtx(sql, schema).planner()) {
+ SqlNode node = planner.parse(sql);
+ List<SqlWithItem> items = new ArrayList<>();
+
+ node.accept(new SqlBasicVisitor<Void>() {
+ /** {@inheritDoc} */
+ @Override public Void visit(SqlCall call) {
+ if (call instanceof SqlWithItem)
+ items.add((SqlWithItem)call);
+
+ return super.visit(call);
+ }
+ });
+
+ planner.validate(node);
+
+ assertEquals(sql, expected.length, items.size());
+
+ for (int i = 0; i < expected.length; i++)
+ assertEquals(sql + " [item=" + i + ']', expected[i],
items.get(i).recursive.booleanValue());
+ }
}
/** A replicated source can be read on the coordinator without an
exchange. */
