This is an automated email from the ASF dual-hosted git repository.
rubenada pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 8f27e24a02 [CALCITE-7814] Support the SQL-standard CYCLE clause in
recursive CTEs
8f27e24a02 is described below
commit 8f27e24a02e7b76fc40b6cfd9b1d2cc96bc8b1b6
Author: Vladislav Pyatkov <[email protected]>
AuthorDate: Fri Oct 9 16:10:14 2026 +0300
[CALCITE-7814] Support the SQL-standard CYCLE clause in recursive CTEs
---
core/src/main/codegen/templates/Parser.jj | 34 +-
.../calcite/adapter/enumerable/PhysTypeImpl.java | 16 +
.../calcite/adapter/enumerable/RexImpTable.java | 4 +
.../apache/calcite/runtime/CalciteResource.java | 18 +
.../org/apache/calcite/runtime/SqlFunctions.java | 12 +
.../org/apache/calcite/sql/SqlCycleClause.java | 113 +++++
.../main/java/org/apache/calcite/sql/SqlKind.java | 5 +-
.../java/org/apache/calcite/sql/SqlWithItem.java | 23 +-
.../calcite/sql/fun/SqlInternalOperators.java | 10 +
.../apache/calcite/sql/validate/CycleRewriter.java | 440 ++++++++++++++++++
.../calcite/sql/validate/SqlValidatorImpl.java | 47 +-
.../org/apache/calcite/util/BuiltInMethod.java | 1 +
.../calcite/runtime/CalciteResource.properties | 6 +
.../calcite/adapter/enumerable/PhysTypeTest.java | 37 ++
.../apache/calcite/test/CollectionTypeTest.java | 40 +-
.../org/apache/calcite/test/RecursiveCteTest.java | 514 +++++++++++++++++++++
core/src/test/resources/sql/recursive_queries.iq | 87 ++++
site/_docs/reference.md | 52 +++
.../apache/calcite/sql/parser/SqlParserTest.java | 76 +++
19 files changed, 1513 insertions(+), 22 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 1105df6bcb..11c352cf97 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -106,6 +106,7 @@ import org.apache.calcite.sql.SqlUtil;
import org.apache.calcite.sql.SqlWindow;
import org.apache.calcite.sql.SqlWith;
import org.apache.calcite.sql.SqlWithItem;
+import org.apache.calcite.sql.SqlCycleClause;
import org.apache.calcite.sql.fun.SqlCase;
import org.apache.calcite.sql.fun.SqlInternalOperators;
import org.apache.calcite.sql.fun.SqlLibraryOperators;
@@ -3991,13 +3992,44 @@ void AddWithItem(List<SqlWithItem> list, SqlLiteral
recursive) :
final SqlIdentifier id;
final SqlNodeList columnList;
final SqlNode definition;
+ SqlCycleClause cycle = null;
}
{
id = SimpleIdentifier()
( columnList = ParenthesizedSimpleIdentifierList() | { columnList = null;
} )
<AS>
definition = ParenthesizedExpression(ExprContext.ACCEPT_QUERY)
- { list.add(new SqlWithItem(id.getParserPosition(), id, columnList,
definition, recursive)); }
+ [ cycle = CycleClause() ]
+ { list.add(new SqlWithItem(id.getParserPosition(), id, columnList,
definition, recursive, cycle)); }
+}
+
+SqlCycleClause CycleClause() :
+{
+ final Span s;
+ final List<SqlNode> columns = new ArrayList<SqlNode>();
+ SqlIdentifier column;
+ final SqlIdentifier markColumn;
+ final SqlNode markValue;
+ final SqlNode defaultValue;
+ final SqlIdentifier pathColumn;
+}
+{
+ <CYCLE> { s = span(); }
+ column = SimpleIdentifier() { columns.add(column); }
+ ( <COMMA> column = SimpleIdentifier() { columns.add(column); } )*
+ <SET> markColumn = SimpleIdentifier()
+ (
+ <TO> markValue = Expression(ExprContext.ACCEPT_NON_QUERY)
+ <DEFAULT_> defaultValue = Expression(ExprContext.ACCEPT_NON_QUERY)
+ |
+ {
+ markValue = SqlLiteral.createBoolean(true,
markColumn.getParserPosition());
+ defaultValue = SqlLiteral.createBoolean(false,
markColumn.getParserPosition());
+ }
+ )
+ <USING> pathColumn = SimpleIdentifier()
+ { return new SqlCycleClause(s.end(this), new SqlNodeList(columns, s.pos()),
+ markColumn, markValue, defaultValue, pathColumn); }
}
/**
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
index 2efbb25d22..f1490a02c0 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/PhysTypeImpl.java
@@ -587,6 +587,11 @@ private RelDataType toStruct(RelDataType type) {
@Override public @Nullable Expression comparer() {
final Expression comparer = format.comparer();
if (comparer != null) {
+ // ARRAY compares nested arrays by content, but a List or Map compares
+ // the Object[] values it contains by reference.
+ if (format == JavaRowFormat.ARRAY &&
containsStructInCollection(rowType)) {
+ return Expressions.call(BuiltInMethod.DEEP_COMPARER.method);
+ }
return comparer;
}
if (anyFieldContainsStruct(rowType)) {
@@ -599,6 +604,17 @@ private RelDataType toStruct(RelDataType type) {
return null;
}
+ /** Returns whether a struct occurs inside a collection or map, including
+ * collections and maps nested in struct fields. Plain nested structs do not
+ * require overriding the ARRAY format's comparer. */
+ private static boolean containsStructInCollection(RelDataType type) {
+ if (type.isStruct()) {
+ return type.getFieldList().stream()
+ .anyMatch(f -> containsStructInCollection(f.getType()));
+ }
+ return containsStruct(type);
+ }
+
/** Returns whether any field of {@code rowType} contains a struct value:
* the field is itself a struct, or a collection or map whose elements
* contain one. */
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index 79424fe6e4..eabb37a923 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -130,6 +130,8 @@
import static org.apache.calcite.linq4j.tree.ExpressionType.Subtract;
import static org.apache.calcite.linq4j.tree.ExpressionType.SubtractChecked;
import static org.apache.calcite.linq4j.tree.ExpressionType.UnaryPlus;
+import static
org.apache.calcite.sql.fun.SqlInternalOperators.CYCLE_PATH_APPEND;
+import static
org.apache.calcite.sql.fun.SqlInternalOperators.CYCLE_PATH_CONTAINS;
import static org.apache.calcite.sql.fun.SqlInternalOperators.LITERAL_AGG;
import static org.apache.calcite.sql.fun.SqlInternalOperators.THROW_UNLESS;
import static org.apache.calcite.sql.fun.SqlLibraryOperators.ACOSD;
@@ -1108,6 +1110,8 @@ void populate2() {
defineMethod(STRUCT_ACCESS, BuiltInMethod.STRUCT_ACCESS.method,
NullPolicy.SEMI_STRICT);
defineMethod(MEMBER_OF, BuiltInMethod.MEMBER_OF.method, NullPolicy.NONE);
defineMethod(ARRAY_APPEND, BuiltInMethod.ARRAY_APPEND.method,
NullPolicy.ARG0);
+ defineMethod(CYCLE_PATH_APPEND, BuiltInMethod.ARRAY_APPEND.method,
NullPolicy.ARG0);
+ defineMethod(CYCLE_PATH_CONTAINS,
BuiltInMethod.CYCLE_PATH_CONTAINS.method, NullPolicy.NONE);
defineMethod(ARRAY_COMPACT, BuiltInMethod.ARRAY_COMPACT.method,
NullPolicy.STRICT);
defineMethod(ARRAY_CONTAINS, BuiltInMethod.ARRAY_CONTAINS.method,
NullPolicy.SEMI_STRICT);
defineMethod(ARRAY_DISTINCT, BuiltInMethod.ARRAY_DISTINCT.method,
NullPolicy.STRICT);
diff --git a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
index cc27cb5c20..00e7667f80 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -935,6 +935,24 @@ ExInst<CalciteException>
illegalArgumentForTableFunctionCall(String a0,
@BaseMessage("A recursive query only supports binary UNION [ALL] operator")
ExInst<SqlValidatorException> recursiveWithMustHaveTwoChildUnionSetOp();
+ @BaseMessage("CYCLE requires WITH RECURSIVE")
+ ExInst<SqlValidatorException> cycleRequiresRecursive();
+
+ @BaseMessage("CYCLE requires a UNION [ALL] with a recursive SELECT
containing exactly one direct reference to its WITH item")
+ ExInst<SqlValidatorException> cycleRequiresRecursiveSelect();
+
+ @BaseMessage("CYCLE is not supported with aggregation, DISTINCT, or window
functions in the recursive SELECT")
+ ExInst<SqlValidatorException> cycleRecursiveSelectNotSupported();
+
+ @BaseMessage("CYCLE column ''{0}'' is not a column of WITH item ''{1}''")
+ ExInst<SqlValidatorException> cycleColumnNotFound(String column, String
withItem);
+
+ @BaseMessage("CYCLE generated column ''{0}'' conflicts with another column")
+ ExInst<SqlValidatorException> cycleColumnConflict(String column);
+
+ @BaseMessage("CYCLE mark and default must be distinct, non-null Boolean or
compatible single-character string literals")
+ ExInst<SqlValidatorException> cycleInvalidMarkValues();
+
@BaseMessage("First column of ORDER BY must be of type TIMESTAMP")
ExInst<SqlValidatorException> firstColumnOfOrderByMustBeTimestamp();
diff --git a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
index 5e1988a996..4dfc4f542f 100644
--- a/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
+++ b/core/src/main/java/org/apache/calcite/runtime/SqlFunctions.java
@@ -7148,6 +7148,18 @@ public static List compact(List list) {
return result;
}
+ /** Tests whether a recursive CTE path contains a key tuple.
+ * Implements the condition of {@code CASE WHEN key IN (path) THEN ...}:
+ * only TRUE comparisons close a cycle; UNKNOWN does not. */
+ public static boolean cyclePathContains(List<?> path, Object key) {
+ for (Object previous : path) {
+ if (Boolean.TRUE.equals(rowEq(previous, key))) {
+ return true;
+ }
+ }
+ return false;
+ }
+
/** Support the ARRAY_APPEND function. */
public static List arrayAppend(List list, Object element) {
final List result = new ArrayList(list.size() + 1);
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlCycleClause.java
b/core/src/main/java/org/apache/calcite/sql/SqlCycleClause.java
new file mode 100644
index 0000000000..3e347dbe45
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/SqlCycleClause.java
@@ -0,0 +1,113 @@
+/*
+ * 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.calcite.sql;
+
+import org.apache.calcite.sql.parser.SqlParserPos;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.List;
+
+import static java.util.Objects.requireNonNull;
+
+/** SQL-standard cycle detection clause of a recursive common table expression.
+ *
+ * <p>Syntax: {@code CYCLE columns SET mark [TO value DEFAULT default] USING
path}.
+ * Omitted mark values are represented by TRUE and FALSE literals.
+ * The mark and path columns are generated columns, not members of the WITH
+ * item's explicit column list. */
+public class SqlCycleClause extends SqlCall {
+ private static final SqlSpecialOperator OPERATOR =
+ new SqlSpecialOperator("CYCLE", SqlKind.CYCLE) {
+ @Override public SqlCall createCall(@Nullable SqlLiteral qualifier,
+ SqlParserPos pos, @Nullable SqlNode... operands) {
+ return new SqlCycleClause(pos,
+ (SqlNodeList) requireNonNull(operands[0]),
+ (SqlIdentifier) requireNonNull(operands[1]),
+ requireNonNull(operands[2]), requireNonNull(operands[3]),
+ (SqlIdentifier) requireNonNull(operands[4]));
+ }
+ };
+
+ public SqlNodeList columns;
+ public SqlIdentifier markColumn;
+ public SqlNode markValue;
+ public SqlNode defaultValue;
+ public SqlIdentifier pathColumn;
+
+ public SqlCycleClause(SqlParserPos pos, SqlNodeList columns,
+ SqlIdentifier markColumn, SqlNode markValue, SqlNode defaultValue,
+ SqlIdentifier pathColumn) {
+ super(pos);
+ this.columns = columns;
+ this.markColumn = markColumn;
+ this.markValue = markValue;
+ this.defaultValue = defaultValue;
+ this.pathColumn = pathColumn;
+ }
+
+ @Override public SqlOperator getOperator() {
+ return OPERATOR;
+ }
+
+ @Override public List<SqlNode> getOperandList() {
+ return ImmutableList.of(columns, markColumn, markValue, defaultValue,
pathColumn);
+ }
+
+ @Override public void setOperand(int i, @Nullable SqlNode operand) {
+ requireNonNull(operand, "operand");
+ switch (i) {
+ case 0:
+ columns = (SqlNodeList) operand;
+ break;
+ case 1:
+ markColumn = (SqlIdentifier) operand;
+ break;
+ case 2:
+ markValue = operand;
+ break;
+ case 3:
+ defaultValue = operand;
+ break;
+ case 4:
+ pathColumn = (SqlIdentifier) operand;
+ break;
+ default:
+ throw new AssertionError(i);
+ }
+ }
+
+ @Override public void unparse(SqlWriter writer, int leftPrec, int rightPrec)
{
+ writer.keyword("CYCLE");
+ final SqlWriter.Frame frame = writer.startList("", "");
+ for (SqlNode column : columns) {
+ writer.sep(",");
+ column.unparse(writer, 0, 0);
+ }
+ writer.endList(frame);
+ writer.keyword("SET");
+ markColumn.unparse(writer, 0, 0);
+ writer.keyword("TO");
+ markValue.unparse(writer, 0, 0);
+ writer.keyword("DEFAULT");
+ defaultValue.unparse(writer, 0, 0);
+ writer.keyword("USING");
+ pathColumn.unparse(writer, 0, 0);
+ }
+}
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
index 92bbfe4c59..e0d2786e68 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlKind.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlKind.java
@@ -204,6 +204,9 @@ public enum SqlKind {
/** Item in WITH clause. */
WITH_ITEM,
+ /** CYCLE clause of a recursive WITH item. */
+ CYCLE,
+
/** Represents a recursive CTE as a table ref. */
WITH_ITEM_TABLE_REF,
@@ -1492,7 +1495,7 @@ public enum SqlKind {
LITERAL_CHAIN, JDBC_FN, PRECEDING, FOLLOWING, ORDER_BY,
ORDER_BY_ALL,
NULLS_FIRST, NULLS_LAST, COLLECTION_TABLE, TABLESAMPLE,
- VALUES, WITH, WITH_ITEM, ITEM, SKIP_TO_FIRST, SKIP_TO_LAST,
+ VALUES, WITH, WITH_ITEM, CYCLE, ITEM, SKIP_TO_FIRST,
SKIP_TO_LAST,
JSON_VALUE_EXPRESSION, UNNEST),
SET_QUERY, AGGREGATE, DML, DDL));
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWithItem.java
b/core/src/main/java/org/apache/calcite/sql/SqlWithItem.java
index 89fe2e8d9c..cee07fbece 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlWithItem.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlWithItem.java
@@ -25,13 +25,14 @@
/**
* An item in a WITH clause of a query.
- * It has a name, an optional column list, and a query.
+ * It has a name, an optional column list, a query, and an optional CYCLE
clause.
*/
public class SqlWithItem extends SqlCall {
public SqlIdentifier name;
public @Nullable SqlNodeList columnList; // may be null
public SqlLiteral recursive;
public SqlNode query;
+ public @Nullable SqlCycleClause cycleClause;
@Deprecated // to be removed before 2.0
public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
@@ -43,11 +44,18 @@ public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
@Nullable SqlNodeList columnList, SqlNode query,
SqlLiteral recursive) {
+ this(pos, name, columnList, query, recursive, null);
+ }
+
+ public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
+ @Nullable SqlNodeList columnList, SqlNode query,
+ SqlLiteral recursive, @Nullable SqlCycleClause cycleClause) {
super(pos);
this.name = name;
this.columnList = columnList;
this.recursive = recursive;
this.query = query;
+ this.cycleClause = cycleClause;
}
//~ Methods ----------------------------------------------------------------
@@ -58,7 +66,7 @@ public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
@SuppressWarnings("nullness")
@Override public List<SqlNode> getOperandList() {
- return ImmutableNullableList.of(name, columnList, query, recursive);
+ return ImmutableNullableList.of(name, columnList, query, recursive,
cycleClause);
}
@SuppressWarnings("assignment.type.incompatible")
@@ -76,6 +84,9 @@ public SqlWithItem(SqlParserPos pos, SqlIdentifier name,
case 3:
recursive = (SqlLiteral) operand;
break;
+ case 4:
+ cycleClause = (SqlCycleClause) operand;
+ break;
default:
throw new AssertionError(i);
}
@@ -111,15 +122,19 @@ private static class SqlWithItemOperator extends
SqlSpecialOperator {
}
writer.keyword("AS");
withItem.query.unparse(writer, MDX_PRECEDENCE, MDX_PRECEDENCE);
+ if (withItem.cycleClause != null) {
+ withItem.cycleClause.unparse(writer, 0, 0);
+ }
}
@SuppressWarnings("argument.type.incompatible")
@Override public SqlCall createCall(@Nullable SqlLiteral functionQualifier,
SqlParserPos pos, @Nullable SqlNode... operands) {
assert functionQualifier == null;
- assert operands.length == 4;
+ assert operands.length == 4 || operands.length == 5;
return new SqlWithItem(pos, (SqlIdentifier) operands[0],
- (SqlNodeList) operands[1], operands[2], (SqlLiteral) operands[3]);
+ (SqlNodeList) operands[1], operands[2], (SqlLiteral) operands[3],
+ operands.length == 5 ? (SqlCycleClause) operands[4] : null);
}
}
}
diff --git
a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
index d1429f501b..5f798876ae 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlInternalOperators.java
@@ -49,6 +49,16 @@ public abstract class SqlInternalOperators {
private SqlInternalOperators() {
}
+ /** Appends a key tuple to the path of a recursive CTE. */
+ public static final SqlInternalOperator CYCLE_PATH_APPEND =
+ new SqlInternalOperator("$CYCLE_PATH_APPEND", SqlKind.OTHER_FUNCTION,
+ 2, true, ReturnTypes.ARG0, null, OperandTypes.ANY_ANY);
+
+ /** Tests whether a key tuple occurs in a recursive CTE's path. */
+ public static final SqlInternalOperator CYCLE_PATH_CONTAINS =
+ new SqlInternalOperator("$CYCLE_PATH_CONTAINS", SqlKind.OTHER_FUNCTION,
+ 2, true, ReturnTypes.BOOLEAN_NOT_NULL, null, OperandTypes.ANY_ANY);
+
/** Similar to {@link SqlStdOperatorTable#ROW}, but does not print "ROW".
*
* <p>For arguments [1, TRUE], ROW would print "{@code ROW (1, TRUE)}",
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/CycleRewriter.java
b/core/src/main/java/org/apache/calcite/sql/validate/CycleRewriter.java
new file mode 100644
index 0000000000..91c26e36e8
--- /dev/null
+++ b/core/src/main/java/org/apache/calcite/sql/validate/CycleRewriter.java
@@ -0,0 +1,440 @@
+/*
+ * 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.calcite.sql.validate;
+
+import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rel.type.RelDataTypeField;
+import org.apache.calcite.sql.JoinConditionType;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCycleClause;
+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.SqlUtil;
+import org.apache.calcite.sql.SqlWith;
+import org.apache.calcite.sql.SqlWithItem;
+import org.apache.calcite.sql.fun.SqlCase;
+import org.apache.calcite.sql.fun.SqlInternalOperators;
+import org.apache.calcite.sql.fun.SqlRowOperator;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParserPos;
+import org.apache.calcite.sql.type.SqlTypeUtil;
+import org.apache.calcite.sql.util.SqlBasicVisitor;
+import org.apache.calcite.sql.util.SqlShuttle;
+import org.apache.calcite.util.NlsString;
+import org.apache.calcite.util.Util;
+
+import com.google.common.collect.ImmutableList;
+
+import org.checkerframework.checker.nullness.qual.EnsuresNonNullIf;
+import org.checkerframework.checker.nullness.qual.Nullable;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Set;
+
+import static org.apache.calcite.sql.validate.SqlValidatorUtil.EXPR_SUGGESTER;
+import static org.apache.calcite.util.Static.RESOURCE;
+
+import static java.util.Objects.requireNonNull;
+
+/** Lowers CYCLE to a recursive UNION with a mark and an array of key tuples.
+ *
+ * <p>Each row has its own path. The row closing a cycle is emitted, but is
+ * filtered out before evaluating the next recursive step. Fresh syntax nodes
+ * are used because the original query has already been validated. */
+final class CycleRewriter {
+ private static final SqlParserPos POS = SqlParserPos.ZERO;
+ private static final String INPUT = "$CYCLE_INPUT";
+
+ private final SqlValidatorImpl validator;
+ private final SqlWithItem item;
+ private final SqlCycleClause cycle;
+ private final SqlNameMatcher matcher;
+
+ private CycleRewriter(SqlValidatorImpl validator, SqlWithItem item) {
+ this.validator = validator;
+ this.item = item;
+ this.cycle = requireNonNull(item.cycleClause);
+ this.matcher = validator.getCatalogReader().nameMatcher();
+ }
+
+ static SqlWithItem rewrite(SqlValidatorImpl validator, SqlValidatorScope
scope,
+ SqlWithItem item) {
+ return new CycleRewriter(validator, item).rewrite(scope);
+ }
+
+ /** Replaces CYCLE with explicit mark and path columns in both UNION
operands.
+ *
+ * <p>For {@code CYCLE k1, k2 SET mark TO cycleValue DEFAULT defaultValue
+ * USING path}, the transformation is schematically:
+ *
+ * <pre>{@code
+ * seed output:
+ * original columns,
+ * defaultValue AS mark,
+ * ARRAY[ROW(k1, k2)] AS path
+ * recursive branch:
+ * SELECT original output columns,
+ * CASE WHEN contains(previousPath, ROW(k1, k2))
+ * THEN cycleValue ELSE defaultValue END AS mark,
+ * append(previousPath, ROW(k1, k2)) AS path
+ * FROM (
+ * SELECT original projection, previous.path AS previousPath
+ * FROM original sources
+ * WHERE (original condition)
+ * AND previous.mark <> cycleValue
+ * ) AS step
+ * }</pre>
+ *
+ * <p>Here {@code previous} is the recursive CTE reference in the original
+ * FROM clause; the original condition is TRUE if there was no WHERE clause.
+ * The added WHERE condition stops expanding rows already marked as cycles.
+ * The recursive output keys are taken after the user's projection. A row
+ * closing a cycle is therefore returned with its extended path, but cannot
+ * feed the next recursive step. The original UNION [ALL] is retained.
+ * The returned WITH item declares the two additional columns and no longer
+ * has a CYCLE clause. */
+ private SqlWithItem rewrite(SqlValidatorScope scope) {
+ final Reference reference = validateRecursiveQuery();
+ final RelDataType rowType =
validator.getNamespaceOrThrow(item).getRowType();
+ final List<RelDataTypeField> keys = validateKeys(rowType);
+ final List<String> names = rowType.getFieldNames();
+ validateGeneratedNames(names);
+ final RelDataType markType = validateMarkType(scope);
+
+ final SqlNode query = prepareQuery(reference);
+ final SqlCall union = (SqlCall) queryBody(query);
+ union.setOperand(0, rewriteSeed(union.operand(0), names, keys, markType));
+ union.setOperand(1, rewriteStep(union.operand(1), reference, names, keys,
markType));
+
+ final List<SqlNode> outputNames = identifiers(names);
+ outputNames.add(copy(cycle.markColumn));
+ outputNames.add(copy(cycle.pathColumn));
+ return new SqlWithItem(item.getParserPosition(), item.name,
+ new SqlNodeList(outputNames, POS), query, item.recursive);
+ }
+
+ /** Checks the recursive query's shape without changing its syntax tree. */
+ private Reference validateRecursiveQuery() {
+ final SqlNode body = queryBody(item.query);
+ if (body.getKind() != SqlKind.UNION
+ || !(((SqlCall) body).operand(1) instanceof SqlSelect)) {
+ throw validator.newValidationError(cycle,
RESOURCE.cycleRequiresRecursiveSelect());
+ }
+ final SqlSelect step = ((SqlCall) body).operand(1);
+ final List<SqlNode> references = new ArrayList<>();
+ findDirectReferences(step.getFrom(), references);
+ if (references.size() != 1 || recursiveReferenceCount() != 1) {
+ throw validator.newValidationError(cycle,
RESOURCE.cycleRequiresRecursiveSelect());
+ }
+ if (validator.isAggregate(step) || step.isDistinct() ||
!step.getWindowList().isEmpty()
+ || SqlUtil.containsCall(step, call -> call.getKind() == SqlKind.OVER))
{
+ throw validator.newValidationError(cycle,
RESOURCE.cycleRecursiveSelectNotSupported());
+ }
+ return reference(references.get(0));
+ }
+
+ /** Finds direct references on the non-null-generating sides of joins. */
+ private void findDirectReferences(@Nullable SqlNode from, List<SqlNode>
references) {
+ if (isSelfReference(from)) {
+ references.add(from);
+ } else if (from instanceof SqlJoin) {
+ final SqlJoin join = (SqlJoin) from;
+ if (!join.getJoinType().generatesNullsOnLeft()) {
+ findDirectReferences(join.getLeft(), references);
+ }
+ if (!join.getJoinType().generatesNullsOnRight()) {
+ findDirectReferences(join.getRight(), references);
+ }
+ } else if (from != null && from.getKind() == SqlKind.AS
+ && isSelfReference(((SqlCall) from).operand(0))) {
+ references.add(from);
+ }
+ }
+
+ @EnsuresNonNullIf(expression = "#1", result = true)
+ private boolean isSelfReference(@Nullable SqlNode node) {
+ return node instanceof SqlIdentifier && ((SqlIdentifier) node).isSimple()
+ && matcher.matches(((SqlIdentifier) node).getSimple(),
item.name.getSimple());
+ }
+
+ /** Counts resolved references as well, to catch references hidden in
subqueries. */
+ private int recursiveReferenceCount() {
+ final List<SqlIdentifier> references = new ArrayList<>();
+ item.query.accept(new SqlBasicVisitor<Void>() {
+ @Override public Void visit(SqlIdentifier id) {
+ final SqlValidatorNamespace ns = validator.getNamespace(id);
+ if (ns != null) {
+ final SqlNode node = ns.resolve().getNode();
+ if (node instanceof SqlWithItemTableRef
+ && ((SqlWithItemTableRef) node).getWithItem() == item) {
+ references.add(id);
+ }
+ }
+ return null;
+ }
+ });
+ return references.size();
+ }
+
+ private List<RelDataTypeField> validateKeys(RelDataType rowType) {
+ final List<RelDataTypeField> keys = new ArrayList<>();
+ for (SqlNode node : cycle.columns) {
+ final SqlIdentifier key = (SqlIdentifier) node;
+ final RelDataTypeField field = matcher.field(rowType, key.getSimple());
+ if (field == null) {
+ throw validator.newValidationError(key,
+ RESOURCE.cycleColumnNotFound(key.getSimple(),
item.name.getSimple()));
+ }
+ if (keys.contains(field)) {
+ throw validator.newValidationError(key,
+ RESOURCE.duplicateNameInColumnList(key.getSimple()));
+ }
+ keys.add(field);
+ }
+ return keys;
+ }
+
+ private void validateGeneratedNames(List<String> names) {
+ if (matcher.matches(cycle.markColumn.getSimple(),
cycle.pathColumn.getSimple())) {
+ throw validator.newValidationError(cycle.pathColumn,
+ RESOURCE.cycleColumnConflict(cycle.pathColumn.getSimple()));
+ }
+ for (SqlIdentifier generated : ImmutableList.of(cycle.markColumn,
cycle.pathColumn)) {
+ if (matcher.indexOf(names, generated.getSimple()) >= 0) {
+ throw validator.newValidationError(generated,
+ RESOURCE.cycleColumnConflict(generated.getSimple()));
+ }
+ }
+ }
+
+ private RelDataType validateMarkType(SqlValidatorScope scope) {
+ if (!(cycle.markValue instanceof SqlLiteral)
+ || !(cycle.defaultValue instanceof SqlLiteral)) {
+ throw validator.newValidationError(cycle,
RESOURCE.cycleInvalidMarkValues());
+ }
+ final Object mark = ((SqlLiteral) cycle.markValue).getValue();
+ final Object defaultMark = ((SqlLiteral) cycle.defaultValue).getValue();
+ final boolean valid;
+ if (mark instanceof Boolean && defaultMark instanceof Boolean) {
+ valid = !mark.equals(defaultMark);
+ } else if (mark instanceof NlsString && defaultMark instanceof NlsString) {
+ final String markString = ((NlsString) mark).getValue();
+ final String defaultString = ((NlsString) defaultMark).getValue();
+ valid = markString.length() == 1 && defaultString.length() == 1
+ && !markString.equals(defaultString);
+ } else {
+ valid = false;
+ }
+ if (!valid) {
+ throw validator.newValidationError(cycle,
RESOURCE.cycleInvalidMarkValues());
+ }
+ final RelDataType type =
+ validator.getTypeFactory().leastRestrictive(
+ ImmutableList.of(validator.deriveType(scope, cycle.markValue),
+ validator.deriveType(scope, cycle.defaultValue)));
+ if (type == null) {
+ throw validator.newValidationError(cycle,
RESOURCE.cycleInvalidMarkValues());
+ }
+ return type;
+ }
+
+ /** Chooses generated column names, accounting for an explicit alias column
list. */
+ private Reference reference(SqlNode source) {
+ final SqlCall as = source instanceof SqlCall ? (SqlCall) source : null;
+ final String alias = requireNonNull(SqlValidatorUtil.alias(source),
"alias");
+ final Set<String> aliases = matcher.createSet();
+ if (as != null && as.operandCount() > 2) {
+ aliases.addAll(
+ SqlIdentifier.simpleNames(as.getOperandList().subList(2,
as.operandCount())));
+ }
+ final String mark =
+ SqlValidatorUtil.uniquify(cycle.markColumn.getSimple(), aliases,
EXPR_SUGGESTER);
+ final String path =
+ SqlValidatorUtil.uniquify(cycle.pathColumn.getSimple(), aliases,
EXPR_SUGGESTER);
+ return new Reference(source, qualified(alias, mark), qualified(alias,
path));
+ }
+
+ /** Copies the query and adjusts joins and aliases before adding generated
columns. */
+ private SqlNode prepareQuery(Reference reference) {
+ final SqlSelect step = ((SqlCall) queryBody(item.query)).operand(1);
+ final SqlNodeList stepSelectList = step.getSelectList();
+ final SelectScope stepScope =
requireNonNull(validator.getRawSelectScope(step));
+ // Validation saves the expanded projection even if identifier expansion
is disabled.
+ final SqlNodeList expandedSelectList =
+ new SqlNodeList(requireNonNull(stepScope.getExpandedSelectList()),
+ stepSelectList.getParserPosition());
+ final SqlShuttle shuttle = new DeepCopier() {
+ @Override public SqlNode visit(SqlNodeList nodeList) {
+ return requireNonNull(
+ super.visit(nodeList == stepSelectList ? expandedSelectList :
nodeList));
+ }
+
+ @Override public SqlNode visit(SqlCall call) {
+ final SqlCall result = (SqlCall) super.visit(call);
+ if (call instanceof SqlJoin && ((SqlJoin) call).isNatural()) {
+ makeJoinExplicit((SqlJoin) call, (SqlJoin) result);
+ }
+ if (call == reference.source && call.operandCount() > 2) {
+ final List<SqlNode> operands = new
ArrayList<>(result.getOperandList());
+ operands.add(new SqlIdentifier(Util.last(reference.mark.names),
POS));
+ operands.add(new SqlIdentifier(Util.last(reference.path.names),
POS));
+ return result.getOperator().createCall(result.getParserPosition(),
operands);
+ }
+ return result;
+ }
+ };
+ return requireNonNull(item.query.accept(shuttle));
+ }
+
+ private void makeJoinExplicit(SqlJoin original, SqlJoin copy) {
+ final List<String> names = requireNonNull(validator.usingNames(original));
+ copy.setOperand(1, SqlLiteral.createBoolean(false, POS));
+ copy.setOperand(4,
+ (names.isEmpty() ? JoinConditionType.ON :
JoinConditionType.USING).symbol(POS));
+ copy.setOperand(5, names.isEmpty() ? SqlLiteral.createBoolean(true, POS)
+ : new SqlNodeList(identifiers(names), POS));
+ }
+
+ private SqlSelect rewriteSeed(SqlNode seed, List<String> names,
+ List<RelDataTypeField> keys, RelDataType markType) {
+ final List<SqlNode> columns = columns(names);
+ columns.add(cast(copy(cycle.defaultValue), markType));
+ columns.add(SqlStdOperatorTable.ARRAY_VALUE_CONSTRUCTOR.createCall(POS,
key(keys)));
+ return select(columns, alias(seed, names));
+ }
+
+ private SqlSelect rewriteStep(SqlSelect step, Reference reference,
List<String> names,
+ List<RelDataTypeField> keys, RelDataType markType) {
+ excludeCompletedPaths(step, reference, markType);
+ final List<String> inputNames = new ArrayList<>(names);
+ inputNames.add(cycle.pathColumn.getSimple());
+ final List<SqlNode> columns = columns(names);
+ columns.add(cycleMark(keys, markType));
+ columns.add(
+ SqlInternalOperators.CYCLE_PATH_APPEND.createCall(POS, previousPath(),
key(keys)));
+ return select(columns, alias(step, inputNames));
+ }
+
+ /** Filters closed paths and carries the previous path through the user's
projection. */
+ private void excludeCompletedPaths(SqlSelect step, Reference reference,
RelDataType markType) {
+ final SqlNode mark = cast(copy(cycle.markValue), markType);
+ final SqlNode live =
+ SqlStdOperatorTable.NOT_EQUALS.createCall(POS, reference.mark, mark);
+ step.setWhere(step.getWhere() == null ? live
+ : SqlStdOperatorTable.AND.createCall(POS, step.getWhere(), live));
+ final List<SqlNode> columns = new ArrayList<>(step.getSelectList());
+ columns.add(reference.path);
+ step.setSelectList(new SqlNodeList(columns, POS));
+ }
+
+ private SqlNode cycleMark(List<RelDataTypeField> keys, RelDataType markType)
{
+ final SqlNode contains =
+ SqlInternalOperators.CYCLE_PATH_CONTAINS.createCall(POS,
previousPath(), key(keys));
+ return new SqlCase(POS, null, SqlNodeList.of(contains),
+ SqlNodeList.of(cast(copy(cycle.markValue), markType)),
+ cast(copy(cycle.defaultValue), markType));
+ }
+
+ private SqlIdentifier previousPath() {
+ return qualified(INPUT, cycle.pathColumn.getSimple());
+ }
+
+ private static SqlNode queryBody(SqlNode query) {
+ while (query instanceof SqlWith) {
+ query = ((SqlWith) query).body;
+ }
+ return query;
+ }
+
+ private static SqlNode key(List<RelDataTypeField> keys) {
+ return new SqlRowOperator("ROW", Util.transform(keys,
RelDataTypeField::getName))
+ .createCall(POS,
+ Util.transform(keys, field -> cast(qualified(INPUT, field.getName()),
field.getType())));
+ }
+
+ private static SqlNode cast(SqlNode value, RelDataType type) {
+ final SqlNode typeSpec = SqlTypeUtil.isInterval(type)
+ ? SqlNode.clone(type.getIntervalQualifier())
+ : SqlTypeUtil.convertTypeToSpec(type);
+ return SqlStdOperatorTable.CAST.createCall(POS, value, typeSpec);
+ }
+
+ private static SqlIdentifier qualified(String table, String column) {
+ return new SqlIdentifier(ImmutableList.of(table, column), POS);
+ }
+
+ private static List<SqlNode> columns(List<String> names) {
+ return new ArrayList<>(Util.transform(names, name -> qualified(INPUT,
name)));
+ }
+
+ private static List<SqlNode> identifiers(List<String> names) {
+ return new ArrayList<>(Util.transform(names, name -> new
SqlIdentifier(name, POS)));
+ }
+
+ private static SqlNode alias(SqlNode query, List<String> names) {
+ final List<SqlNode> operands = new ArrayList<>();
+ operands.add(query);
+ operands.add(new SqlIdentifier(INPUT, POS));
+ operands.addAll(identifiers(names));
+ return SqlStdOperatorTable.AS.createCall(POS, operands);
+ }
+
+ private static SqlSelect select(List<SqlNode> columns, SqlNode from) {
+ return new SqlSelect(POS, null, new SqlNodeList(columns, POS), from,
+ null, null, null, null, null, null, null, null, null);
+ }
+
+ private static SqlNode copy(SqlNode node) {
+ return requireNonNull(node.accept(new DeepCopier()));
+ }
+
+ /** Original recursive table reference and its qualified generated columns.
*/
+ private static class Reference {
+ final SqlNode source;
+ final SqlIdentifier mark;
+ final SqlIdentifier path;
+
+ Reference(SqlNode source, SqlIdentifier mark, SqlIdentifier path) {
+ this.source = source;
+ this.mark = mark;
+ this.path = path;
+ }
+ }
+
+ /** Copies syntax nodes without reusing the validator's cached call types. */
+ private static class DeepCopier extends SqlShuttle {
+ @Override public SqlNode visit(SqlIdentifier id) {
+ return SqlNode.clone(id);
+ }
+
+ @Override public SqlNode visit(SqlLiteral literal) {
+ return SqlNode.clone(literal);
+ }
+
+ @Override public SqlNode visit(SqlCall call) {
+ final CallCopyingArgHandler handler = new CallCopyingArgHandler(call,
true);
+ call.getOperator().acceptCall(this, call, false, handler);
+ return handler.result();
+ }
+ }
+}
diff --git
a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 4c21d04377..85d7bc8bb4 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -52,6 +52,7 @@
import org.apache.calcite.sql.SqlBasicCall;
import org.apache.calcite.sql.SqlCall;
import org.apache.calcite.sql.SqlCallBinding;
+import org.apache.calcite.sql.SqlCycleClause;
import org.apache.calcite.sql.SqlDataTypeSpec;
import org.apache.calcite.sql.SqlDelete;
import org.apache.calcite.sql.SqlDynamicParam;
@@ -3756,27 +3757,43 @@ private void registerWith(
scopes.put(with, parentScope);
SqlValidatorScope scope = parentScope;
- for (SqlNode withItem_ : with.withList) {
- final SqlWithItem withItem = (SqlWithItem) withItem_;
-
- final boolean isRecursiveWith = withItem.recursive.booleanValue();
- final SqlValidatorScope withScope =
- new WithScope(scope, withItem,
- isRecursiveWith ? new WithRecursiveScope(scope, withItem) :
null);
- scopes.put(withItem, withScope);
-
- registerQuery(scope, null, withItem.query,
- withItem.recursive.booleanValue() ? withItem : with,
withItem.name.getSimple(),
- forceNullable);
- registerNamespace(null, alias,
- new WithItemNamespace(this, withItem, enclosingNode),
- false);
+ for (int i = 0; i < with.withList.size(); i++) {
+ SqlWithItem withItem = (SqlWithItem) with.withList.get(i);
+ final SqlCycleClause cycleClause = withItem.cycleClause;
+ if (cycleClause != null && !withItem.recursive.booleanValue()) {
+ throw newValidationError(cycleClause,
RESOURCE.cycleRequiresRecursive());
+ }
+ SqlValidatorScope withScope =
+ registerWithItem(scope, with, withItem, enclosingNode, alias,
forceNullable);
+ if (withItem.cycleClause != null) {
+ // Validate and expand the user's projection before adding the
generated
+ // columns. In particular, SELECT * must not pick up the path or mark.
+ validateWithItem(withItem);
+ withItem = CycleRewriter.rewrite(this, scope, withItem);
+ with.withList.set(i, withItem);
+ withScope = registerWithItem(scope, with, withItem, enclosingNode,
alias, forceNullable);
+ }
scope = withScope;
}
registerQuery(scope, null, with.body, enclosingNode, alias, forceNullable,
checkUpdate);
}
+ private SqlValidatorScope registerWithItem(SqlValidatorScope scope, SqlWith
with,
+ SqlWithItem withItem, SqlNode enclosingNode, @Nullable String alias,
+ boolean forceNullable) {
+ final boolean recursive = withItem.recursive.booleanValue();
+ final SqlValidatorScope withScope =
+ new WithScope(scope, withItem,
+ recursive ? new WithRecursiveScope(scope, withItem) : null);
+ scopes.put(withItem, withScope);
+ registerQuery(scope, null, withItem.query,
+ recursive ? withItem : with, withItem.name.getSimple(), forceNullable);
+ registerNamespace(null, alias,
+ new WithItemNamespace(this, withItem, enclosingNode), false);
+ return withScope;
+ }
+
@Override public boolean isAggregate(SqlSelect select) {
if (getAggregate(select) != null) {
return true;
diff --git a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
index 004537b2ad..4f4a021c6d 100644
--- a/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
+++ b/core/src/main/java/org/apache/calcite/util/BuiltInMethod.java
@@ -923,6 +923,7 @@ public enum BuiltInMethod {
SUBMULTISET_OF(SqlFunctions.class, "submultisetOf", Collection.class,
Collection.class),
ARRAY_APPEND(SqlFunctions.class, "arrayAppend", List.class, Object.class),
+ CYCLE_PATH_CONTAINS(SqlFunctions.class, "cyclePathContains", List.class,
Object.class),
ARRAY_COMPACT(SqlFunctions.class, "compact", List.class),
ARRAY_CONTAINS(SqlFunctions.class, "arrayContains", List.class,
Object.class),
ARRAY_DISTINCT(SqlFunctions.class, "distinct", List.class),
diff --git
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
index c90ac9c4ed..2e6e56fb6b 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -70,6 +70,12 @@ IncompatibleCharset=Cannot apply operation ''{0}'' to
strings with different cha
InvalidOrderByPos=ORDER BY is only allowed on top-level SELECT
RecursiveWithMustHaveUnionSetOp=A recursive query only supports UNION [ALL]
operator
RecursiveWithMustHaveTwoChildUnionSetOp=A recursive query only supports binary
UNION [ALL] operator
+CycleRequiresRecursive=CYCLE requires WITH RECURSIVE
+CycleRequiresRecursiveSelect=CYCLE requires a UNION [ALL] with a recursive
SELECT containing exactly one direct reference to its WITH item
+CycleRecursiveSelectNotSupported=CYCLE is not supported with aggregation,
DISTINCT, or window functions in the recursive SELECT
+CycleColumnNotFound=CYCLE column ''{0}'' is not a column of WITH item ''{1}''
+CycleColumnConflict=CYCLE generated column ''{0}'' conflicts with another
column
+CycleInvalidMarkValues=CYCLE mark and default must be distinct, non-null
Boolean or compatible single-character string literals
UnknownIdentifier=Unknown identifier ''{0}''
UnknownField=Unknown field ''{0}''
UnknownTargetColumn=Unknown target column ''{0}''
diff --git
a/core/src/test/java/org/apache/calcite/adapter/enumerable/PhysTypeTest.java
b/core/src/test/java/org/apache/calcite/adapter/enumerable/PhysTypeTest.java
index ed61ad90fc..9bc6da7764 100644
--- a/core/src/test/java/org/apache/calcite/adapter/enumerable/PhysTypeTest.java
+++ b/core/src/test/java/org/apache/calcite/adapter/enumerable/PhysTypeTest.java
@@ -25,6 +25,7 @@
import org.apache.calcite.rel.RelCollations;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.util.BuiltInMethod;
import com.google.common.collect.ImmutableList;
@@ -41,6 +42,42 @@
public final class PhysTypeTest {
private static final JavaTypeFactory TYPE_FACTORY = new
JavaTypeFactoryImpl();
+ @Test void testArrayComparerForPlainStructsAndScalarCollections() {
+ final RelDataType integer =
TYPE_FACTORY.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType row = TYPE_FACTORY.builder().add("n", integer).build();
+ for (RelDataType columnType : ImmutableList.of(integer, row,
+ TYPE_FACTORY.builder().add("r", row).build(),
+ TYPE_FACTORY.createArrayType(integer, -1),
+ TYPE_FACTORY.createMapType(integer, integer),
+ TYPE_FACTORY.builder().add("a", TYPE_FACTORY.createArrayType(integer,
-1)).build())) {
+ assertComparer(columnType, JavaRowFormat.ARRAY,
BuiltInMethod.ARRAY_COMPARER);
+ }
+ // Formats without their own comparer still need the existing ROW fallback.
+ assertComparer(row, JavaRowFormat.LIST, BuiltInMethod.DEEP_COMPARER);
+ assertComparer(row, JavaRowFormat.SCALAR, BuiltInMethod.DEEP_COMPARER);
+ }
+
+ @Test void testDeepComparerForStructsInCollections() {
+ final RelDataType integer =
TYPE_FACTORY.createSqlType(SqlTypeName.INTEGER);
+ final RelDataType row = TYPE_FACTORY.builder().add("n", integer).build();
+ final RelDataType array = TYPE_FACTORY.createArrayType(row, -1);
+ for (RelDataType columnType : ImmutableList.of(array,
+ TYPE_FACTORY.createMultisetType(row, -1),
+ TYPE_FACTORY.createMapType(integer, row),
+ TYPE_FACTORY.createMapType(row, integer),
+ TYPE_FACTORY.createArrayType(array, -1),
+ TYPE_FACTORY.builder().add("a", array).build())) {
+ assertComparer(columnType, JavaRowFormat.ARRAY,
BuiltInMethod.DEEP_COMPARER);
+ }
+ }
+
+ private static void assertComparer(RelDataType columnType, JavaRowFormat
format,
+ BuiltInMethod expected) {
+ final RelDataType rowType = TYPE_FACTORY.builder().add("v",
columnType).build();
+ final PhysType physType = PhysTypeImpl.of(TYPE_FACTORY, rowType, format,
false);
+ assertThat(columnType.toString(), physType.comparer(),
is(Expressions.call(expected.method)));
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2677";>[CALCITE-2677]
* Struct types with one field are not mapped correctly to Java Classes</a>.
*/
diff --git a/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
b/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
index 1ea33de0b2..ba08547ce1 100644
--- a/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
+++ b/core/src/test/java/org/apache/calcite/test/CollectionTypeTest.java
@@ -54,11 +54,49 @@
import static org.junit.jupiter.api.Assertions.fail;
/**
- * Test cases for
+ * Tests for collection types, including
* <a href="https://issues.apache.org/jira/browse/CALCITE-1386";>[CALCITE-1386]
* ITEM operator seems to ignore the value type of collection and assign the
value to Object</a>.
*/
class CollectionTypeTest {
+ // Use a table scan so multi-column results are represented as Object[].
+ // VALUES can produce a different physical format and hide comparator bugs.
+ private static final String ARRAY_OF_ROWS_QUERY =
+ "SELECT ARRAY[ROW(MOD(\"ID\", 2), CAST(NULL AS INTEGER))] AS a,\n"
+ + "\"ARRAYFIELD\"[1] AS n FROM \"T\"";
+
+ @Test void testDistinctArrayOfRows() {
+ withNestedTable()
+ .query("SELECT DISTINCT a, n FROM (" + ARRAY_OF_ROWS_QUERY + ")")
+ .returnsUnordered("A=[{0, null}]; N=100", "A=[{1, null}]; N=100");
+ }
+
+ @Test void testUnionArrayOfRows() {
+ withNestedTable()
+ .query(ARRAY_OF_ROWS_QUERY + " UNION " + ARRAY_OF_ROWS_QUERY)
+ .returnsUnordered("A=[{0, null}]; N=100", "A=[{1, null}]; N=100");
+ }
+
+ @Test void testIntersectArrayOfRows() {
+ withNestedTable()
+ .query(ARRAY_OF_ROWS_QUERY + " INTERSECT " + ARRAY_OF_ROWS_QUERY + "
WHERE \"ID\" = 1")
+ .returnsUnordered("A=[{1, null}]; N=100");
+ }
+
+ @Test void testExceptArrayOfRows() {
+ withNestedTable()
+ .query(ARRAY_OF_ROWS_QUERY + " EXCEPT " + ARRAY_OF_ROWS_QUERY + "
WHERE \"ID\" = 1")
+ .returnsUnordered("A=[{0, null}]; N=100");
+ }
+
+ private static CalciteAssert.AssertThat withNestedTable() {
+ return CalciteAssert.that().with(connection -> {
+ connection.unwrap(CalciteConnection.class).getRootSchema()
+ .add("T", new NestedCollectionTable());
+ return connection;
+ });
+ }
+
@Test void testAccessNestedMap() throws Exception {
Connection connection = setupConnectionWithNestedTable();
diff --git a/core/src/test/java/org/apache/calcite/test/RecursiveCteTest.java
b/core/src/test/java/org/apache/calcite/test/RecursiveCteTest.java
new file mode 100644
index 0000000000..1ace435df9
--- /dev/null
+++ b/core/src/test/java/org/apache/calcite/test/RecursiveCteTest.java
@@ -0,0 +1,514 @@
+/*
+ * 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.calcite.test;
+
+import org.apache.calcite.plan.RelOptUtil;
+import org.apache.calcite.rel.RelNode;
+import org.apache.calcite.sql.SqlCall;
+import org.apache.calcite.sql.SqlCycleClause;
+import org.apache.calcite.sql.SqlIdentifier;
+import org.apache.calcite.sql.SqlKind;
+import org.apache.calcite.sql.SqlLiteral;
+import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlSelect;
+import org.apache.calcite.sql.SqlWith;
+import org.apache.calcite.sql.SqlWithItem;
+import org.apache.calcite.sql.dialect.CalciteSqlDialect;
+import org.apache.calcite.sql.fun.SqlStdOperatorTable;
+import org.apache.calcite.sql.parser.SqlParseException;
+import org.apache.calcite.sql.parser.SqlParser;
+
+import org.junit.jupiter.api.Test;
+import org.junit.jupiter.api.Timeout;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.CsvSource;
+import org.junit.jupiter.params.provider.ValueSource;
+
+import static org.apache.calcite.test.Matchers.isLinux;
+
+import static org.hamcrest.CoreMatchers.containsString;
+import static org.hamcrest.CoreMatchers.is;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.hamcrest.Matchers.hasSize;
+
+import static java.util.Objects.requireNonNull;
+
+/** Examples of recursive CTE parsing, validation and execution, including the
+ * SQL-standard CYCLE clause. */
+@Timeout(30)
+class RecursiveCteTest {
+ private static final String WITH_NUMBERS = "WITH RECURSIVE t(n) AS (\n"
+ + " VALUES (1)\n"
+ + " UNION ALL\n"
+ + " SELECT n + 1 FROM t WHERE n < 3\n"
+ + ")\n";
+
+ private static final String NUMBERS = WITH_NUMBERS + "SELECT * FROM t";
+
+ /** The self-reference is an identifier in the parse tree, not a Java object
+ * reference back to the WITH item. Name resolution happens during
validation.
+ *
+ * <pre>
+ * SqlWith
+ * withList: SqlNodeList
+ * SqlWithItem(name=T, columnList=[N], recursive=true)
+ * query: UNION ALL
+ * VALUES (1)
+ * SqlSelect(n + 1, from=T, where=n < 3)
+ * body: SqlSelect(*, from=T)
+ * </pre>
+ */
+ @Test void testRecursiveCteParseTree() throws SqlParseException {
+ final SqlWith with = (SqlWith) SqlParser.create(NUMBERS).parseQuery();
+ assertThat(with.getKind(), is(SqlKind.WITH));
+ assertThat(with.withList, hasSize(1));
+ final SqlWithItem item = (SqlWithItem) with.withList.get(0);
+ assertThat(item.name.getSimple(), is("T"));
+ assertThat(item.recursive.booleanValue(), is(true));
+ final SqlNodeList columnList = requireNonNull(item.columnList);
+ assertThat(columnList, hasSize(1));
+ assertThat(((SqlIdentifier) columnList.get(0)).getSimple(), is("N"));
+ // CYCLE is the optional fifth operand of a WITH item.
+ assertThat(item.getOperandList(), hasSize(5));
+ final SqlCall union = (SqlCall) item.query;
+ assertThat(union.getOperator(), is(SqlStdOperatorTable.UNION_ALL));
+ assertThat(union.operand(0).getKind(), is(SqlKind.VALUES));
+ final SqlSelect step = union.operand(1);
+ assertThat(((SqlIdentifier) requireNonNull(step.getFrom())).getSimple(),
is("T"));
+ assertThat(requireNonNull(step.getWhere()).getKind(),
is(SqlKind.LESS_THAN));
+ final SqlSelect body = (SqlSelect) with.body;
+ assertThat(((SqlIdentifier) requireNonNull(body.getFrom())).getSimple(),
is("T"));
+ }
+
+ /** RECURSIVE belongs to WITH syntactically, but is copied to each WITH
item. */
+ @Test void testRecursiveKeywordAppliesToEveryWithItem() throws
SqlParseException {
+ final String sql = "WITH RECURSIVE a(n) AS (VALUES (1)),\n"
+ + "b(n) AS (SELECT n FROM a UNION ALL SELECT n + 1 FROM b WHERE n <
3)\n"
+ + "SELECT * FROM b";
+ final SqlWith with = (SqlWith) SqlParser.create(sql).parseQuery();
+ assertThat(with.withList, hasSize(2));
+ assertThat(((SqlWithItem) with.withList.get(0)).recursive.booleanValue(),
is(true));
+ assertThat(((SqlWithItem) with.withList.get(1)).recursive.booleanValue(),
is(true));
+ }
+
+ /** CYCLE follows AS (query) and belongs to that WITH item. */
+ @ParameterizedTest
+ @ValueSource(strings = {
+ "CYCLE n SET is_cycle TO 'Y' DEFAULT 'N' USING cycle_path",
+ "CYCLE n, depth SET is_cycle TO 'Y' DEFAULT 'N' USING cycle_path",
+ "CYCLE n, depth SET is_cycle USING cycle_path"
+ })
+ void testStandardCycleClause(String cycleClause) throws SqlParseException {
+ final String sql = "WITH RECURSIVE t(n, depth) AS (\n"
+ + " VALUES (1, 0)\n"
+ + " UNION ALL\n"
+ + " SELECT MOD(n + 1, 2), depth + 1 FROM t WHERE depth < 3\n"
+ + ")\n"
+ + cycleClause + "\n"
+ + "SELECT * FROM t";
+ final SqlWith with = (SqlWith) SqlParser.create(sql).parseQuery();
+ final SqlCycleClause cycle =
+ requireNonNull(((SqlWithItem) with.withList.get(0)).cycleClause);
+ assertThat(cycle.getKind(), is(SqlKind.CYCLE));
+ assertThat(cycle.getOperandList(), hasSize(5));
+ assertThat(cycle.markColumn.getSimple(), is("IS_CYCLE"));
+ assertThat(cycle.pathColumn.getSimple(), is("CYCLE_PATH"));
+ assertThat(cycle.getParserPosition().getLineNum(), is(6));
+ assertThat(with.toString(), containsString("CYCLE"));
+ final String unparsed =
with.toSqlString(CalciteSqlDialect.DEFAULT).getSql();
+ final SqlWith roundTrip = (SqlWith)
SqlParser.create(unparsed).parseQuery();
+ assertThat(requireNonNull(((SqlWithItem)
roundTrip.withList.get(0)).cycleClause)
+ .columns, hasSize(cycle.columns.size()));
+ }
+
+ @Test void testCycleExecution() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT MOD(n + 1, 3) FROM t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT n, c, CARDINALITY(p) AS depth FROM t")
+ .returnsOrdered("N=1; C=N; DEPTH=1", "N=2; C=N; DEPTH=2",
+ "N=0; C=N; DEPTH=3", "N=1; C=Y; DEPTH=4");
+ }
+
+ @Test void testCycleDefaultMarks() throws SqlParseException {
+ final String sql = "WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n
FROM t)\n"
+ + "CYCLE n SET c USING p SELECT * FROM t";
+ final SqlWith with = (SqlWith) SqlParser.create(sql).parseQuery();
+ final SqlCycleClause cycle =
+ requireNonNull(((SqlWithItem) with.withList.get(0)).cycleClause);
+ assertThat(((SqlLiteral) cycle.markValue).booleanValue(), is(true));
+ assertThat(((SqlLiteral) cycle.defaultValue).booleanValue(), is(false));
+ assertThat(with.toSqlString(CalciteSqlDialect.DEFAULT).getSql(),
+ containsString("TO TRUE DEFAULT FALSE"));
+ CalciteAssert.that().query(sql)
+ .returnsOrdered("N=1; C=false; P=[{1}]", "N=1; C=true; P=[{1}, {1}]");
+ }
+
+ /** Two paths reach D independently; returning to A closes each path. */
+ @Test void testCycleDirectedGraph() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE edges(src, dst) AS (\n"
+ + "VALUES ('A', 'B'), ('A', 'C'), ('B', 'D'), ('C', 'D'), ('D',
'A')),\n"
+ + "walk(node) AS (VALUES ('A')\n"
+ + "UNION ALL SELECT e.dst FROM walk AS w JOIN edges AS e ON e.src
= w.node)\n"
+ + "CYCLE node SET is_cycle TO 'Y' DEFAULT 'N' USING cycle_path\n"
+ + "SELECT node, is_cycle, CARDINALITY(cycle_path) AS depth FROM
walk")
+ .returnsUnordered("NODE=A; IS_CYCLE=N; DEPTH=1",
+ "NODE=B; IS_CYCLE=N; DEPTH=2", "NODE=C; IS_CYCLE=N; DEPTH=2",
+ "NODE=D; IS_CYCLE=N; DEPTH=3", "NODE=D; IS_CYCLE=N; DEPTH=3",
+ "NODE=A; IS_CYCLE=Y; DEPTH=4", "NODE=A; IS_CYCLE=Y; DEPTH=4");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"UNION ALL", "UNION DISTINCT"})
+ void testCycleCompositeKey(String union) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, k, depth) AS (VALUES (0, 0, 0)\n"
+ + union + " SELECT MOD(n + 1, 2), MOD(k + 1, 3), depth + 1 FROM
t\n"
+ + "WHERE depth < 10) CYCLE n, k SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT n, k, depth, c, CARDINALITY(p) AS len FROM t")
+ .returnsOrdered("N=0; K=0; DEPTH=0; C=N; LEN=1",
+ "N=1; K=1; DEPTH=1; C=N; LEN=2",
+ "N=0; K=2; DEPTH=2; C=N; LEN=3",
+ "N=1; K=0; DEPTH=3; C=N; LEN=4",
+ "N=0; K=1; DEPTH=4; C=N; LEN=5",
+ "N=1; K=2; DEPTH=5; C=N; LEN=6",
+ "N=0; K=0; DEPTH=6; C=Y; LEN=7");
+ }
+
+ @Test void testCyclePath() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT * FROM
t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT * FROM t")
+ .returnsOrdered("N=1; C=N; P=[{1}]", "N=1; C=Y; P=[{1}, {1}]");
+ }
+
+ @Test void testCycleUnionDistinctSeedDuplicates() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1), (1) UNION SELECT n FROM
t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t")
+ .returnsOrdered("N=1; C=N; P=[{1}]", "N=1; C=Y; P=[{1}, {1}]");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {
+ "INTERVAL '1' DAY",
+ "INTERVAL '123-4' YEAR(3) TO MONTH",
+ "INTERVAL '123 04:05:06.789' DAY(3) TO SECOND(3)"
+ })
+ void testCycleIntervalKey(String key) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(k, d) AS (VALUES (" + key + ", 0)\n"
+ + "UNION ALL SELECT k, d + 1 FROM t WHERE d < 2)\n"
+ + "CYCLE k SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT d, c, k = " + key + " AS key_matches, CARDINALITY(p) AS
depth FROM t")
+ .returnsOrdered("D=0; C=N; KEY_MATCHES=true; DEPTH=1",
+ "D=1; C=Y; KEY_MATCHES=true; DEPTH=2");
+ }
+
+ @Test void testCycleInferredColumnNames() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t AS (SELECT 1 AS n\n"
+ + "UNION ALL SELECT * FROM t) CYCLE n SET c TO 'Y' DEFAULT 'N'
USING p\n"
+ + "SELECT n, c FROM t")
+ .returnsOrdered("N=1; C=N", "N=1; C=Y");
+ }
+
+ @Test void testCycleColumnAliases() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT w.c FROM t AS w(c))\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT n, c FROM t")
+ .returnsOrdered("N=1; C=N", "N=1; C=Y");
+ }
+
+ @Test void testCycleColumnAliasCollisions() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, k, m) AS (VALUES (1, 2, 3)\n"
+ + "UNION ALL SELECT w.c, w.c0, w.p FROM t AS w(c, c0, p))\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT n, k, m, c, CARDINALITY(p) AS depth FROM t")
+ .returnsOrdered("N=1; K=2; M=3; C=N; DEPTH=1",
+ "N=1; K=2; M=3; C=Y; DEPTH=2");
+ }
+
+ @Test void testCycleWithConjunction() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1), (2), (3)\n"
+ + "UNION ALL SELECT n FROM t WHERE n > 1 AND n < 3)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT n, c FROM t")
+ .returnsUnordered("N=1; C=N", "N=2; C=N", "N=3; C=N",
+ "N=2; C=Y");
+ }
+
+ @Test void testCycleNaturalJoin() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT n FROM t NATURAL JOIN (VALUES (1, TRUE)) AS
v(n, c))\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT n, c FROM t")
+ .returnsOrdered("N=1; C=N", "N=1; C=Y");
+ }
+
+ @Test void testCycleNullKey() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, depth) AS (VALUES (CAST(NULL AS INTEGER),
0)\n"
+ + "UNION ALL SELECT n, depth + 1 FROM t WHERE depth < 3)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT n, depth, c
FROM t")
+ .returnsOrdered("N=null; DEPTH=0; C=N", "N=null; DEPTH=1; C=N",
+ "N=null; DEPTH=2; C=N", "N=null; DEPTH=3; C=N");
+ }
+
+ @Test void testCycleEmptySeed() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (SELECT n FROM (VALUES (1)) AS v(n)
WHERE FALSE\n"
+ + "UNION ALL SELECT n FROM t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t")
+ .returnsCount(0);
+ }
+
+ @ParameterizedTest
+ @CsvSource({"false, *", "true, *", "false, t.*", "true, t.*"})
+ void testCycleStarInRecursiveTerm(boolean identifierExpansion, String
projection) {
+ SqlValidatorTestCase.FIXTURE
+ .withValidatorIdentifierExpansion(identifierExpansion)
+ .withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT " + projection + " FROM t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t")
+ .type("RecordType(INTEGER NOT NULL N, CHAR(1) NOT NULL C, "
+ + "RecordType(INTEGER NOT NULL N) NOT NULL ARRAY NOT NULL P) NOT
NULL");
+ }
+
+ @Test void testCycleRowType() {
+ SqlValidatorTestCase.FIXTURE.withSql(WITH_NUMBERS
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t")
+ .type("RecordType(INTEGER NOT NULL N, CHAR(1) NOT NULL C, "
+ + "RecordType(INTEGER NOT NULL N) NOT NULL ARRAY NOT NULL P) NOT
NULL");
+ }
+
+ @Test void testCyclePathFieldNames() {
+ SqlValidatorTestCase.FIXTURE
+ .withSql("WITH RECURSIVE t(\"nodeId\", k) AS (VALUES (1, 2)\n"
+ + "UNION ALL SELECT * FROM t)\n"
+ + "CYCLE k, \"nodeId\" SET c USING p SELECT * FROM t")
+ .type("RecordType(INTEGER NOT NULL nodeId, INTEGER NOT NULL K, "
+ + "BOOLEAN NOT NULL C, RecordType(INTEGER NOT NULL K, "
+ + "INTEGER NOT NULL nodeId) NOT NULL ARRAY NOT NULL P) NOT NULL");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"SELECT a.n FROM t a JOIN t b ON a.n = b.n",
+ "SELECT a.n FROM (VALUES (1)) v(n) LEFT JOIN t a ON a.n = v.n",
+ "SELECT a.n FROM t a WHERE EXISTS (SELECT 1 FROM t b WHERE b.n = a.n)"})
+ void testCycleInvalidRecursiveReference(String step) {
+ SqlValidatorTestCase.FIXTURE.withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL " + step + ") ^CYCLE n SET c TO 'Y' DEFAULT 'N' USING
p^\n"
+ + "SELECT * FROM t")
+ .fails("CYCLE requires a UNION \\[ALL\\] with a recursive SELECT
containing"
+ + " exactly one direct reference to its WITH item");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings =
+ {"SELECT DISTINCT n FROM t", "SELECT MAX(n) FROM t", "SELECT SUM(n) OVER
() FROM t"})
+ void testCycleInvalidRecursiveSelect(String step) {
+ SqlValidatorTestCase.FIXTURE.withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL " + step + ") ^CYCLE n SET c TO 'Y' DEFAULT 'N' USING
p^\n"
+ + "SELECT * FROM t")
+ .fails("CYCLE is not supported with aggregation, DISTINCT, or window
functions"
+ + " in the recursive SELECT");
+ }
+
+ @Test void testCycleMarkNotVisibleInRecursiveTerm() {
+ SqlValidatorTestCase.FIXTURE
+ .withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT n FROM t WHERE ^c^ = 'N')\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT * FROM t")
+ .fails("Column 'C' not found in any table");
+ }
+
+ @Test void testCyclePathNotVisibleInRecursiveTerm() {
+ SqlValidatorTestCase.FIXTURE
+ .withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT n FROM t WHERE CARDINALITY(^p^) < 3)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT * FROM t")
+ .fails("Column 'P' not found in any table");
+ }
+
+ @Test void testCycleQuotedNames() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE \"Walk\"(\"Node\") AS (VALUES (1)\n"
+ + "UNION ALL SELECT w.\"Node\" FROM \"Walk\" AS w)\n"
+ + "CYCLE \"Node\" SET \"Cycle\" TO 'Y' DEFAULT 'N' USING
\"Path\"\n"
+ + "SELECT \"Node\", \"Cycle\" FROM \"Walk\"")
+ .returnsOrdered("Node=1; Cycle=N", "Node=1; Cycle=Y");
+ }
+
+ @Test void testCycleNoCycle() {
+ CalciteAssert.that()
+ .query(WITH_NUMBERS + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p\n"
+ + "SELECT n, c, CARDINALITY(p) AS depth FROM t")
+ .returnsOrdered("N=1; C=N; DEPTH=1", "N=2; C=N; DEPTH=2",
+ "N=3; C=N; DEPTH=3");
+ }
+
+ @Test void testCycleFollowingWithItem() {
+ CalciteAssert.that()
+ .query(WITH_NUMBERS + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p,\n"
+ + "u AS (SELECT n, c, CARDINALITY(p) AS depth FROM t) SELECT *
FROM u")
+ .returnsOrdered("N=1; C=N; DEPTH=1", "N=2; C=N; DEPTH=2",
+ "N=3; C=N; DEPTH=3");
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"Y", " ", "\u00e9"})
+ void testCycleSingleCharacterMarks(String mark) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n FROM
t)\n"
+ + "CYCLE n SET c TO '" + mark + "' DEFAULT 'N' USING p SELECT n, c
FROM t")
+ .returnsOrdered("N=1; C=N", "N=1; C=" + mark);
+ }
+
+ @ParameterizedTest
+ @ValueSource(booleans = {true, false})
+ void testCycleBooleanMarks(boolean mark) {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n FROM
t)\n"
+ + "CYCLE n SET c TO " + mark + " DEFAULT " + !mark + " USING p
SELECT n, c FROM t")
+ .returnsOrdered("N=1; C=" + !mark, "N=1; C=" + mark);
+ }
+
+ @Test void testCycleWithLocalWith() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n) AS (WITH seed(n) AS (VALUES (1))\n"
+ + "SELECT n FROM seed UNION ALL SELECT n FROM t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT n, c FROM t")
+ .returnsOrdered("N=1; C=N", "N=1; C=Y");
+ }
+
+ @Test void testCyclePlan() {
+ final RelNode rel = SqlToRelFixture.DEFAULT.withSql(WITH_NUMBERS
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t").toRel();
+ final String plan = RelOptUtil.toString(rel);
+ assertThat(plan, containsString("LogicalRepeatUnion(all=[true])"));
+ assertThat(plan, containsString("$CYCLE_PATH_CONTAINS"));
+ assertThat(plan, containsString("$CYCLE_PATH_APPEND"));
+ }
+
+ @ParameterizedTest
+ @CsvSource(delimiter = '|', value = {
+ "CYCLE ^missing^ SET c TO 'Y' DEFAULT 'N' USING p|CYCLE column 'MISSING'
is not a column of WITH item 'T'",
+ "CYCLE n, ^n^ SET c TO 'Y' DEFAULT 'N' USING p|Duplicate name 'N' in
column list",
+ "CYCLE n SET ^n^ TO 'Y' DEFAULT 'N' USING p|CYCLE generated column 'N'
conflicts with another column",
+ "CYCLE n SET c TO 'Y' DEFAULT 'N' USING ^n^|CYCLE generated column 'N'
conflicts with another column",
+ "CYCLE n SET c TO 'Y' DEFAULT 'N' USING ^c^|CYCLE generated column 'C'
conflicts with another column"
+ })
+ void testCycleInvalidColumns(String clause, String error) {
+ SqlValidatorTestCase.FIXTURE.withSql(WITH_NUMBERS + clause + " SELECT *
FROM t")
+ .fails(error);
+ }
+
+ @ParameterizedTest
+ @ValueSource(strings = {"TO NULL DEFAULT 'N'", "TO 'Y' DEFAULT NULL",
+ "TO 'Y' DEFAULT 'Y'", "TO ' ' DEFAULT ' '",
+ "TO '' DEFAULT 'N'", "TO 'Y' DEFAULT ''",
+ "TO 'YY' DEFAULT 'N'", "TO 'Y' DEFAULT 'NN'",
+ "TO TRUE DEFAULT TRUE", "TO FALSE DEFAULT FALSE", "TO TRUE DEFAULT
UNKNOWN",
+ "TO TRUE DEFAULT 'N'", "TO 'Y' DEFAULT FALSE", "TO 1 DEFAULT 0", "TO -1
DEFAULT 0",
+ "TO 1.0 DEFAULT 0.0", "TO 1e0 DEFAULT 0e0",
+ "TO 'Y' DEFAULT 0", "TO n DEFAULT 'N'",
+ "TO X'01' DEFAULT X'0100'", "TO INTERVAL '1' DAY DEFAULT INTERVAL '24'
HOUR"})
+ void testCycleInvalidMark(String values) {
+ SqlValidatorTestCase.FIXTURE.withSql(WITH_NUMBERS
+ + "^CYCLE n SET c " + values + " USING p^ SELECT * FROM t")
+ .fails("CYCLE mark and default must be distinct, non-null Boolean"
+ + " or compatible single-character string literals");
+ }
+
+ @Test void testCycleRequiresRecursive() {
+ SqlValidatorTestCase.FIXTURE.withSql("WITH t(n) AS (VALUES (1))\n"
+ + "^CYCLE n SET c TO 'Y' DEFAULT 'N' USING p^ SELECT * FROM t")
+ .fails("CYCLE requires WITH RECURSIVE");
+ }
+
+ @Test void testCycleRequiresRecursiveReference() {
+ SqlValidatorTestCase.FIXTURE.withSql("WITH RECURSIVE t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT 2) ^CYCLE n SET c TO 'Y' DEFAULT 'N' USING p^\n"
+ + "SELECT * FROM t")
+ .fails("CYCLE requires a UNION \\[ALL\\] with a recursive SELECT
containing"
+ + " exactly one direct reference to its WITH item");
+ }
+
+ @Test void testRecursiveReferenceRequiresRecursiveKeyword() {
+ SqlValidatorTestCase.FIXTURE
+ .withSql("WITH t(n) AS (VALUES (1)\n"
+ + "UNION ALL SELECT n + 1 FROM ^t^ WHERE n < 3)\n"
+ + "SELECT * FROM t")
+ .fails("Object 'T' not found");
+ }
+
+ @Test void testRecursiveReferenceNotVisibleInSeed() {
+ SqlValidatorTestCase.FIXTURE
+ .withSql("WITH RECURSIVE t(n) AS (SELECT n FROM ^t^\n"
+ + "UNION ALL SELECT n + 1 FROM t WHERE n < 3)\n"
+ + "SELECT * FROM t")
+ .fails("Object 'T' not found");
+ }
+
+ @Test void testRecursiveCteRowType() {
+ SqlValidatorTestCase.FIXTURE.withSql(NUMBERS)
+ .type("RecordType(INTEGER NOT NULL N) NOT NULL");
+ }
+
+ /** Both branches write the working table; the recursive branch reads it. */
+ @Test void testRecursiveCtePlan() {
+ final RelNode rel = SqlToRelFixture.DEFAULT.withSql(NUMBERS).toRel();
+ final String expected = "LogicalProject(N=[$0])\n"
+ + " LogicalRepeatUnion(all=[true])\n"
+ + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY],
table=[[T]])\n"
+ + " LogicalValues(tuples=[[{ 1 }]])\n"
+ + " LogicalTableSpool(readType=[LAZY], writeType=[LAZY],
table=[[T]])\n"
+ + " LogicalProject(EXPR$0=[+($0, 1)])\n"
+ + " LogicalFilter(condition=[<($0, 3)])\n"
+ + " LogicalTableScan(table=[[T]])\n";
+ assertThat(RelOptUtil.toString(rel), isLinux(expected));
+ }
+
+ @Test void testRecursiveCteExecution() {
+ CalciteAssert.that()
+ .query(NUMBERS + " ORDER BY n")
+ .returnsOrdered("N=1", "N=2", "N=3");
+ }
+
+ /** UNION removes duplicate whole rows. It does not implement CYCLE n:
+ * n repeats, but the changing depth keeps each row distinct. The explicit
+ * depth bound makes this example terminate without cycle detection. */
+ @Test void testUnionDistinctDoesNotDetectCyclesByKey() {
+ CalciteAssert.that()
+ .query("WITH RECURSIVE t(n, depth) AS (\n"
+ + " VALUES (1, 0)\n"
+ + " UNION\n"
+ + " SELECT MOD(n + 1, 2), depth + 1 FROM t WHERE depth < 3\n"
+ + ")\n"
+ + "SELECT * FROM t ORDER BY depth")
+ .returnsOrdered("N=1; DEPTH=0", "N=0; DEPTH=1",
+ "N=1; DEPTH=2", "N=0; DEPTH=3");
+ }
+}
diff --git a/core/src/test/resources/sql/recursive_queries.iq
b/core/src/test/resources/sql/recursive_queries.iq
index ba1f9ce831..25a5a9508c 100644
--- a/core/src/test/resources/sql/recursive_queries.iq
+++ b/core/src/test/resources/sql/recursive_queries.iq
@@ -504,4 +504,91 @@ FROM a,
!ok
+WITH RECURSIVE graph(src, dst) AS (
+ VALUES ('A', 'B'), ('B', 'C'), ('C', 'A'), ('B', 'D')
+),
+walk(node) AS (
+ VALUES ('A')
+ UNION ALL
+ SELECT g.dst FROM walk w JOIN graph g ON g.src = w.node
+)
+CYCLE node SET is_cycle TO 'Y' DEFAULT 'N' USING cycle_path
+SELECT node, is_cycle, cycle_path FROM walk
+ORDER BY CARDINALITY(cycle_path), node;
++------+----------+----------------------+
+| NODE | IS_CYCLE | CYCLE_PATH |
++------+----------+----------------------+
+| A | N | [{A}] |
+| B | N | [{A}, {B}] |
+| C | N | [{A}, {B}, {C}] |
+| D | N | [{A}, {B}, {D}] |
+| A | Y | [{A}, {B}, {C}, {A}] |
++------+----------+----------------------+
+(5 rows)
+
+!ok
+
+WITH RECURSIVE pairs(n, k) AS (
+ VALUES (1, 1)
+ UNION ALL
+ SELECT n, 3 - k FROM pairs
+)
+CYCLE n, k SET is_cycle USING cycle_path
+SELECT n, k, is_cycle, CARDINALITY(cycle_path) AS depth FROM pairs
+ORDER BY depth;
++---+---+----------+-------+
+| N | K | IS_CYCLE | DEPTH |
++---+---+----------+-------+
+| 1 | 1 | false | 1 |
+| 1 | 2 | false | 2 |
+| 1 | 1 | true | 3 |
++---+---+----------+-------+
+(3 rows)
+
+!ok
+
+WITH RECURSIVE t(n) AS (
+ VALUES (1), (1)
+ UNION DISTINCT
+ SELECT n FROM t
+)
+CYCLE n SET is_cycle USING cycle_path
+SELECT n, is_cycle, cycle_path FROM t
+ORDER BY CARDINALITY(cycle_path);
++---+----------+------------+
+| N | IS_CYCLE | CYCLE_PATH |
++---+----------+------------+
+| 1 | false | [{1}] |
+| 1 | true | [{1}, {1}] |
++---+----------+------------+
+(2 rows)
+
+!ok
+
+WITH RECURSIVE t("nodeId", k) AS (
+ VALUES (1, 2)
+ UNION ALL
+ SELECT "nodeId" + 1, k FROM t WHERE "nodeId" < 2
+)
+CYCLE k, "nodeId" SET is_cycle USING cycle_path
+SELECT u.k, u."nodeId"
+FROM t, UNNEST(t.cycle_path) AS u
+WHERE t."nodeId" = 2
+ORDER BY u."nodeId";
++---+--------+
+| K | nodeId |
++---+--------+
+| 2 | 1 |
+| 2 | 2 |
++---+--------+
+(2 rows)
+
+!ok
+
+WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n FROM t)
+CYCLE n SET is_cycle TO 'Y' DEFAULT 'Y' USING cycle_path
+SELECT * FROM t;
+CYCLE mark and default must be distinct, non-null Boolean or compatible
single-character string literals
+!error
+
# End recursive_queries.iq
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 54e47d58c0..72a0cd6590 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -200,6 +200,8 @@ ## Grammar
name
[ '(' column [, column ]* ')' ]
AS '(' query ')'
+ [ CYCLE column [, column ]* SET markColumn
+ [ TO cycleValue DEFAULT nonCycleValue ] USING pathColumn ]
orderItem:
expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]
@@ -522,6 +524,56 @@ ## Grammar
ON left_table.col = right_table.col
```
+### Cycle detection in recursive queries
+
+`CYCLE` tracks the specified columns along each path through a recursive
+common table expression. It adds two columns after the query's original
+columns: a cycle mark and a path, represented as an array of rows containing
+the key values. The path includes the current row. A row that repeats a key
+on its own path receives the cycle mark and is returned, but is not used in
+the next recursive iteration. Reaching the same key through a different
+path does not by itself constitute a cycle.
+
+For example, this graph contains the cycle A, B, C, A:
+
+```sql
+WITH RECURSIVE
+ edges(src, dst) AS (
+ VALUES ('A', 'B'), ('B', 'C'), ('C', 'A')
+ ),
+ walk(node) AS (
+ VALUES ('A')
+ UNION ALL
+ SELECT e.dst
+ FROM walk AS w JOIN edges AS e ON e.src = w.node
+ ) CYCLE node SET is_cycle TO 'Y' DEFAULT 'N' USING cycle_path
+SELECT node, is_cycle, cycle_path
+FROM walk;
+```
+
+The result contains A, B, C, and A again; only the last row is marked `Y`.
+Its path is `[{A}, {B}, {C}, {A}]`.
+
+Cycle columns must be distinct columns of the CTE. The generated mark and
+path names must be distinct from each other and from the original columns;
+they are not included in the CTE's explicit column list. Mark values must
+be distinct, non-null Boolean literals (`TRUE` and `FALSE`) or compatible
+character string literals of length one, such as `'Y'` and `'N'`.
+If `TO cycleValue DEFAULT nonCycleValue` is omitted, the mark values default
+to `TRUE` and `FALSE`, respectively: `CYCLE node SET is_cycle USING
cycle_path`.
+Key comparisons use SQL row equality:
+a comparison that is UNKNOWN because of a null key does not close a cycle.
+
+In the current implementation, the generated mark and path columns cannot
+be referenced in the recursive term. They are available to queries that
+read the CTE's result, such as the outer `SELECT` in the example above.
+
+`CYCLE` requires `WITH RECURSIVE` and a binary `UNION` or `UNION ALL` whose
+recursive operand is a `SELECT` with exactly one direct reference to the
+CTE. That reference cannot be on the null-generating side of an outer join.
+Aggregation, `DISTINCT`, and window functions in the recursive `SELECT`
+are not supported. Enumerable execution supports both forms of `UNION`.
+
## Keywords
The following is a list of SQL keywords.
diff --git
a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
index be6087e752..3b3b9640bd 100644
--- a/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/testkit/src/main/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2744,6 +2744,82 @@ void checkPeriodPredicate(Checker checker) {
sql(sql).ok(expected);
}
+ @Test void testWithCycle() {
+ sql("WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n FROM t)\n"
+ + "CYCLE n SET c TO 'Y' DEFAULT 'N' USING p SELECT * FROM t")
+ .ok("WITH RECURSIVE `T` (`N`) AS ((VALUES (ROW(1)))\n"
+ + "UNION ALL\n"
+ + "SELECT `N`\n"
+ + "FROM `T`) CYCLE `N` SET `C` TO 'Y' DEFAULT 'N' USING `P` SELECT
*\n"
+ + "FROM `T`");
+ }
+
+ @Test void testWithCycleDefaultMarks() {
+ final String expected = "WITH RECURSIVE `T` (`N`) AS ((VALUES (ROW(1)))\n"
+ + "UNION ALL\n"
+ + "SELECT `N`\n"
+ + "FROM `T`) CYCLE `N` SET `C` TO TRUE DEFAULT FALSE USING `P` SELECT
*\n"
+ + "FROM `T`";
+ final String prefix = "WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT
n FROM t)\n"
+ + "CYCLE n SET c ";
+ sql(prefix + "TO TRUE DEFAULT FALSE USING p SELECT * FROM t").ok(expected);
+ sql(prefix + "USING p SELECT * FROM t").ok(expected);
+ }
+
+ @Test void testWithCycleCompositeKey() {
+ sql("WITH RECURSIVE t(\"Key\", depth) AS (VALUES (1, 0)\n"
+ + "UNION ALL SELECT \"Key\", depth + 1 FROM t)\n"
+ + "CYCLE \"Key\", depth SET \"Cycle\" USING \"Path\" SELECT * FROM t")
+ .ok("WITH RECURSIVE `T` (`Key`, `DEPTH`) AS ((VALUES (ROW(1, 0)))\n"
+ + "UNION ALL\n"
+ + "SELECT `Key`, (`DEPTH` + 1)\n"
+ + "FROM `T`) CYCLE `Key`, `DEPTH` SET `Cycle` TO TRUE DEFAULT
FALSE"
+ + " USING `Path` SELECT *\n"
+ + "FROM `T`");
+ }
+
+ @Test void testWithCycleFollowingWithItem() {
+ sql("WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n FROM t)\n"
+ + "CYCLE n SET c USING p, u AS (SELECT * FROM t) SELECT * FROM u")
+ .ok("WITH RECURSIVE `T` (`N`) AS ((VALUES (ROW(1)))\n"
+ + "UNION ALL\n"
+ + "SELECT `N`\n"
+ + "FROM `T`) CYCLE `N` SET `C` TO TRUE DEFAULT FALSE USING `P`,"
+ + " `U` AS (SELECT *\n"
+ + "FROM `T`) SELECT *\n"
+ + "FROM `U`");
+ }
+
+ @Test void testWithCycleFails() {
+ String wasExpectedIdentifierClause = "Was expecting.*<IDENTIFIER>.*";
+
+ final String prefix = "WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT
n FROM t)\n";
+ // A non-empty list of column names is required.
+ sql(prefix + "CYCLE ^SET^ c USING p SELECT * FROM t")
+ .fails("(?s)Incorrect syntax near the keyword 'SET' at .*"
+ + wasExpectedIdentifierClause);
+ sql(prefix + "CYCLE n, ^SET^ c USING p SELECT * FROM t")
+ .fails("(?s)Incorrect syntax near the keyword 'SET' at .*"
+ + wasExpectedIdentifierClause);
+ sql(prefix + "CYCLE n ^+^ 1 SET c USING p SELECT * FROM t")
+ .fails("(?s)Encountered \"\\+\" at .*Was expecting.*\"SET\".*");
+ sql(prefix + "CYCLE n ^USING^ p SELECT * FROM t")
+ .fails("(?s)Encountered \"USING\" at .*Was expecting.*\"SET\".*");
+ sql(prefix + "CYCLE n SET ^USING^ p SELECT * FROM t")
+ .fails("(?s)Incorrect syntax near the keyword 'USING' at .*"
+ + wasExpectedIdentifierClause);
+ // TO and DEFAULT must either both be present or both be omitted.
+ sql(prefix + "CYCLE n SET c TO TRUE ^USING^ p SELECT * FROM t")
+ .fails("(?s)Encountered \"USING\" at .*Was expecting.*\"DEFAULT\".*");
+ sql(prefix + "CYCLE n SET c ^DEFAULT^ FALSE USING p SELECT * FROM t")
+ .fails("(?s)Encountered \"DEFAULT\" at .*Was expecting.*\"USING\".*");
+ sql(prefix + "CYCLE n SET c TO TRUE DEFAULT FALSE ^SELECT^ * FROM t")
+ .fails("(?s)Encountered \"SELECT\" at .*Was expecting.*\"USING\".*");
+ sql(prefix + "CYCLE n SET c USING ^SELECT^ * FROM t")
+ .fails("(?s)Incorrect syntax near the keyword 'SELECT' at .*"
+ + wasExpectedIdentifierClause);
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7784";>[CALCITE-7784]
* Parse fails for ROW(CASE ...)</a>. */
@Test void testRowCase() {
