You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by zh...@apache.org on 2026/07/28 02:31:15 UTC
(calcite) branch revert-5004-calcite-7592 created (now 58839b2d66)
This is an automated email from the ASF dual-hosted git repository.
xiedeyantu pushed a change to branch revert-5004-calcite-7592
in repository https://gitbox.apache.org/repos/asf/calcite.git
at 58839b2d66 Revert "[CALCITE-7592] Add expression support for FETCH"
This branch includes the following new commits:
new 58839b2d66 Revert "[CALCITE-7592] Add expression support for FETCH"
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
(calcite) 01/01: Revert "[CALCITE-7592] Add expression support for FETCH"
Posted by zh...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
xiedeyantu pushed a commit to branch revert-5004-calcite-7592
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 58839b2d664672be3c792c9c124561ec0ae42c7d
Author: Zhen Chen <cz...@163.com>
AuthorDate: Tue Jul 28 10:31:06 2026 +0800
Revert "[CALCITE-7592] Add expression support for FETCH"
This reverts commit 711d46417d31b3370947dc63f0c793e0973673c9.
---
core/src/main/codegen/templates/Parser.jj | 20 +-
.../calcite/adapter/enumerable/EnumUtils.java | 7 +-
.../adapter/enumerable/EnumerableLimit.java | 20 +-
.../adapter/enumerable/EnumerableLimitSort.java | 6 +-
.../enumerable/EnumerableMergeUnionRule.java | 9 +-
.../adapter/enumerable/RexToLixTranslator.java | 16 +
.../org/apache/calcite/interpreter/SortNode.java | 102 ++-----
.../calcite/rel/metadata/RelMdMaxRowCount.java | 15 +-
.../calcite/rel/metadata/RelMdMinRowCount.java | 17 +-
.../apache/calcite/rel/metadata/RelMdRowCount.java | 12 +-
.../org/apache/calcite/rel/metadata/RelMdUtil.java | 15 +-
.../calcite/rel/rel2sql/RelToSqlConverter.java | 12 +-
.../org/apache/calcite/rel/rules/MeasureRules.java | 4 +-
.../apache/calcite/rel/rules/PruneEmptyRules.java | 6 +-
.../calcite/rel/rules/SortJoinTransposeRule.java | 4 +-
.../calcite/rel/rules/SortRemoveRedundantRule.java | 3 -
.../calcite/rel/rules/SortUnionTransposeRule.java | 9 +-
.../main/java/org/apache/calcite/rex/RexUtil.java | 80 -----
.../apache/calcite/runtime/CalciteResource.java | 9 -
.../java/org/apache/calcite/sql/SqlDialect.java | 34 +--
.../calcite/sql/dialect/SqliteSqlDialect.java | 2 +-
.../apache/calcite/sql/fun/SqlCastFunction.java | 2 +-
.../calcite/sql/type/SqlTypeFactoryImpl.java | 31 +-
.../org/apache/calcite/sql/type/SqlTypeUtil.java | 15 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 29 --
.../calcite/sql2rel/CorrelateProjectExtractor.java | 77 ++++-
.../apache/calcite/sql2rel/RelDecorrelator.java | 29 +-
.../sql2rel/TopDownGeneralDecorrelator.java | 35 +--
.../java/org/apache/calcite/tools/RelBuilder.java | 52 +---
.../calcite/runtime/CalciteResource.properties | 3 -
.../calcite/adapter/enumerable/EnumUtilsTest.java | 11 -
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 82 +----
.../org/apache/calcite/rex/RexProgramTest.java | 30 --
.../calcite/sql/type/SqlTypeFactoryTest.java | 36 +++
.../sql2rel/CorrelateProjectExtractorTest.java | 72 +++++
.../calcite/sql2rel/RelDecorrelatorTest.java | 99 ++++--
.../java/org/apache/calcite/test/JdbcTest.java | 338 ---------------------
.../org/apache/calcite/test/RelBuilderTest.java | 105 -------
.../org/apache/calcite/test/RelMetadataTest.java | 33 +-
.../org/apache/calcite/test/RelOptRulesTest.java | 81 +----
.../apache/calcite/test/SqlToRelConverterTest.java | 9 -
.../org/apache/calcite/test/SqlValidatorTest.java | 26 +-
.../test/enumerable/EnumerableMergeUnionTest.java | 30 --
.../org/apache/calcite/test/RelOptRulesTest.xml | 217 ++-----------
.../apache/calcite/test/SqlToRelConverterTest.xml | 52 ++--
core/src/test/resources/sql/fetch.iq | 183 -----------
core/src/test/resources/sql/lateral.iq | 94 ++++++
.../java/org/apache/calcite/test/ServerTest.java | 37 ---
site/_docs/reference.md | 9 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 19 --
.../org/apache/calcite/test/SqlOperatorTest.java | 95 +++++-
51 files changed, 629 insertions(+), 1704 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index ce69124c4b..7246e60842 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -709,7 +709,7 @@ SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext) :
*
* <blockquote><pre>
* [ OFFSET start { ROW | ROWS } ]
- * [ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY ]</pre>
+ * [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]</pre>
* </blockquote>
*/
SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
@@ -796,26 +796,10 @@ void FetchClause(SqlNode[] offsetFetch) :
{
// SQL:2008-style syntax. "OFFSET ... FETCH ...".
// If you specify both LIMIT and FETCH, FETCH wins.
- <FETCH> ( <FIRST> | <NEXT> ) offsetFetch[1] = FetchCount()
+ <FETCH> ( <FIRST> | <NEXT> ) offsetFetch[1] = UnsignedNumericLiteralOrParam()
( <ROW> | <ROWS> ) <ONLY>
}
-/**
- * Parses the row count of a FETCH clause. Expressions must be parenthesized.
- */
-SqlNode FetchCount() :
-{
- final SqlNode e;
-}
-{
- (
- e = UnsignedNumericLiteralOrParam()
- |
- <LPAREN> e = Expression(ExprContext.ACCEPT_NON_QUERY) <RPAREN>
- )
- { return e; }
-}
-
/**
* Parses a LIMIT clause in an ORDER BY expression.
*/
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
index 71dc7e6026..80c46ec3f9 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumUtils.java
@@ -116,7 +116,7 @@ private EnumUtils() {}
/** Converts a FETCH or OFFSET runtime value to {@link BigDecimal}.
*
* <p>The value must be numeric and non-negative. */
- public static BigDecimal numberToBigDecimal(@Nullable Object value, String kind) {
+ public static BigDecimal numberToBigDecimal(Object value, String kind) {
return numberToBigDecimal(value, kind, FetchOffsetRoundingPolicy.NONE);
}
@@ -124,11 +124,8 @@ public static BigDecimal numberToBigDecimal(@Nullable Object value, String kind)
*
* <p>The value must be numeric and non-negative. The result is adjusted by
* the configured rounding policy. */
- public static BigDecimal numberToBigDecimal(@Nullable Object value, String kind,
+ public static BigDecimal numberToBigDecimal(Object value, String kind,
FetchOffsetRoundingPolicy roundingPolicy) {
- if (value == null) {
- throw new IllegalArgumentException(kind + " expression evaluated to NULL");
- }
if (!(value instanceof Number)) {
throw new IllegalArgumentException(kind + " must be a number");
}
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
index de1f94d562..02fd54bdad 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
@@ -106,15 +106,13 @@ public static EnumerableLimit create(final RelNode input, @Nullable RexNode offs
v =
builder.append("offset",
Expressions.call(BuiltInMethod.SKIP_BIG_DECIMAL.method, v,
- getExpression(offset, "OFFSET", implementor, builder,
- roundingPolicyExp, false)));
+ getExpression(offset, "OFFSET", roundingPolicyExp)));
}
if (fetch != null) {
v =
builder.append("fetch",
Expressions.call(BuiltInMethod.TAKE_BIG_DECIMAL.method, v,
- getExpression(fetch, "FETCH", implementor, builder,
- roundingPolicyExp, true)));
+ getExpression(fetch, "FETCH", roundingPolicyExp)));
}
builder.add(Expressions.return_(null, v));
@@ -122,8 +120,7 @@ public static EnumerableLimit create(final RelNode input, @Nullable RexNode offs
}
static Expression getExpression(RexNode rexNode, String kind,
- EnumerableRelImplementor implementor, BlockBuilder builder,
- Expression roundingPolicy, boolean translateExpression) {
+ Expression roundingPolicy) {
final Expression value;
if (rexNode instanceof RexDynamicParam) {
final RexDynamicParam param = (RexDynamicParam) rexNode;
@@ -131,17 +128,8 @@ static Expression getExpression(RexNode rexNode, String kind,
Expressions.call(DataContext.ROOT,
BuiltInMethod.DATA_CONTEXT_GET.method,
Expressions.constant("?" + param.getIndex()));
- } else if (rexNode instanceof RexLiteral) {
- value = Expressions.constant(RexLiteral.bigDecimalValue(rexNode));
} else {
- if (!translateExpression) {
- throw new IllegalArgumentException(kind + " must be a literal or dynamic parameter");
- }
-
- value =
- RexToLixTranslator.forAggregation(implementor.getTypeFactory(),
- builder, null, implementor.getConformance())
- .translate(rexNode);
+ value = Expressions.constant(RexLiteral.bigDecimalValue(rexNode));
}
return Expressions.call(
BuiltInMethod.NUMBER_TO_BIG_DECIMAL_LIMIT.method,
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java
index 97d9fd8169..325fe687ba 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java
@@ -104,16 +104,14 @@ public static EnumerableLimitSort create(
if (this.fetch == null) {
fetchVal = Expressions.constant(BigDecimal.valueOf(Integer.MAX_VALUE));
} else {
- fetchVal =
- getExpression(this.fetch, "FETCH", implementor, builder, roundingPolicyExp, true);
+ fetchVal = getExpression(this.fetch, "FETCH", roundingPolicyExp);
}
final Expression offsetVal;
if (this.offset == null) {
offsetVal = Expressions.constant(BigDecimal.ZERO);
} else {
- offsetVal =
- getExpression(this.offset, "OFFSET", implementor, builder, roundingPolicyExp, false);
+ offsetVal = getExpression(this.offset, "OFFSET", roundingPolicyExp);
}
builder.add(
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java
index 57f864794a..7d47e639b7 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java
@@ -29,7 +29,6 @@
import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
-import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.util.ImmutableBitSet;
@@ -89,13 +88,9 @@ public EnumerableMergeUnionRule(Config config) {
// Push down sort limit, if possible.
RexNode inputFetch = null;
if (sort.fetch != null) {
- final boolean safeToReevaluate =
- RexUtil.isDeterministic(sort.fetch);
- if (sort.offset == null && safeToReevaluate) {
+ if (sort.offset == null) {
inputFetch = sort.fetch;
- } else if (safeToReevaluate
- && sort.fetch instanceof RexLiteral
- && sort.offset instanceof RexLiteral) {
+ } else if (sort.fetch instanceof RexLiteral && sort.offset instanceof RexLiteral) {
inputFetch =
call.builder().literal(RexLiteral.bigDecimalValue(sort.fetch)
.add(RexLiteral.bigDecimalValue(sort.offset)));
diff --git a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
index 5a1e0fee20..a39146ac75 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexToLixTranslator.java
@@ -341,6 +341,16 @@ Expression translateCast(
return expressionHandlingSafe(convert3, safe, targetType);
}
+ /** Returns whether every runtime value of {@code type} is null.
+ * This holds for the NULL type, which describes untyped NULL literals,
+ * and for the UNKNOWN type, e.g. the element type inferred for the
+ * no-argument array constructor ARRAY(). Such values are never
+ * created, but the code generated needs to typecheck. */
+ private static boolean valueIsAlwaysNull(RelDataType type) {
+ SqlTypeName typeName = type.getSqlTypeName();
+ return typeName == SqlTypeName.UNKNOWN || typeName == SqlTypeName.NULL;
+ }
+
private Expression getConvertExpression(
RelDataType sourceType,
RelDataType targetType,
@@ -366,6 +376,9 @@ private Expression getConvertExpression(
}
if (targetType.getSqlTypeName() == SqlTypeName.ROW) {
+ if (valueIsAlwaysNull(sourceType)) {
+ return Expressions.constant(null);
+ }
assert sourceType.getSqlTypeName() == SqlTypeName.ROW;
List<RelDataTypeField> targetTypes = targetType.getFieldList();
List<RelDataTypeField> sourceTypes = sourceType.getFieldList();
@@ -397,6 +410,9 @@ private Expression getConvertExpression(
switch (targetType.getSqlTypeName()) {
case ARRAY:
case MULTISET:
+ if (valueIsAlwaysNull(sourceType)) {
+ return Expressions.constant(null);
+ }
final RelDataType sourceDataType = sourceType.getComponentType();
final RelDataType targetDataType = targetType.getComponentType();
assert sourceDataType != null;
diff --git a/core/src/main/java/org/apache/calcite/interpreter/SortNode.java b/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
index 0f393a3e68..71d9f2b22e 100644
--- a/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
+++ b/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
@@ -16,22 +16,14 @@
*/
package org.apache.calcite.interpreter;
-import org.apache.calcite.adapter.enumerable.EnumUtils;
-import org.apache.calcite.adapter.enumerable.EnumerableRelImplementor;
-import org.apache.calcite.adapter.enumerable.FetchOffsetRoundingPolicy;
import org.apache.calcite.rel.RelFieldCollation;
import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.Util;
-import com.google.common.collect.ImmutableList;
import com.google.common.collect.Ordering;
-import org.checkerframework.checker.nullness.qual.Nullable;
-
-import java.math.BigDecimal;
-import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
@@ -43,57 +35,37 @@
* {@link org.apache.calcite.rel.core.Sort}.
*/
public class SortNode extends AbstractSingleNode<Sort> {
- private final @Nullable Scalar offsetScalar;
- private final @Nullable Context offsetContext;
- private final @Nullable Scalar fetchScalar;
- private final @Nullable Context fetchContext;
- private final FetchOffsetRoundingPolicy fetchOffsetRoundingPolicy;
-
public SortNode(Compiler compiler, Sort rel) {
super(compiler, rel);
- if (rel.offset != null && !(rel.offset instanceof RexLiteral)) {
- this.offsetScalar = compiler.compile(ImmutableList.of(rel.offset), null);
- this.offsetContext = compiler.createContext();
- } else {
- this.offsetScalar = null;
- this.offsetContext = null;
- }
- if (rel.fetch != null && !(rel.fetch instanceof RexLiteral)) {
- this.fetchScalar = compiler.compile(ImmutableList.of(rel.fetch), null);
- this.fetchContext = compiler.createContext();
- } else {
- this.fetchScalar = null;
- this.fetchContext = null;
- }
- final Object roundingPolicy = compiler.getDataContext()
- .get(EnumerableRelImplementor.FETCH_OFFSET_ROUNDING_POLICY);
- this.fetchOffsetRoundingPolicy =
- roundingPolicy instanceof FetchOffsetRoundingPolicy
- ? (FetchOffsetRoundingPolicy) roundingPolicy
- : FetchOffsetRoundingPolicy.NONE;
+ }
+
+ private static int getValueAsInt(RexNode node) {
+ return requireNonNull(((RexLiteral) node).getValueAs(Integer.class),
+ () -> "getValueAs(Integer.class) for " + node);
}
@Override public void run() throws InterruptedException {
- final BigDecimal offset = getOffset();
- final @Nullable BigDecimal fetch = getFetch();
+ final int offset =
+ rel.offset == null
+ ? 0
+ : getValueAsInt(rel.offset);
+ final int fetch =
+ rel.fetch == null
+ ? -1
+ : getValueAsInt(rel.fetch);
// In pure limit mode. No sort required.
Row row;
loop:
if (rel.getCollation().getFieldCollations().isEmpty()) {
- BigDecimal skipped = BigDecimal.ZERO;
- while (skipped.compareTo(offset) < 0) {
+ for (int i = 0; i < offset; i++) {
row = source.receive();
if (row == null) {
break loop;
}
- skipped = skipped.add(BigDecimal.ONE);
}
- if (fetch != null) {
- BigDecimal fetched = BigDecimal.ZERO;
- while (fetched.compareTo(fetch) < 0
- && (row = source.receive()) != null) {
+ if (fetch >= 0) {
+ for (int i = 0; i < fetch && (row = source.receive()) != null; i++) {
sink.send(row);
- fetched = fetched.add(BigDecimal.ONE);
}
} else {
while ((row = source.receive()) != null) {
@@ -107,15 +79,10 @@ public SortNode(Compiler compiler, Sort rel) {
list.add(row);
}
list.sort(comparator());
- final int start = offset.compareTo(BigDecimal.valueOf(list.size())) >= 0
- ? list.size()
- : rowCount(offset);
- final int available = list.size() - start;
- final int end = fetch == null
- || fetch.compareTo(BigDecimal.valueOf(available)) >= 0
+ final int end = fetch < 0 || offset + fetch > list.size()
? list.size()
- : start + rowCount(fetch);
- for (int i = start; i < end; i++) {
+ : offset + fetch;
+ for (int i = offset; i < end; i++) {
sink.send(list.get(i));
}
}
@@ -149,35 +116,4 @@ private static Comparator<Row> comparator(RelFieldCollation fieldCollation) {
};
}
}
-
- private @Nullable BigDecimal getFetch() {
- if (rel.fetch == null) {
- return null;
- }
- return getValue(rel.fetch, fetchScalar, fetchContext, "FETCH");
- }
-
- private BigDecimal getOffset() {
- if (rel.offset == null) {
- return BigDecimal.ZERO;
- }
- return getValue(rel.offset, offsetScalar, offsetContext, "OFFSET");
- }
-
- private BigDecimal getValue(RexNode node, @Nullable Scalar scalar,
- @Nullable Context context, String kind) {
- final @Nullable Object value;
- if (node instanceof RexLiteral) {
- value = RexLiteral.bigDecimalValue(node);
- } else {
- value =
- requireNonNull(scalar, () -> kind + " scalar")
- .execute(requireNonNull(context, () -> kind + " context"));
- }
- return EnumUtils.numberToBigDecimal(value, kind, fetchOffsetRoundingPolicy);
- }
-
- private static int rowCount(BigDecimal value) {
- return value.setScale(0, RoundingMode.CEILING).intValueExact();
- }
}
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java
index 869f1ad50e..e728c22e1e 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMaxRowCount.java
@@ -117,12 +117,10 @@ public Double getMaxRowCount(Sort rel, RelMetadataQuery mq) {
rowCount = Double.POSITIVE_INFINITY;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch, rowCount);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
@@ -132,12 +130,10 @@ public Double getMaxRowCount(EnumerableLimit rel, RelMetadataQuery mq) {
rowCount = Double.POSITIVE_INFINITY;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch, rowCount);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
@@ -218,8 +214,7 @@ public Double getMaxRowCount(RelSubset rel, RelMetadataQuery mq) {
if (node instanceof Sort) {
Sort sort = (Sort) node;
if (sort.fetch instanceof RexLiteral) {
- return literalValueApproximatedByDouble(sort.fetch,
- Double.POSITIVE_INFINITY);
+ return literalValueApproximatedByDouble(sort.fetch, Double.POSITIVE_INFINITY);
}
}
}
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java
index 2cb710f398..869d343335 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java
@@ -116,13 +116,10 @@ public Double getMinRowCount(Sort rel, RelMetadataQuery mq) {
rowCount = 0D;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch,
- rel.fetch == null ? rowCount : 0D);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
@@ -132,13 +129,10 @@ public Double getMinRowCount(EnumerableLimit rel, RelMetadataQuery mq) {
rowCount = 0D;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch,
- rel.fetch == null ? rowCount : 0D);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
@@ -180,8 +174,7 @@ public Double getMinRowCount(RelSubset rel, RelMetadataQuery mq) {
if (node instanceof Sort) {
Sort sort = (Sort) node;
if (sort.fetch instanceof RexLiteral) {
- return literalValueApproximatedByDouble(sort.fetch,
- Double.POSITIVE_INFINITY);
+ return literalValueApproximatedByDouble(sort.fetch, Double.POSITIVE_INFINITY);
}
}
}
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdRowCount.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdRowCount.java
index 3e7824e1aa..e83f4c1da9 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdRowCount.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdRowCount.java
@@ -165,12 +165,10 @@ public Double getRowCount(Calc rel, RelMetadataQuery mq) {
return null;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch, rowCount);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
@@ -180,12 +178,10 @@ public Double getRowCount(Calc rel, RelMetadataQuery mq) {
return null;
}
- final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ final double offset = literalValueApproximatedByDouble(rel.offset, 0D);
rowCount = Math.max(rowCount - offset, 0D);
- final double limit =
- literalValueApproximatedByDouble(rel.fetch, rowCount);
+ final double limit = literalValueApproximatedByDouble(rel.fetch, rowCount);
return limit < rowCount ? limit : rowCount;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java
index 5b09628938..1f65022436 100644
--- a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java
+++ b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java
@@ -25,7 +25,6 @@
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Minus;
import org.apache.calcite.rel.core.Project;
-import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
@@ -57,7 +56,6 @@
import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
-import java.util.Objects;
import java.util.Set;
import static com.google.common.base.Preconditions.checkArgument;
@@ -485,9 +483,6 @@ public static double literalValueApproximatedByDouble(@Nullable RexNode node,
throw new IllegalArgumentException(
"literal value " + number + " cannot be converted to BigDecimal");
}
- if (decimal.signum() < 0) {
- return defaultValue;
- }
if (decimal.abs().compareTo(BigDecimal.valueOf(Double.MAX_VALUE)) > 0) {
throw new IllegalArgumentException(
"literal value " + decimal + " exceeds double range");
@@ -1048,16 +1043,8 @@ private static boolean alreadySmaller(RelMetadataQuery mq, RelNode input,
if (fetch == null) {
return true;
}
- final RelNode strippedInput = input.stripped();
- if (strippedInput instanceof Sort) {
- final Sort sort = (Sort) strippedInput;
- if (Objects.equals(offset, sort.offset)
- && Objects.equals(fetch, sort.fetch)) {
- return true;
- }
- }
final Double rowCount = mq.getMaxRowCount(input);
- if (rowCount == null || offset instanceof RexDynamicParam || !(fetch instanceof RexLiteral)) {
+ if (rowCount == null || offset instanceof RexDynamicParam || fetch instanceof RexDynamicParam) {
// Cannot be determined
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
index 718e6b1cb9..8871f24d3e 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/RelToSqlConverter.java
@@ -59,7 +59,6 @@
import org.apache.calcite.rex.RexLocalRef;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexProgram;
-import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.sql.JoinConditionType;
import org.apache.calcite.sql.JoinType;
import org.apache.calcite.sql.SqlAsofJoin;
@@ -1228,7 +1227,7 @@ public Result visit(Sort e) {
sqlSelect.setOffset(offset);
}
if (e.fetch != null) {
- SqlNode fetch = toSqlFetch(e, builder.context);
+ SqlNode fetch = builder.context.toSql(null, e.fetch);
sqlSelect.setFetch(fetch);
}
return result(sqlSelect, ImmutableList.of(Clause.ORDER_BY), e, null);
@@ -1286,20 +1285,13 @@ public Result visit(Sort e) {
* The builder must have been created with OFFSET and FETCH clauses. */
void offsetFetch(Sort e, Builder builder) {
if (e.fetch != null) {
- builder.setFetch(toSqlFetch(e, builder.context));
+ builder.setFetch(builder.context.toSql(null, e.fetch));
}
if (e.offset != null) {
builder.setOffset(builder.context.toSql(null, e.offset));
}
}
- private static SqlNode toSqlFetch(Sort sort, Context context) {
- final RexNode fetch = requireNonNull(sort.fetch, "fetch");
- final @Nullable RexLiteral reduced =
- RexUtil.reduceFetchToLiteral(sort.getCluster(), fetch);
- return context.toSql(null, reduced == null ? fetch : reduced);
- }
-
public boolean hasTrickyRollup(Sort e, Aggregate aggregate) {
return !dialect.supportsAggregateFunction(SqlKind.ROLLUP)
&& dialect.supportsGroupByWithRollup()
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java b/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
index f69810a14d..037a4d6054 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/MeasureRules.java
@@ -30,6 +30,7 @@
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexCorrelVariable;
import org.apache.calcite.rex.RexInputRef;
+import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.rex.RexShuttle;
import org.apache.calcite.rex.RexUtil;
@@ -507,7 +508,8 @@ protected ProjectSortMeasureRule(ProjectSortMeasureRuleConfig config) {
relBuilder.push(sort.getInput())
.projectPlus(map.keySet())
- .sortLimit(sort.offset, sort.fetch,
+ .sortLimit(sort.offset == null ? 0 : RexLiteral.numberValue(sort.offset),
+ sort.fetch == null ? -1 : RexLiteral.numberValue(sort.fetch),
sort.getSortExps())
.project(newProjects);
call.transformTo(relBuilder.build());
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
index 5cb60bc906..5d70c5e0de 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
@@ -41,6 +41,7 @@
import org.apache.calcite.rel.logical.LogicalValues;
import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.type.RelDataType;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.tools.RelBuilder;
@@ -518,8 +519,9 @@ public interface SortFetchZeroRuleConfig extends PruneEmptyRule.Config {
return new RemoveEmptySingleRule(this) {
@Override public boolean matches(final RelOptRuleCall call) {
Sort sort = call.rel(0);
- return sort.fetch instanceof RexLiteral
- && BigDecimal.ZERO.equals(RexLiteral.bigDecimalValue(sort.fetch));
+ return sort.fetch != null
+ && !(sort.fetch instanceof RexDynamicParam)
+ && RexLiteral.bigDecimalValue(sort.fetch).equals(BigDecimal.ZERO);
}
};
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
index df967e56aa..4310d6d655 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
@@ -105,9 +105,9 @@ public SortJoinTransposeRule(Class<? extends Sort> sortClass,
final Sort sort = call.rel(0);
final Join join = call.rel(1);
- // The pushed fetch is calculated from literal offset and fetch values.
+ // Do nothing if SORT contains dynamic parameters in offset or fetch
if (sort.offset instanceof RexDynamicParam
- || sort.fetch != null && !(sort.fetch instanceof RexLiteral)) {
+ || sort.fetch instanceof RexDynamicParam) {
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
index 08563cdcdb..9bcf026fc6 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SortRemoveRedundantRule.java
@@ -133,9 +133,6 @@ protected SortRemoveRedundantRule(final SortRemoveRedundantRule.Config config) {
private static Optional<BigDecimal> getRowCountThreshold(Sort sort) {
if (RelOptUtil.isLimit(sort)) {
assert sort.fetch != null;
- if (!(sort.fetch instanceof RexLiteral)) {
- return Optional.empty();
- }
final BigDecimal fetch = RexLiteral.bigDecimalValue(sort.fetch);
// We don't need to deal with fetch is 0.
diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java
index 93b6af657c..416825ee92 100644
--- a/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java
+++ b/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java
@@ -23,7 +23,7 @@
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
-import org.apache.calcite.rex.RexUtil;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.tools.RelBuilderFactory;
import org.immutables.value.Value;
@@ -67,14 +67,13 @@ public SortUnionTransposeRule(
@Override public boolean matches(RelOptRuleCall call) {
final Sort sort = call.rel(0);
final Union union = call.rel(1);
- // Re-evaluating a non-deterministic FETCH in every branch can produce a
- // different limit from the top Sort.
+ // We only apply this rule if Union.all is true, Sort.offset is null and Sort.fetch is not
+ // a dynamic param.
// There is a flag indicating if this rule should be applied when
// Sort.fetch is null.
return union.all
&& sort.offset == null
- && (sort.fetch == null
- || RexUtil.isDeterministic(sort.fetch))
+ && !(sort.fetch instanceof RexDynamicParam)
&& (config.matchNullFetch() || sort.fetch != null);
}
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index b592093a5a..3604e98dfd 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -19,7 +19,6 @@
import org.apache.calcite.DataContexts;
import org.apache.calcite.linq4j.function.Predicate1;
import org.apache.calcite.plan.PlanTooComplexError;
-import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptPredicateList;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.rel.RelCollation;
@@ -49,7 +48,6 @@
import org.apache.calcite.util.ControlFlowException;
import org.apache.calcite.util.ImmutableBitSet;
import org.apache.calcite.util.Litmus;
-import org.apache.calcite.util.NumberUtil;
import org.apache.calcite.util.Pair;
import org.apache.calcite.util.RangeSets;
import org.apache.calcite.util.Sarg;
@@ -65,11 +63,9 @@
import org.apiguardian.api.API;
import org.checkerframework.checker.nullness.qual.Nullable;
-import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
-import java.util.Collections;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
@@ -844,82 +840,6 @@ public static boolean isDeterministic(RexNode e) {
}
}
- /** Returns whether an expression contains a dynamic function. */
- public static boolean containsDynamicFunction(RexNode e) {
- try {
- e.accept(
- new RexVisitorImpl<Void>(true) {
- @Override public Void visitCall(RexCall call) {
- if (call.getOperator().isDynamicFunction()) {
- throw Util.FoundOne.NULL;
- }
- return super.visitCall(call);
- }
- });
- return false;
- } catch (Util.FoundOne ex) {
- Util.swallow(ex, null);
- return true;
- }
- }
-
- /** Returns whether an expression contains a dynamic parameter. */
- public static boolean containsDynamicParam(RexNode e) {
- try {
- e.accept(
- new RexVisitorImpl<Void>(true) {
- @Override public Void visitDynamicParam(RexDynamicParam dynamicParam) {
- throw Util.FoundOne.NULL;
- }
- });
- return false;
- } catch (Util.FoundOne ex) {
- Util.swallow(ex, null);
- return true;
- }
- }
-
- /** Converts a FETCH expression result to its validated canonical representation. */
- public static BigDecimal validateFetchValue(@Nullable Number value) {
- if (value == null) {
- throw new IllegalArgumentException("FETCH expression evaluated to NULL");
- }
- final BigDecimal decimal = NumberUtil.toBigDecimal(value);
- if (decimal.signum() < 0) {
- throw new IllegalArgumentException("FETCH value " + value
- + " is out of range; expected a non-negative value");
- }
- return decimal;
- }
-
- /** Reduces a constant FETCH expression to a validated literal. */
- public static @Nullable RexLiteral reduceFetchToLiteral(
- RelOptCluster cluster, RexNode fetch) {
- final RexLiteral literal;
- if (fetch instanceof RexLiteral) {
- literal = (RexLiteral) fetch;
- } else {
- if (!isConstant(fetch)
- || !isDeterministic(fetch)
- || containsDynamicFunction(fetch)
- || containsDynamicParam(fetch)) {
- return null;
- }
- final RexExecutor executor =
- Util.first(cluster.getPlanner().getExecutor(), EXECUTOR);
- final List<RexNode> reducedValues = new ArrayList<>(1);
- executor.reduce(cluster.getRexBuilder(),
- Collections.singletonList(fetch), reducedValues);
- final RexNode reduced = reducedValues.get(0);
- if (!(reduced instanceof RexLiteral)) {
- return null;
- }
- literal = (RexLiteral) reduced;
- }
- validateFetchValue(literal.getValueAs(Number.class));
- return literal;
- }
-
public static List<RexNode> retainDeterministic(List<RexNode> list) {
List<RexNode> conjunctions = new ArrayList<>();
for (RexNode x : list) {
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 e5932b6376..c6e1a4dbdc 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -164,15 +164,6 @@ ExInstWithCause<CalciteContextException> validatorContext(int a0, int a1,
@BaseMessage("Values passed to {0} operator must have compatible types")
ExInst<SqlValidatorException> incompatibleValueType(String a0);
- @BaseMessage("FETCH expression must have a numeric type; actual type is ''{0}''")
- ExInst<SqlValidatorException> fetchExpressionMustBeNumeric(String type);
-
- @BaseMessage("FETCH expression cannot reference table column ''{0}''")
- ExInst<SqlValidatorException> fetchExpressionCannotReferenceColumn(String column);
-
- @BaseMessage("FETCH expression evaluated to NULL")
- ExInst<SqlValidatorException> fetchExpressionEvaluatedToNull();
-
@BaseMessage("Values in expression list must have compatible types")
ExInst<SqlValidatorException> incompatibleTypesInList();
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
index 663aee63b0..869976f0d4 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1081,18 +1081,7 @@ protected static void unparseFetchUsingAnsi(SqlWriter writer, @Nullable SqlNode
writer.startList(SqlWriter.FrameTypeEnum.FETCH);
writer.keyword("FETCH");
writer.keyword("NEXT");
- if (fetch instanceof SqlLiteral
- || fetch instanceof SqlDynamicParam) {
- fetch.unparse(writer, -1, -1);
- } else {
- final SqlWriter.Frame expressionFrame = writer.startList("(", ")");
- if (fetch instanceof SqlCall) {
- writer.getDialect().unparseCall(writer, (SqlCall) fetch, 0, 0);
- } else {
- fetch.unparse(writer, 0, 0);
- }
- writer.endList(expressionFrame);
- }
+ fetch.unparse(writer, -1, -1);
writer.keyword("ROWS");
writer.keyword("ONLY");
writer.endList(fetchFrame);
@@ -1102,32 +1091,13 @@ protected static void unparseFetchUsingAnsi(SqlWriter writer, @Nullable SqlNode
/** Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax. */
protected static void unparseFetchUsingLimit(SqlWriter writer, @Nullable SqlNode offset,
@Nullable SqlNode fetch) {
- unparseFetchUsingLimit(writer, offset, fetch, false);
- }
-
- /** Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax,
- * optionally allowing a scalar expression as fetch. */
- protected static void unparseFetchUsingLimit(SqlWriter writer, @Nullable SqlNode offset,
- @Nullable SqlNode fetch, boolean allowExpression) {
checkArgument(fetch != null || offset != null);
- unparseLimit(writer, fetch, allowExpression);
+ unparseLimit(writer, fetch);
unparseOffset(writer, offset);
}
protected static void unparseLimit(SqlWriter writer, @Nullable SqlNode fetch) {
- unparseLimit(writer, fetch, false);
- }
-
- private static void unparseLimit(SqlWriter writer, @Nullable SqlNode fetch,
- boolean allowExpression) {
if (fetch != null) {
- if (!allowExpression
- && !(fetch instanceof SqlLiteral)
- && !(fetch instanceof SqlDynamicParam)) {
- throw new IllegalArgumentException(
- "LIMIT dialect does not support FETCH expressions that cannot "
- + "be reduced to a literal");
- }
writer.newlineAndIndent();
final SqlWriter.Frame fetchFrame =
writer.startList(SqlWriter.FrameTypeEnum.FETCH);
diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
index f312764136..82376ae576 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
@@ -90,7 +90,7 @@ public SqliteSqlDialect(SqlDialect.Context context) {
@Override public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode offset,
@Nullable SqlNode fetch) {
- unparseFetchUsingLimit(writer, offset, fetch, true);
+ unparseFetchUsingLimit(writer, offset, fetch);
}
@Override public void unparseCall(SqlWriter writer, SqlCall call,
diff --git a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
index b25a9b8fdd..62ce488718 100644
--- a/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/fun/SqlCastFunction.java
@@ -200,7 +200,7 @@ private static RelDataType createTypeWithNullabilityFromExpr(RelDataTypeFactory
RelDataType valueType =
createTypeWithNullabilityFromExpr(
typeFactory, expressionValueType, targetValueType, safe);
- SqlTypeUtil.createMapType(typeFactory, keyType, valueType, isNullable);
+ return SqlTypeUtil.createMapType(typeFactory, keyType, valueType, isNullable);
}
return typeFactory.createTypeWithNullability(targetType, isNullable);
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
index d0ccab7dfd..544f9ca708 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeFactoryImpl.java
@@ -28,6 +28,7 @@
import org.checkerframework.checker.nullness.qual.Nullable;
import java.nio.charset.Charset;
+import java.util.ArrayList;
import java.util.List;
import static com.google.common.base.Preconditions.checkArgument;
@@ -191,11 +192,33 @@ public SqlTypeFactoryImpl(RelDataTypeSystem typeSystem) {
RelDataType type0 = types.get(0);
if (type0.getSqlTypeName() != null) {
- RelDataType resultType = leastRestrictiveSqlType(types);
- if (resultType != null) {
- return resultType;
+ // First preprocess to filter out UNKNOWN types.
+ // leastRestrictive() can be thought as a form of type unification,
+ // and UNKNOWN behaves like an unbound type variable: it unifies with any type
+ // without constraining the result.
+ // Note that UNKNOWN can be nullable, so this information is carried over to the result.
+ List<RelDataType> knownTypes = new ArrayList<>(types.size());
+ // True if any UNKNOWN type is nullable
+ boolean anyUnknownIsNullable = false;
+ for (RelDataType type : types) {
+ if (type.getSqlTypeName() == SqlTypeName.UNKNOWN) {
+ anyUnknownIsNullable |= type.isNullable();
+ } else {
+ knownTypes.add(type);
+ }
}
- return leastRestrictiveByCast(types, mappingRule);
+ if (knownTypes.isEmpty()) {
+ // All types are unknown
+ return createTypeWithNullability(createUnknownType(), anyUnknownIsNullable);
+ }
+ RelDataType resultType = leastRestrictiveSqlType(knownTypes);
+ if (resultType == null) {
+ resultType = leastRestrictiveByCast(knownTypes, mappingRule);
+ }
+ if (resultType != null && anyUnknownIsNullable) {
+ resultType = createTypeWithNullability(resultType, true);
+ }
+ return resultType;
}
return super.leastRestrictive(types, mappingRule);
diff --git a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
index 0298866828..5de00a50b4 100644
--- a/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
+++ b/core/src/main/java/org/apache/calcite/sql/type/SqlTypeUtil.java
@@ -1128,8 +1128,9 @@ public static boolean canCastFrom(
requireNonNull(fromType.getComponentType(), "componentType"),
typeMappingRule);
} else if (fromType.getFamily() == SqlTypeFamily.CHARACTER
- || fromType.getSqlTypeName() == SqlTypeName.NULL) {
- // Cast from NULL or string to array is legal
+ || fromType.getSqlTypeName() == SqlTypeName.NULL
+ || fromType.getSqlTypeName() == SqlTypeName.UNKNOWN) {
+ // Cast from NULL, UNKNOWN, or string to array is legal
return true;
}
return false;
@@ -1145,8 +1146,9 @@ && canCastFrom(
requireNonNull(fromType.getValueType(), "valueType"),
typeMappingRule);
} else if (fromType.getFamily() == SqlTypeFamily.CHARACTER
- || fromType.getSqlTypeName() == SqlTypeName.NULL) {
- // Cast from NULL or string to map is legal
+ || fromType.getSqlTypeName() == SqlTypeName.NULL
+ || fromType.getSqlTypeName() == SqlTypeName.UNKNOWN) {
+ // Cast from NULL, UNKNOWN, or string to map is legal
return true;
}
return false;
@@ -1164,7 +1166,10 @@ && canCastFrom(
toType, fromType.getFieldList().get(0).getType(), typeMappingRule);
} else if (toTypeName == SqlTypeName.ROW) {
if (fromTypeName != SqlTypeName.ROW) {
- return fromTypeName == SqlTypeName.NULL;
+ // UNKNOWN can arise e.g. as the element type inferred for the
+ // no-argument array constructor ARRAY()
+ return fromTypeName == SqlTypeName.NULL
+ || fromTypeName == SqlTypeName.UNKNOWN;
}
int n = toType.getFieldCount();
if (fromType.getFieldCount() != n) {
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 02a044820f..023de1b77f 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
@@ -1771,34 +1771,6 @@ private void handleOffsetFetch(@Nullable SqlNode offset, @Nullable SqlNode fetch
}
}
- private void validateFetchExpression(@Nullable SqlNode fetch) {
- if (fetch == null || fetch instanceof SqlDynamicParam) {
- return;
- }
- if (SqlUtil.isNullLiteral(fetch, true)) {
- throw newValidationError(fetch,
- RESOURCE.fetchExpressionEvaluatedToNull());
- }
- validateNoAggs(aggOrOverFinder, fetch, "FETCH");
- fetch.accept(new SqlBasicVisitor<Void>() {
- @Override public Void visit(SqlIdentifier id) {
- if (makeNullaryCall(id) != null) {
- return null;
- }
- throw newValidationError(id,
- RESOURCE.fetchExpressionCannotReferenceColumn(id.toString()));
- }
- });
- final SqlValidatorScope scope = getEmptyScope();
- inferUnknownTypes(typeFactory.createSqlType(SqlTypeName.DECIMAL), scope, fetch);
- validateExpr(fetch, scope);
- final RelDataType type = getValidatedNodeType(fetch);
- if (!SqlTypeUtil.isNumeric(type)) {
- throw newValidationError(fetch,
- RESOURCE.fetchExpressionMustBeNumeric(type.getFullTypeString()));
- }
- }
-
/**
* Performs expression rewrites which are always used unconditionally. These
* rewrites massage the expression tree into a standard form so that the
@@ -4497,7 +4469,6 @@ protected void validateSelect(
validateWindowClause(select);
validateQualifyClause(select);
handleOffsetFetch(select.getOffset(), select.getFetch());
- validateFetchExpression(select.getFetch());
// Validate the SELECT clause late, because a select item might
// depend on the GROUP BY list, or the window function might reference
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java b/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
index 50d255c331..127f4e4879 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/CorrelateProjectExtractor.java
@@ -88,6 +88,20 @@ public CorrelateProjectExtractor(RelBuilderFactory factory) {
this.builderFactory = factory;
}
+ /** Returns whether {@code node} is a direct field access on the correlation
+ * variable with the specified id, such as {@code $cor0.DEPTNO}. A nested
+ * access such as {@code $cor0.REC.DEPTNO} is not direct. */
+ private static boolean isDirectFieldAccess(RexNode node, CorrelationId id) {
+ if (node instanceof RexFieldAccess) {
+ RexFieldAccess access = (RexFieldAccess) node;
+ if (access.getReferenceExpr() instanceof RexCorrelVariable) {
+ RexCorrelVariable correlVar = (RexCorrelVariable) access.getReferenceExpr();
+ return correlVar.id.equals(id);
+ }
+ }
+ return false;
+ }
+
@Override public RelNode visit(LogicalCorrelate correlate) {
RelNode left = correlate.getLeft().accept(this);
RelNode right = correlate.getRight().accept(this);
@@ -95,8 +109,12 @@ public CorrelateProjectExtractor(RelBuilderFactory factory) {
// Find the correlated expressions from the right side that can be moved to the left
Set<RexNode> callsWithCorrelationInRight =
findCorrelationDependentCalls(correlate.getCorrelationId(), right);
+ // Only direct field accesses on the correlation variable, such as
+ // $cor0.DEPTNO, are left in place. A nested field access, such as
+ // $cor0.REC.DEPTNO, is extracted
boolean isTrivialCorrelation =
- callsWithCorrelationInRight.stream().allMatch(exp -> exp instanceof RexFieldAccess);
+ callsWithCorrelationInRight.stream()
+ .allMatch(exp -> isDirectFieldAccess(exp, correlate.getCorrelationId()));
// Early exit condition
if (isTrivialCorrelation) {
if (correlate.getLeft().equals(left) && correlate.getRight().equals(right)) {
@@ -116,27 +134,42 @@ public CorrelateProjectExtractor(RelBuilderFactory factory) {
// Transform the correlated expression from the right side to an expression over the left side
builder.push(left);
+ ImmutableBitSet.Builder requiredColumns = ImmutableBitSet.builder();
List<RexNode> callsWithCorrelationOverLeft = new ArrayList<>();
for (RexNode callInRight : callsWithCorrelationInRight) {
- callsWithCorrelationOverLeft.add(replaceCorrelationsWithInputRef(callInRight, builder));
+ if (isDirectFieldAccess(callInRight, correlate.getCorrelationId())) {
+ // Direct field accesses stay in the right side and keep reading their
+ // original left column; that column must remain a required column.
+ requiredColumns.set(((RexFieldAccess) callInRight).getField().getIndex());
+ } else {
+ callsWithCorrelationOverLeft.add(replaceCorrelationsWithInputRef(callInRight, builder));
+ }
}
builder.projectPlus(callsWithCorrelationOverLeft);
// Construct the mapping to transform the expressions in the right side based on the new
// projection in the left side.
Map<RexNode, RexNode> transformMapping = new HashMap<>();
+ int newFieldIndex = oldLeft;
for (RexNode callInRight : callsWithCorrelationInRight) {
- RexBuilder xb = builder.getRexBuilder();
- RexNode v = xb.makeCorrel(builder.peek().getRowType(), correlate.getCorrelationId());
- RexNode flatCorrelationInRight = xb.makeFieldAccess(v, oldLeft + transformMapping.size());
- transformMapping.put(callInRight, flatCorrelationInRight);
+ if (!isDirectFieldAccess(callInRight, correlate.getCorrelationId())) {
+ RexBuilder xb = builder.getRexBuilder();
+ RexNode v = xb.makeCorrel(builder.peek().getRowType(), correlate.getCorrelationId());
+ RexNode flatCorrelationInRight = xb.makeFieldAccess(v, newFieldIndex);
+ transformMapping.put(callInRight, flatCorrelationInRight);
+ newFieldIndex++;
+ }
}
- // Select the required fields/columns from the left side of the correlation. Based on the code
- // above all these fields should be at the end of the left relational expression.
+ // Select the required fields/columns from the left side of the correlation: the columns
+ // read by the direct field accesses plus the newly projected columns, which are at the
+ // end of the left relational expression.
List<RexNode> requiredFields =
builder.fields(
- ImmutableBitSet.range(oldLeft, oldLeft + callsWithCorrelationOverLeft.size()).asList());
+ requiredColumns
+ .set(oldLeft, oldLeft + callsWithCorrelationOverLeft.size())
+ .build()
+ .asList());
final int newLeft = builder.fields().size();
// Transform the expressions in the right side using the mapping constructed earlier.
@@ -264,8 +297,13 @@ private static boolean isSimpleCorrelatedExpression(RexNode node, CorrelationId
* +(10, $cor0.DEPTNO) -> TRUE
* /(100,+(10, $cor0.DEPTNO)) -> TRUE
* CAST(+(10, $cor0.DEPTNO)):INTEGER NOT NULL -> TRUE
+ * CASE(IS NOT NULL($cor0.PATH), $cor0.PATH, ARRAY(null:INTEGER)) -> TRUE
* +($0, $cor0.DEPTNO) -> FALSE
* }</pre>
+ *
+ * <p>A subexpression built only from literals and dynamic parameters, such
+ * as {@code ARRAY(null:INTEGER)} above, is neutral: it neither qualifies nor
+ * disqualifies the enclosing call.
*/
private static class SimpleCorrelationDetector
extends RexVisitorImpl<@Nullable Boolean> {
@@ -284,7 +322,8 @@ private SimpleCorrelationDetector(CorrelationId corrId) {
return Boolean.FALSE;
}
- @Override public Boolean visitCall(RexCall call) {
+ @Override public @Nullable Boolean visitCall(RexCall call) {
+ // Constant operands must not disqualify the call
Boolean hasSimpleCorrelation = null;
for (RexNode op : call.operands) {
Boolean b = op.accept(this);
@@ -292,7 +331,8 @@ private SimpleCorrelationDetector(CorrelationId corrId) {
hasSimpleCorrelation = hasSimpleCorrelation == null ? b : hasSimpleCorrelation && b;
}
}
- return hasSimpleCorrelation == null ? Boolean.FALSE : hasSimpleCorrelation;
+ // If unsure return null; caller will decide
+ return hasSimpleCorrelation;
}
@Override public @Nullable Boolean visitFieldAccess(RexFieldAccess fieldAccess) {
@@ -332,8 +372,10 @@ private static RexNode replaceCorrelationsWithInputRef(RexNode exp, RelBuilder b
}
/**
- * A visitor traversing row expressions and replacing calls with other expressions according
- * to the specified mapping.
+ * A visitor traversing row expressions and replacing calls and field
+ * accesses with other expressions according to the specified mapping.
+ * The mapping is consulted before recursing so that the outermost
+ * matching expression wins.
*/
private static final class CallReplacer extends RexShuttle {
private final Map<RexNode, RexNode> mapping;
@@ -350,5 +392,14 @@ private static final class CallReplacer extends RexShuttle {
return super.visitCall(oldCall);
}
}
+
+ @Override public RexNode visitFieldAccess(RexFieldAccess fieldAccess) {
+ RexNode replacement = mapping.get(fieldAccess);
+ if (replacement != null) {
+ return replacement;
+ } else {
+ return super.visitFieldAccess(fieldAccess);
+ }
+ }
}
}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 4e4104ad48..300ff959bc 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -575,10 +575,6 @@ protected RexNode removeCorrelationExpr(
// Its output does not change the input ordering, so there's no
// need to call propagateExpr.
- if (isCorVarDefined && !canDecorrelateOffsetFetch(rel)) {
- return null;
- }
-
final RelNode oldInput = rel.getInput();
final Frame frame = getInvoke(oldInput, isCorVarDefined, rel, true);
if (frame == null) {
@@ -1141,31 +1137,8 @@ private static void shiftMapping(Map<Integer, Integer> mapping, int startIndex,
return register(sort, result, mapOldToNewOutputs, corDefOutputs);
}
- static boolean canDecorrelateOffsetFetch(Sort sort) {
- final @Nullable RexLiteral fetch = sort.fetch == null
- ? null
- : RexUtil.reduceFetchToLiteral(sort.getCluster(), sort.fetch);
- return isNonNegativeIntegralLiteral(sort.offset)
- && (sort.fetch == null
- || fetch != null && isNonNegativeIntegralLiteral(fetch));
- }
-
- private static boolean isNonNegativeIntegralLiteral(@Nullable RexNode node) {
- if (node == null) {
- return true;
- }
- if (!(node instanceof RexLiteral)) {
- return false;
- }
- final @Nullable BigDecimal value =
- ((RexLiteral) node).getValueAs(BigDecimal.class);
- return value != null
- && value.signum() >= 0
- && value.stripTrailingZeros().scale() <= 0;
- }
-
protected @Nullable Frame decorrelateSortAsAggregate(Sort sort, final Frame frame) {
- if (sort.offset != null || !(sort.fetch instanceof RexLiteral)) {
+ if (sort.offset != null || sort.fetch == null) {
return null;
}
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
index 4139cf4b2b..291eb619d0 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -233,14 +233,12 @@ public static RelNode decorrelateQuery(RelNode rel, RelBuilder builder) {
RelNode preparedRel = prePlanner.findBestExp();
// start decorrelating
+ TopDownGeneralDecorrelator decorrelator = createEmptyDecorrelator(builder);
RelNode decorrelateNode = rel;
- if (canDecorrelateOffsetFetch(preparedRel, false)) {
- TopDownGeneralDecorrelator decorrelator = createEmptyDecorrelator(builder);
- try {
- decorrelateNode = decorrelator.correlateElimination(preparedRel, true);
- } catch (UnsupportedOperationException e) {
- // if the correlation exists in an unsupported operator, retain the original plan.
- }
+ try {
+ decorrelateNode = decorrelator.correlateElimination(preparedRel, true);
+ } catch (UnsupportedOperationException e) {
+ // if the correlation exists in an unsupported operator, retain the original plan.
}
HepProgram postProgram = HepProgram.builder()
@@ -257,29 +255,6 @@ public static RelNode decorrelateQuery(RelNode rel, RelBuilder builder) {
return postPlanner.findBestExp();
}
- /** Returns whether correlated Sorts in a tree have OFFSET and FETCH values
- * that can be decorrelated without changing their row-count semantics. */
- private static boolean canDecorrelateOffsetFetch(RelNode rel,
- boolean isCorVarDefined) {
- if (isCorVarDefined && rel instanceof Sort
- && !RelDecorrelator.canDecorrelateOffsetFetch((Sort) rel)) {
- return false;
- }
- if (rel instanceof Correlate) {
- final Correlate correlate = (Correlate) rel;
- if (!canDecorrelateOffsetFetch(correlate.getLeft(), isCorVarDefined)) {
- return false;
- }
- return canDecorrelateOffsetFetch(correlate.getRight(), true);
- }
- for (RelNode input : rel.getInputs()) {
- if (!canDecorrelateOffsetFetch(input, isCorVarDefined)) {
- return false;
- }
- }
- return true;
- }
-
/**
* Eliminates Correlate.
*
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 2309102ff8..36d56a9f04 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -86,7 +86,6 @@
import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexUnknownAs;
import org.apache.calcite.rex.RexUtil;
-import org.apache.calcite.rex.RexVisitorImpl;
import org.apache.calcite.rex.RexWindowBound;
import org.apache.calcite.rex.RexWindowBounds;
import org.apache.calcite.rex.RexWindowExclusion;
@@ -109,7 +108,6 @@
import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlReturnTypeInference;
import org.apache.calcite.sql.type.SqlTypeName;
-import org.apache.calcite.sql.type.SqlTypeUtil;
import org.apache.calcite.sql.type.TableFunctionReturnTypeInference;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.sql2rel.SqlToRelConverter;
@@ -3803,7 +3801,8 @@ public RelBuilder sortLimit(Number offset, Number fetch,
*
* @param offsetNode RexLiteral means number of rows to skip is deterministic,
* RexDynamicParam means number of rows to skip is dynamic.
- * @param fetchNode Maximum number of rows to fetch
+ * @param fetchNode RexLiteral means maximum number of rows to fetch is deterministic,
+ * RexDynamicParam mean maximum number is dynamic.
* @param nodes Sort expressions
*/
public RelBuilder sortLimit(@Nullable RexNode offsetNode, @Nullable RexNode fetchNode,
@@ -3813,17 +3812,12 @@ public RelBuilder sortLimit(@Nullable RexNode offsetNode, @Nullable RexNode fetc
throw new IllegalArgumentException("OFFSET node must be RexLiteral or RexDynamicParam");
}
}
- if (fetchNode != null && !isValidFetchExpression(fetchNode)) {
- throw new IllegalArgumentException(
- "FETCH node must not reference input fields or contain aggregate functions, "
- + "window functions, or subqueries");
- }
- if (fetchNode != null
- && !SqlTypeUtil.isNumeric(fetchNode.getType())) {
- throw new IllegalArgumentException(
- "FETCH node must have a numeric type; actual type is "
- + fetchNode.getType().getFullTypeString());
+ if (fetchNode != null) {
+ if (!(fetchNode instanceof RexLiteral || fetchNode instanceof RexDynamicParam)) {
+ throw new IllegalArgumentException("FETCH node must be RexLiteral or RexDynamicParam");
+ }
}
+
final Registrar registrar = new Registrar(fields(), ImmutableList.of());
final List<RelFieldCollation> fieldCollations =
registrar.registerFieldCollations(nodes);
@@ -3890,38 +3884,6 @@ public RelBuilder sortLimit(@Nullable RexNode offsetNode, @Nullable RexNode fetc
return this;
}
- private static boolean isValidFetchExpression(RexNode node) {
- return Boolean.TRUE.equals(node.accept(new FetchExpressionVisitor()));
- }
-
- /** Visitor that validates FETCH expressions. */
- private static class FetchExpressionVisitor
- extends RexVisitorImpl<@Nullable Boolean> {
- FetchExpressionVisitor() {
- super(false);
- }
-
- @Override public Boolean visitLiteral(RexLiteral literal) {
- return true;
- }
-
- @Override public Boolean visitDynamicParam(RexDynamicParam dynamicParam) {
- return true;
- }
-
- @Override public Boolean visitCall(RexCall call) {
- if (call.getOperator().isAggregator()) {
- return false;
- }
- for (RexNode operand : call.getOperands()) {
- if (!Boolean.TRUE.equals(operand.accept(this))) {
- return false;
- }
- }
- return true;
- }
- }
-
private static RelFieldCollation collation(RexNode node,
RelFieldCollation.Direction direction,
RelFieldCollation.@Nullable NullDirection nullDirection,
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 19f3ef47a8..f4f16d7326 100644
--- a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++ b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -61,9 +61,6 @@ ValidatorContext=From line {0,number,#}, column {1,number,#} to line {2,number,#
CannotCastValue=Cast function cannot convert value of type {0} to type {1}
UnknownDatatypeName=Unknown datatype name ''{0}''
IncompatibleValueType=Values passed to {0} operator must have compatible types
-FetchExpressionMustBeNumeric=FETCH expression must have a numeric type; actual type is ''{0}''
-FetchExpressionCannotReferenceColumn=FETCH expression cannot reference table column ''{0}''
-FetchExpressionEvaluatedToNull=FETCH expression evaluated to NULL
IncompatibleTypesInList=Values in expression list must have compatible types
IncompatibleCharset=Cannot apply operation ''{0}'' to strings with different charsets ''{1}'' and ''{2}''
InvalidOrderByPos=ORDER BY is only allowed on top-level SELECT
diff --git a/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumUtilsTest.java b/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumUtilsTest.java
index 70370d7bb1..40b825939d 100644
--- a/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumUtilsTest.java
+++ b/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumUtilsTest.java
@@ -34,7 +34,6 @@
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
-import static org.junit.jupiter.api.Assertions.assertThrows;
/**
* Tests for {@link EnumUtils}.
@@ -187,16 +186,6 @@ public final class EnumUtilsTest {
is(BigDecimal.valueOf(2)));
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testNumberToBigDecimalRejectsNull() {
- final IllegalArgumentException e =
- assertThrows(IllegalArgumentException.class,
- () -> EnumUtils.numberToBigDecimal(null, "FETCH"));
- assertThat(e.getMessage(), is("FETCH expression evaluated to NULL"));
- }
-
@Test void testMethodCallExpression() {
// test for Object.class method parameter type
final ConstantExpression arg0 = Expressions.constant(1, int.class);
diff --git a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 279454f6b3..d29bcaa1bd 100644
--- a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -3060,25 +3060,25 @@ private SqlDialect nonOrdinalDialect() {
+ " as MAP<varchar,varchar> array)";
final String expectedClickHouse2 =
"SELECT CAST(array(map('a', '1'), map('b', '2'), map('c', '3'))"
- + " AS Array(Map(`String`, `Nullable(String)`)))";
+ + " AS Array(Map(`String`, `String`)))";
sql(query2).withClickHouse().ok(expectedClickHouse2);
final String query3 = "select cast(MAP['a',ARRAY[1,2,3]]"
+ " as MAP<varchar,integer array>)";
final String expectedClickHouse3 =
- "SELECT CAST(map('a', array(1, 2, 3)) AS Map(`String`, Array(`Nullable(Int32)`)))";
+ "SELECT CAST(map('a', array(1, 2, 3)) AS Map(`String`, Array(`Int32`)))";
sql(query3).withClickHouse().ok(expectedClickHouse3);
final String query4 = "select cast(MAP['a',ARRAY[1.0,2.0,3.0]]"
+ " as MAP<varchar,real array>)";
final String expectedClickHouse4 =
- "SELECT CAST(map('a', array(1.0, 2.0, 3.0)) AS Map(`String`, Array(`Nullable(Float32)`)))";
+ "SELECT CAST(map('a', array(1.0, 2.0, 3.0)) AS Map(`String`, Array(`Float32`)))";
sql(query4).withClickHouse().ok(expectedClickHouse4);
final String query5 = "select cast(MAP['a',MAP['b','c']]"
+ " as MAP<varchar,MAP<varchar,varchar>>)";
final String expectedClickHouse5 =
- "SELECT CAST(map('a', map('b', 'c')) AS Map(`String`, Map(`String`, `Nullable(String)`)))";
+ "SELECT CAST(map('a', map('b', 'c')) AS Map(`String`, Map(`String`, `String`)))";
sql(query5).withClickHouse().ok(expectedClickHouse5);
}
@@ -4950,74 +4950,6 @@ private SqlDialect nonOrdinalDialect() {
.withSybase().ok(expectedSybase);
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionWithLimitDialect() {
- final String query = "select \"product_id\"\n"
- + "from \"product\"\n"
- + "fetch next (1 + 2) rows only";
- final String expected = "SELECT `product_id`\n"
- + "FROM `foodmart`.`product`\n"
- + "LIMIT 3";
- sql(query).withMysql().ok(expected);
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testNegativeFetchExpressionIsRejectedBeforeSqlGeneration() {
- final String query = "select \"product_id\"\n"
- + "from \"product\"\n"
- + "fetch next (0 - 1) rows only";
- final String error =
- "FETCH value -1 is out of range; expected a non-negative value";
- sql(query).throws_(error);
- sql(query).withMysql().throws_(error);
- sql(query).withSQLite().throws_(error);
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testParameterizedFetchExpressionWithLimitDialect() {
- final String query = "select \"product_id\"\n"
- + "from \"product\"\n"
- + "fetch next (? + 1) rows only";
- sql(query).withMysql().throws_(
- "LIMIT dialect does not support FETCH expressions that cannot "
- + "be reduced to a literal");
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testParameterizedFetchExpressionWithSQLite() {
- final String query = "select \"product_id\"\n"
- + "from \"product\"\n"
- + "fetch next (? + 1) rows only";
- final String expected = "SELECT \"product_id\"\n"
- + "FROM \"foodmart\".\"product\"\n"
- + "LIMIT ? + 1";
- sql(query).withSQLite().ok(expected);
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testDynamicFetchExpressionIsNotReduced() {
- final String query = "select \"product_id\"\n"
- + "from \"product\"\n"
- + "fetch next (extract(day from current_date)) rows only";
- final String expected = "SELECT \"product_id\"\n"
- + "FROM \"foodmart\".\"product\"\n"
- + "FETCH NEXT (EXTRACT(DAY FROM CURRENT_DATE)) ROWS ONLY";
- sql(query).ok(expected);
- sql(query).withMysql().throws_(
- "LIMIT dialect does not support FETCH expressions that cannot "
- + "be reduced to a literal");
- }
-
@Test void testSelectQueryComplex() {
String query =
"select count(*), \"units_per_case\" from \"product\" where \"cases_per_pallet\" > 100 "
@@ -5752,15 +5684,15 @@ private SqlDialect nonOrdinalDialect() {
@Test void testCastAsMapType() {
sql("SELECT CAST(MAP['A', 1.0] AS MAP<VARCHAR, DOUBLE>)")
.ok("SELECT CAST(MAP['A', 1.0] AS "
- + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\", DOUBLE NULL >)\n"
+ + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\", DOUBLE >)\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")");
sql("SELECT CAST(MAP['A', ARRAY[1, 2, 3]] AS MAP<VARCHAR, INT ARRAY>)")
.ok("SELECT CAST(MAP['A', ARRAY[1, 2, 3]] AS "
- + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\", INTEGER ARRAY NULL >)\n"
+ + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\", INTEGER ARRAY >)\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")");
sql("SELECT CAST(MAP[ARRAY['A'], MAP[1, 2]] AS MAP<VARCHAR ARRAY, MAP<INT, INT>>)")
.ok("SELECT CAST(MAP[ARRAY['A'], MAP[1, 2]] AS "
- + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\" ARRAY, MAP< INTEGER, INTEGER NULL > NULL >)\n"
+ + "MAP< VARCHAR CHARACTER SET \"ISO-8859-1\" ARRAY, MAP< INTEGER, INTEGER > >)\n"
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")");
}
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 5f8b8edfb1..b22218b955 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -83,7 +83,6 @@
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotEquals;
import static org.junit.jupiter.api.Assertions.assertNotSame;
-import static org.junit.jupiter.api.Assertions.assertThrows;
import static org.junit.jupiter.api.Assertions.fail;
import static java.util.Objects.requireNonNull;
@@ -3632,35 +3631,6 @@ private void assertTypeAndToString(
hasSize(0));
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testContainsDynamicParam() {
- final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
- final RexNode literal = rexBuilder.makeExactLiteral(BigDecimal.ONE, intType);
- final RexNode dynamicParam = rexBuilder.makeDynamicParam(intType, 0);
- final RexNode expression =
- rexBuilder.makeCall(SqlStdOperatorTable.PLUS, literal, dynamicParam);
-
- assertThat(RexUtil.containsDynamicParam(literal), is(false));
- assertThat(RexUtil.containsDynamicParam(dynamicParam), is(true));
- assertThat(RexUtil.containsDynamicParam(expression), is(true));
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testValidateFetchValueAllowsFractionalBigDecimal() {
- assertThat(RexUtil.validateFetchValue(new BigDecimal("1.5")),
- is(new BigDecimal("1.5")));
-
- final IllegalArgumentException e =
- assertThrows(IllegalArgumentException.class,
- () -> RexUtil.validateFetchValue(new BigDecimal("-1.5")));
- assertThat(e.getMessage(),
- containsString("FETCH value -1.5 is out of range"));
- }
-
@Test void testConstantMap() {
final RelDataType intType = typeFactory.createSqlType(SqlTypeName.INTEGER);
final RelDataType bigintType = typeFactory.createSqlType(SqlTypeName.BIGINT);
diff --git a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
index f0ff190d28..68a34eb5ba 100644
--- a/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/type/SqlTypeFactoryTest.java
@@ -87,6 +87,42 @@ class SqlTypeFactoryTest {
assertThat(leastRestrictive.isNullable(), is(true));
}
+ /** UNKNOWN types in leastRestrictive() affect only the result nullability. */
+ @Test void testLeastRestrictiveWithUnknown() {
+ SqlTypeFixture f = new SqlTypeFixture();
+ // UNKNOWN never constrains the result, no matter its position
+ checkUnknownWithType(f, f.sqlBigInt);
+ checkUnknownWithType(f, f.structOfInt);
+ checkUnknownWithType(f, f.arrayBigInt);
+ checkUnknownWithType(f, f.mapOfInt);
+ // A nullable UNKNOWN makes the result nullable
+ RelDataType leastRestrictive =
+ f.typeFactory.leastRestrictive(
+ Lists.newArrayList(f.structOfInt,
+ f.typeFactory.createTypeWithNullability(f.sqlUnknown, true)));
+ assertThat(leastRestrictive, notNullValue());
+ assertThat(leastRestrictive.getSqlTypeName(), is(SqlTypeName.ROW));
+ assertThat(leastRestrictive.isNullable(), is(true));
+ // A list of UNKNOWN unifies to UNKNOWN
+ leastRestrictive =
+ f.typeFactory.leastRestrictive(
+ Lists.newArrayList(f.sqlUnknown, f.sqlUnknown));
+ assertThat(leastRestrictive.getSqlTypeName(), is(SqlTypeName.UNKNOWN));
+ }
+
+ /** Checks that leastRestictive({@code type}, UNKNOWN) yields {@code type},
+ * in either order. */
+ private void checkUnknownWithType(SqlTypeFixture f, RelDataType type) {
+ RelDataType r1 =
+ f.typeFactory.leastRestrictive(Lists.newArrayList(type, f.sqlUnknown));
+ RelDataType r2 =
+ f.typeFactory.leastRestrictive(Lists.newArrayList(f.sqlUnknown, type));
+ assertThat(r1, notNullValue());
+ assertThat(r2, notNullValue());
+ assertThat(r1.getFullTypeString(), is(type.getFullTypeString()));
+ assertThat(r2.getFullTypeString(), is(type.getFullTypeString()));
+ }
+
@Test void testLeastRestrictiveStructWithNull() {
SqlTypeFixture f = new SqlTypeFixture();
RelDataType leastRestrictive =
diff --git a/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java b/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
index 72276d42c3..da0a17296b 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/CorrelateProjectExtractorTest.java
@@ -84,6 +84,78 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a href="https://issues.apache.org/jira/browse/CALCITE-7646">[CALCITE-7646]
+ * CorrelateProjectExtractor does not handle nested field accesses cor0.field0.field1</a>. */
+ @Test void testNestedCorrelationFieldAccessInFilter() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
+ RelNode before = builder.scan("EMP")
+ .project(
+ builder.alias(
+ builder.call(SqlStdOperatorTable.ROW,
+ builder.field("EMPNO"), builder.field("DEPTNO")), "R"))
+ .variable(v::set)
+ .scan("DEPT")
+ .filter(
+ builder.equals(builder.field(0),
+ builder.getRexBuilder().makeFieldAccess(builder.field(v.get(), "R"), 1)))
+ .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "R"))
+ .build();
+
+ final String planBefore = ""
+ + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])\n"
+ + " LogicalProject(R=[ROW($0, $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.R.EXPR$1)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ RelNode after = before.accept(new CorrelateProjectExtractor(RelFactories.LOGICAL_BUILDER));
+ final String planAfter = ""
+ + "LogicalProject(R=[$0], DEPTNO=[$2], DNAME=[$3], LOC=[$4])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])\n"
+ + " LogicalProject(R=[ROW($0, $7)], $f1=[ROW($0, $7).EXPR$1])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.$f1)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Tests that a constant call operand, such as {@code POWER(2, 3)}, does
+ * not prevent extracting the enclosing correlated call. */
+ @Test void testCorrelationCallWithConstantCallOperandInFilter() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
+ RelNode before = builder.scan("EMP")
+ .variable(v::set)
+ .scan("DEPT")
+ .filter(
+ builder.equals(builder.field(0),
+ builder.call(SqlStdOperatorTable.PLUS,
+ builder.call(SqlStdOperatorTable.POWER,
+ builder.literal(2), builder.literal(3)),
+ builder.field(v.get(), "DEPTNO"))))
+ .correlate(JoinRelType.LEFT, v.get().id, builder.field(2, 0, "DEPTNO"))
+ .build();
+
+ final String planBefore = ""
+ + "LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, +(POWER(2, 3), $cor0.DEPTNO))])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ RelNode after = before.accept(new CorrelateProjectExtractor(RelFactories.LOGICAL_BUILDER));
+ final String planAfter = ""
+ + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$9], DNAME=[$10], LOC=[$11])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{8}])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], $f8=[+(POWER(2, 3), $7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.$f8)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
@Test void testDoubleCorrelationCallOverVariableInFilters() {
final RelBuilder builder = RelBuilder.create(config().build());
final Holder<@Nullable RexCorrelVariable> v = Holder.empty();
diff --git a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
index 51c1e89d2d..a15d741dc9 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -290,9 +290,9 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left])\n"
+ " LogicalValues(tuples=[[{ 7369 }, { 7499 }]])\n"
+ " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
- + " LogicalProject(EMPNO1=[$12], EXPR$0=[||(||($1, ' from dept '), $13)])\n"
- + " LogicalJoin(condition=[AND(=($7, $10), =($9, $11))], joinType=[left])\n"
- + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO0=[$7], EMPNO0=[CAST($0):INTEGER NOT NULL])\n"
+ + " LogicalProject(EMPNO1=[$11], EXPR$0=[||(||($1, ' from dept '), $12)])\n"
+ + " LogicalJoin(condition=[AND(=($7, $9), =($8, $10))], joinType=[left])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], EMPNO0=[CAST($0):INTEGER NOT NULL])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
+ " LogicalAggregate(group=[{0, 1, 2}], agg#0=[SINGLE_VALUE($3)])\n"
+ " LogicalProject(DEPTNO0=[$3], EMPNO0=[$4], EMPNO=[$5], DNAME=[$1])\n"
@@ -556,29 +556,29 @@ public static Frameworks.ConfigBuilder config() {
// LogicalTableScan(table=[[scott, EMP]])
final String planAfter = ""
+ "LogicalSort(sort0=[$0], dir0=[ASC])\n"
- + " LogicalProject(DNAME=[$1], C=[$7])\n"
- + " LogicalJoin(condition=[AND(=($0, $5), =($4, $6))], joinType=[left])\n"
- + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[$0], $f4=[*($0, 100)])\n"
+ + " LogicalProject(DNAME=[$1], C=[$6])\n"
+ + " LogicalJoin(condition=[AND(=($0, $4), =($3, $5))], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f3=[*($0, 100)])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
- + " LogicalProject(DEPTNO8=[$0], $f4=[$1], EXPR$0=[CASE(IS NOT NULL($4), $4, 0)])\n"
+ + " LogicalProject(DEPTNO8=[$0], $f3=[$1], EXPR$0=[CASE(IS NOT NULL($4), $4, 0)])\n"
+ " LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($0, $2), IS NOT DISTINCT FROM($1, $3))], joinType=[left])\n"
- + " LogicalProject(DEPTNO=[$0], $f4=[*($0, 100)])\n"
+ + " LogicalProject(DEPTNO=[$0], $f3=[*($0, 100)])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
+ " LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT()])\n"
- + " LogicalProject(DEPTNO8=[$7], $f4=[$9])\n"
+ + " LogicalProject(DEPTNO8=[$7], $f3=[$9])\n"
+ " LogicalFilter(condition=[IS NOT NULL($7)])\n"
- + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], i=[$11], $f4=[$9])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], i=[$11], $f3=[$9])\n"
+ " LogicalJoin(condition=[=($8, $10)], joinType=[inner])\n"
+ " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SAL0=[CAST($5):DECIMAL(12, 2)])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
- + " LogicalProject($f4=[$0], SAL0=[$1], $f2=[true])\n"
+ + " LogicalProject($f3=[$0], SAL0=[$1], $f2=[true])\n"
+ " LogicalAggregate(group=[{0, 1}])\n"
- + " LogicalProject($f4=[$1], SAL0=[$2])\n"
+ + " LogicalProject($f3=[$1], SAL0=[$2])\n"
+ " LogicalJoin(condition=[AND(>($2, CAST($0):DECIMAL(12, 2) NOT NULL), <($1, $0))], joinType=[inner])\n"
+ " LogicalValues(tuples=[[{ 1000 }, { 2000 }, { 3000 }]])\n"
+ " LogicalJoin(condition=[true], joinType=[inner])\n"
+ " LogicalAggregate(group=[{0}])\n"
- + " LogicalProject($f4=[*($0, 100)])\n"
+ + " LogicalProject($f3=[*($0, 100)])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
+ " LogicalAggregate(group=[{0}])\n"
+ " LogicalProject(SAL0=[CAST($5):DECIMAL(12, 2)])\n"
@@ -1870,9 +1870,9 @@ public static Frameworks.ConfigBuilder config() {
RelDecorrelator.decorrelateQuery(before, builder, RuleSets.ofList(Collections.emptyList()),
RuleSets.ofList(Collections.emptyList()));
final String planAfter = ""
- + "LogicalProject(DEPTNO=[$0], I0=[$3], I1=[$8])\n"
- + " LogicalJoin(condition=[AND(=($0, $6), =($5, $7))], joinType=[left])\n"
- + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EXPR$0=[$5], DEPTNO0=[$0], $f5=[>(CAST($0):INTEGER NOT NULL, 0)])\n"
+ + "LogicalProject(DEPTNO=[$0], I0=[$3], I1=[$7])\n"
+ + " LogicalJoin(condition=[AND(=($0, $5), =($4, $6))], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EXPR$0=[$5], $f4=[>(CAST($0):INTEGER NOT NULL, 0)])\n"
+ " LogicalJoin(condition=[=($3, $4)], joinType=[left])\n"
+ " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[CAST($0):SMALLINT NOT NULL])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
@@ -1884,12 +1884,12 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalProject(DEPTNO0=[CAST($0):SMALLINT NOT NULL])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
+ " LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($2)])\n"
- + " LogicalProject(DEPTNO0=[$8], $f5=[$9], $f0=[0])\n"
+ + " LogicalProject(DEPTNO0=[$8], $f4=[$9], $f0=[0])\n"
+ " LogicalJoin(condition=[=($7, $8)], joinType=[inner])\n"
+ " LogicalFilter(condition=[=($1, 'SMITH')])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
+ " LogicalFilter(condition=[$1])\n"
- + " LogicalProject(DEPTNO=[$0], $f5=[>(CAST($0):INTEGER NOT NULL, 0)])\n"
+ + " LogicalProject(DEPTNO=[$0], $f4=[>(CAST($0):INTEGER NOT NULL, 0)])\n"
+ " LogicalJoin(condition=[=($3, $4)], joinType=[left])\n"
+ " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], DEPTNO0=[CAST($0):SMALLINT NOT NULL])\n"
+ " LogicalTableScan(table=[[scott, DEPT]])\n"
@@ -2428,4 +2428,67 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalTableScan(table=[[scott, EMP]])\n";
assertThat(after, hasTree(planAfter));
}
+
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7646">[CALCITE-7646]
+ * CorrelateProjectExtractor does not handle nested field accesses cor0.field0.field1</a>. */
+ @Test void testNestedCorrelatedFieldAccess() throws SqlParseException {
+ final String sql = "select a.\"aid\", t.lat\n"
+ + "from \"bookstore\".\"authors\" a,\n"
+ + "lateral (select b.\"aid\" as c,\n"
+ + " (a.\"birthPlace\").\"coords\".\"latitude\" as lat\n"
+ + " from \"bookstore\".\"authors\" b\n"
+ + " where b.\"aid\" = a.\"aid\") as t";
+ SchemaPlus rootSchema = Frameworks.createRootSchema(true);
+ CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.BOOKSTORE);
+ CalciteConnectionConfig config = new CalciteConnectionConfigImpl(new Properties());
+ // The Frameworks planner cannot be used here because it flattens
+ // structured types, and RelStructuredTypeFlattener does not support
+ // correlations on structured columns.
+ SqlTestFactory factory = SqlTestFactory.INSTANCE
+ .withCatalogReader((typeFactory, caseSensitive) ->
+ new CalciteCatalogReader(
+ CalciteSchema.from(rootSchema),
+ ImmutableList.of("bookstore"),
+ typeFactory,
+ config));
+ SqlParser parser = factory.createParser(sql);
+ SqlNode parsed = parser.parseQuery();
+ final SqlToRelConverter sqlToRelConverter = factory.createSqlToRelConverter();
+ assert sqlToRelConverter.validator != null;
+ final SqlNode validated = sqlToRelConverter.validator.validate(parsed);
+ final RelNode before = sqlToRelConverter.convertQuery(validated, false, true).rel;
+
+ final String planBefore = ""
+ + "LogicalProject(aid=[$0], LAT=[$5])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{0, 2}])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalProject(C=[$0], LAT=[$cor1.birthPlace.coords.latitude])\n"
+ + " LogicalFilter(condition=[=($0, $cor1.aid)])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ final RelBuilder relBuilder =
+ RelFactories.LOGICAL_BUILDER.create(before.getCluster(), null);
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, relBuilder,
+ RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+
+ // The nested field access is extracted into the projection $f4 on the
+ // left side and no correlation variables remain.
+ final String planAfter = ""
+ + "LogicalProject(aid=[$0], LAT=[$6])\n"
+ + " LogicalJoin(condition=[AND(=($0, $7), IS NOT DISTINCT FROM($4, $8))], joinType=[inner])\n"
+ + " LogicalProject(aid=[$0], name=[$1], birthPlace=[$2], books=[$3], $f4=[$2.coords.latitude])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalProject(C=[$0], LAT=[$4], aid=[$0], $f4=[$4])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject($f4=[$2.coords.latitude])\n"
+ + " LogicalTableScan(table=[[bookstore, authors]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcTest.java b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index f213b12dcb..83ce5b1d78 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -3581,181 +3581,6 @@ public void checkOrderBy(final boolean desc,
+ "store_id=4; grocery_sqft=16844\n");
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpression() {
- CalciteAssert.that()
- .query("select * from (values (1), (2), (3), (4)) as t(x)\n"
- + "fetch next (1 + abs(-2)) rows only")
- .returns("X=1\n"
- + "X=2\n"
- + "X=3\n");
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testBindableFetchExpression() {
- try (Hook.Closeable ignored = Hook.ENABLE_BINDABLE.addThread(Hook.propertyJ(true))) {
- final CalciteAssert.AssertThat with = CalciteAssert.that();
- with
- .query("select * from (values (1), (2), (3), (4)) as t(x)\n"
- + "fetch next (rand_integer(1) + 2) rows only")
- .explainContains("BindableSort(fetch=[+(RAND_INTEGER(1), 2)])")
- .returns("X=1\n"
- + "X=2\n");
- with.query("select * from (values (1), (2), (3), (4)) as t(x)\n"
- + "fetch next (cast(9223372036854775808 as decimal(20, 0))) rows only")
- .returns("X=1\nX=2\nX=3\nX=4\n");
- with.query("select * from (values (1), (2), (3), (4)) as t(x)\n"
- + "order by x fetch next ? rows only")
- .explainContains("BindableSort(sort0=[$0], dir0=[ASC], fetch=[?0])")
- .consumesPreparedStatement(p ->
- p.setBigDecimal(1, new BigDecimal("1.5")))
- .returns("X=1\n"
- + "X=2\n");
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionFunctionArguments() {
- final CalciteAssert.AssertThat with = CalciteAssert.that();
- final String values = "select * from (values (1), (2), (3)) as t(x)\n";
- with.query(values + "fetch next (abs(2)) rows only")
- .returns("X=1\n"
- + "X=2\n");
- with.query(values + "fetch next (abs(-2)) rows only")
- .returns("X=1\n"
- + "X=2\n");
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionInvalidValue() {
- final CalciteAssert.AssertThat with = CalciteAssert.that();
- final String values = "select * from (values (1), (2), (3)) as t(x)\n";
- with.query(values + "fetch next (0 - 1) rows only")
- .throws_("FETCH must not be negative");
- with.query(values + "fetch next (-1) rows only")
- .throws_("FETCH must not be negative");
- with.query(values
- + "fetch next (cast(null as integer)) rows only")
- .throws_("FETCH expression evaluated to NULL");
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testCorrelatedFetchExpressionInvalidValue() {
- final String sqlPrefix = "select d.\"name\", e.\"name\"\n"
- + "from \"hr\".\"depts\" d,\n"
- + "lateral (select \"name\" from \"hr\".\"emps\"\n"
- + " where \"deptno\" = d.\"deptno\"\n";
- for (String fetch : new String[] {"(0 - 1)", "(-1)"}) {
- for (boolean topDown : new boolean[] {false, true}) {
- CalciteAssert.hr()
- .with(CalciteConnectionProperty.TOPDOWN_GENERAL_DECORRELATION_ENABLED, topDown)
- .query(sqlPrefix + " fetch next " + fetch + " rows only) e")
- .throws_("FETCH value -1 is out of range");
- }
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testCorrelatedFractionalOffsetFetch() {
- final String sqlPrefix = "select d.\"name\" as dname, e.\"name\" as ename\n"
- + "from \"hr\".\"depts\" d,\n"
- + "lateral (select \"empid\", \"name\" from \"hr\".\"emps\"\n"
- + " where \"deptno\" = d.\"deptno\"\n"
- + " order by \"empid\" ";
- final String sqlSuffix = ") e\norder by e.\"empid\"";
- for (boolean topDown : new boolean[] {false, true}) {
- final CalciteAssert.AssertThat with = CalciteAssert.hr()
- .with(CalciteConnectionProperty.TOPDOWN_GENERAL_DECORRELATION_ENABLED, topDown);
- with.query(sqlPrefix + "fetch next (0.5 + 1) rows only" + sqlSuffix)
- .returns("DNAME=Sales; ENAME=Bill\n"
- + "DNAME=Sales; ENAME=Theodore\n");
- with.query(sqlPrefix + "offset 1.5 rows fetch next 1 row only" + sqlSuffix)
- .returns("DNAME=Sales; ENAME=Sebastian\n");
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testCorrelatedPreparedFractionalOffset() throws Exception {
- final String sql = "select d.\"name\" as dname, e.\"name\" as ename\n"
- + "from \"hr\".\"depts\" d,\n"
- + "lateral (select \"empid\", \"name\" from \"hr\".\"emps\"\n"
- + " where \"deptno\" = d.\"deptno\"\n"
- + " order by \"empid\" offset ? rows fetch next 1 row only) e\n"
- + "order by e.\"empid\"";
- for (boolean topDown : new boolean[] {false, true}) {
- CalciteAssert.hr()
- .with(CalciteConnectionProperty.TOPDOWN_GENERAL_DECORRELATION_ENABLED, topDown)
- .doWithConnection(connection -> {
- checkPreparedBigDecimalParameter(connection, sql,
- new BigDecimal("1.5"),
- "DNAME=Sales; ENAME=Sebastian\n");
- });
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testCorrelatedPreparedFetchExpression() throws Exception {
- for (String fetch : new String[] {"?", "(? + 0)"}) {
- final String sql = "select d.\"name\" as dname, e.\"name\" as ename\n"
- + "from \"hr\".\"depts\" d,\n"
- + "lateral (select \"empid\", \"name\" from \"hr\".\"emps\"\n"
- + " where \"deptno\" = d.\"deptno\"\n"
- + " order by \"empid\" fetch next " + fetch + " rows only) e\n"
- + "order by e.\"empid\"";
- for (boolean topDown : new boolean[] {false, true}) {
- CalciteAssert.hr()
- .with(CalciteConnectionProperty.TOPDOWN_GENERAL_DECORRELATION_ENABLED, topDown)
- .doWithConnection(connection -> {
- checkPreparedFetchRepeated(connection, sql,
- new int[] {1, 3},
- new String[] {
- "DNAME=Sales; ENAME=Bill\n",
- "DNAME=Sales; ENAME=Bill\n"
- + "DNAME=Sales; ENAME=Theodore\n"
- + "DNAME=Sales; ENAME=Sebastian\n"
- });
- checkPreparedParameterFails(connection, sql, -1,
- "FETCH must not be negative");
- checkPreparedParameterNullFails(connection, sql,
- "FETCH expression evaluated to NULL");
- });
- }
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionBeyondLong() {
- final CalciteAssert.AssertThat with = CalciteAssert.that();
- final String values = "select * from (values (1), (2), (3), (4)) as t(x)\n";
- final String expected = "X=1\nX=2\nX=3\nX=4\n";
- with.query(values + "fetch next 9223372036854775808 rows only")
- .returns(expected);
- with.query(values + "fetch next "
- + "(cast(9223372036854775808 as decimal(20, 0)) + 1) rows only")
- .returns(expected);
- with.query(values + "order by x fetch next "
- + "(cast(9223372036854775808 as decimal(20, 0)) + 1) rows only")
- .returns(expected);
- }
-
/** Tests ORDER BY ... OFFSET ... FETCH. */
@Test void testOrderByOffsetFetch() {
CalciteAssert.that()
@@ -6233,169 +6058,6 @@ private CalciteAssert.AssertQuery withEmpDept(String sql) {
"name=Theodore");
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testPreparedFetchExpression() throws Exception {
- CalciteAssert.that()
- .doWithConnection(connection -> {
- final String values =
- "select * from (values (1), (2), (3), (4)) as t(x)\n";
- checkPreparedFetch(connection, values + "fetch next (?) rows only",
- 2, "X=1\nX=2\n");
- checkPreparedFetch(connection, values + "fetch next (? + 1) rows only",
- 2, "X=1\nX=2\nX=3\n");
- checkPreparedFetch(connection,
- values + "fetch next (abs(cast(? as integer))) rows only",
- 2, "X=1\nX=2\n");
- checkPreparedFetch(connection,
- values + "fetch next (abs(cast(? as integer))) rows only",
- -2, "X=1\nX=2\n");
- checkPreparedFetchRepeated(connection,
- values + "fetch next (?) rows only",
- new int[] {1, 3},
- new String[] {"X=1\n", "X=1\nX=2\nX=3\n"});
- checkPreparedFetchRepeated(connection,
- values + "fetch next (? + 1) rows only",
- new int[] {0, 2, 3},
- new String[] {"X=1\n", "X=1\nX=2\nX=3\n",
- "X=1\nX=2\nX=3\nX=4\n"});
- checkPreparedFetch(connection,
- values + "fetch next (? + abs(2)) rows only",
- 1, "X=1\nX=2\nX=3\n");
- checkPreparedBigDecimalParameter(connection,
- values + "fetch next (cast(? as decimal(20, 0))) rows only",
- new BigDecimal("9223372036854775808"),
- "X=1\nX=2\nX=3\nX=4\n");
-
- checkPreparedParameterFails(connection,
- values + "fetch next (?) rows only", -1,
- "FETCH must not be negative");
- checkPreparedParameterFails(connection,
- values + "fetch next (? + 1) rows only", -2,
- "FETCH must not be negative");
- });
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testBindablePreparedFetchExpression() throws Exception {
- try (Hook.Closeable ignored = Hook.ENABLE_BINDABLE.addThread(Hook.propertyJ(true))) {
- CalciteAssert.that()
- .doWithConnection(connection -> {
- final String values =
- "select * from (values (1), (2), (3), (4)) as t(x)\n";
- checkPreparedFetch(connection,
- values + "fetch next (? + 1) rows only",
- 2, "X=1\nX=2\nX=3\n");
- checkPreparedFetchRepeated(connection,
- values + "fetch next (? + 1) rows only",
- new int[] {0, 2, 3},
- new String[] {"X=1\n", "X=1\nX=2\nX=3\n",
- "X=1\nX=2\nX=3\nX=4\n"});
- checkPreparedBigDecimalParameter(connection,
- values + "fetch next (cast(? as decimal(20, 0))) rows only",
- new BigDecimal("9223372036854775808"),
- "X=1\nX=2\nX=3\nX=4\n");
- });
- }
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testBindablePreparedOffset() throws Exception {
- try (Hook.Closeable ignored = Hook.ENABLE_BINDABLE.addThread(Hook.propertyJ(true))) {
- CalciteAssert.that()
- .doWithConnection(connection -> {
- final String values =
- "select * from (values (1), (2), (3), (4)) as t(x)\n";
- final String offset = values + "offset ? rows";
- checkPreparedBigDecimalParameter(connection, offset,
- new BigDecimal("1.5"),
- "X=3\nX=4\n");
- checkPreparedBigDecimalParameter(connection, offset,
- BigDecimal.valueOf(Integer.MAX_VALUE).add(BigDecimal.ONE), "");
-
- final String sortedOffset = values + "order by x desc offset ? rows";
- checkPreparedBigDecimalParameter(connection, sortedOffset,
- new BigDecimal("1.5"),
- "X=2\nX=1\n");
- checkPreparedParameterFails(connection, offset, -1,
- "OFFSET must not be negative");
- checkPreparedParameterNullFails(connection, offset,
- "OFFSET expression evaluated to NULL");
- });
- }
- }
-
- private static void checkPreparedFetch(Connection connection, String sql,
- int value, String expected) {
- try (PreparedStatement p = connection.prepareStatement(sql)) {
- p.setInt(1, value);
- try (ResultSet r = p.executeQuery()) {
- assertThat(CalciteAssert.toString(r), is(expected));
- }
- } catch (SQLException e) {
- throw TestUtil.rethrow(e);
- }
- }
-
- private static void checkPreparedBigDecimalParameter(Connection connection, String sql,
- BigDecimal value, String expected) {
- try (PreparedStatement p = connection.prepareStatement(sql)) {
- p.setBigDecimal(1, value);
- try (ResultSet r = p.executeQuery()) {
- assertThat(CalciteAssert.toString(r), is(expected));
- }
- } catch (SQLException e) {
- throw TestUtil.rethrow(e);
- }
- }
-
- private static void checkPreparedFetchRepeated(Connection connection, String sql,
- int[] values, String[] expected) {
- try (PreparedStatement p = connection.prepareStatement(sql)) {
- for (int i = 0; i < values.length; i++) {
- p.setInt(1, values[i]);
- try (ResultSet r = p.executeQuery()) {
- assertThat(CalciteAssert.toString(r), is(expected[i]));
- }
- }
- } catch (SQLException e) {
- throw TestUtil.rethrow(e);
- }
- }
-
- private static void checkPreparedParameterFails(Connection connection, String sql,
- long value, String expectedMessage) {
- try (PreparedStatement p = connection.prepareStatement(sql)) {
- if (value >= Integer.MIN_VALUE && value <= Integer.MAX_VALUE) {
- p.setInt(1, (int) value);
- } else {
- p.setLong(1, value);
- }
- final SQLException e =
- assertThrows(SQLException.class, p::executeQuery);
- assertThat(e.getMessage(), containsString(expectedMessage));
- } catch (SQLException e) {
- throw TestUtil.rethrow(e);
- }
- }
-
- private static void checkPreparedParameterNullFails(Connection connection, String sql,
- String expectedMessage) {
- try (PreparedStatement p = connection.prepareStatement(sql)) {
- p.setNull(1, Types.INTEGER);
- final SQLException e =
- assertThrows(SQLException.class, p::executeQuery);
- assertThat(e.getMessage(), containsString(expectedMessage));
- } catch (SQLException e) {
- throw TestUtil.rethrow(e);
- }
- }
-
private void checkPreparedOffsetFetch(final int offset, final int fetch,
final Matcher<? super ResultSet> matcher) throws Exception {
CalciteAssert.hr()
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 7ad9a4d733..493df7c30b 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -52,10 +52,7 @@
import org.apache.calcite.rex.RexCorrelVariable;
import org.apache.calcite.rex.RexFieldCollation;
import org.apache.calcite.rex.RexInputRef;
-import org.apache.calcite.rex.RexLambdaRef;
import org.apache.calcite.rex.RexNode;
-import org.apache.calcite.rex.RexNodeAndFieldIndex;
-import org.apache.calcite.rex.RexSubQuery;
import org.apache.calcite.rex.RexWindowBounds;
import org.apache.calcite.runtime.CalciteException;
import org.apache.calcite.schema.SchemaPlus;
@@ -5650,108 +5647,6 @@ private static RelNode buildCorrelateWithJoin(JoinRelType type, RelBuilder build
assertThat(mq.getMaxRowCount(planAfter), is(Double.POSITIVE_INFINITY));
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionCannotReferenceInputField() {
- final RelBuilder builder = RelBuilder.create(config().build());
- builder.scan("DEPT");
- final RexNode field = builder.field("DEPTNO");
-
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, field, ImmutableList.of()));
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null,
- builder.call(SqlStdOperatorTable.PLUS, builder.literal(1), field),
- ImmutableList.of()));
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null,
- new RexNodeAndFieldIndex(0, 0, "DEPTNO", field.getType()),
- ImmutableList.of()));
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionMustHaveNumericType() {
- final RelBuilder builder = RelBuilder.create(config().build());
- builder.scan("DEPT");
-
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, builder.literal("x"), ImmutableList.of()));
- builder.sortLimit(null, builder.literal(new BigDecimal("1.5")),
- ImmutableList.of());
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionAllowsScalarCallAndDynamicParameter() {
- final RelBuilder builder = RelBuilder.create(config().build());
- final RelDataType intType =
- builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER);
- builder.scan("DEPT")
- .sortLimit(null,
- builder.call(SqlStdOperatorTable.PLUS,
- builder.getRexBuilder().makeDynamicParam(intType, 0),
- builder.literal(1)),
- ImmutableList.of());
-
- assertThat(
- builder.build(), hasTree("LogicalSort(fetch=[+(?0, 1)])\n"
- + " LogicalTableScan(table=[[scott, DEPT]])\n"));
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionCannotContainAggregateWindowOrSubQuery() {
- final RelBuilder builder = RelBuilder.create(config().build());
- final RelDataType intType =
- builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER);
- builder.scan("DEPT");
- final RexNode aggregate =
- builder.call(SqlStdOperatorTable.SUM, builder.literal(1));
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, aggregate, ImmutableList.of()));
-
- final RexNode over =
- builder.getRexBuilder().makeOver(intType,
- SqlStdOperatorTable.ROW_NUMBER, ImmutableList.of(),
- ImmutableList.of(), ImmutableList.of(),
- RexWindowBounds.UNBOUNDED_PRECEDING,
- RexWindowBounds.UNBOUNDED_FOLLOWING,
- true, true, false, false, false);
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, over, ImmutableList.of()));
-
- final RelBuilder subQueryBuilder = RelBuilder.create(config().build());
- final RexNode subQuery =
- RexSubQuery.scalar(subQueryBuilder.values(new String[] {"N"}, 1).build());
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, subQuery, ImmutableList.of()));
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionCannotContainLambda() {
- final RelBuilder builder = RelBuilder.create(config().build());
- final RelDataType intType =
- builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER);
- builder.scan("DEPT");
- final RexLambdaRef lambdaRef = new RexLambdaRef(0, "x", intType);
- final RexNode lambda =
- builder.getRexBuilder().makeLambdaCall(
- builder.call(SqlStdOperatorTable.PLUS, lambdaRef, builder.literal(1)),
- ImmutableList.of(lambdaRef));
-
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, lambda, ImmutableList.of()));
- assertThrows(IllegalArgumentException.class,
- () -> builder.sortLimit(null, lambdaRef, ImmutableList.of()));
- }
-
@Test void testAdoptConventionEnumerable() {
final RelBuilder builder = RelBuilder.create(config().build());
RelNode root = builder
diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
index 460e066051..9c3c30e344 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -1467,7 +1467,7 @@ void testColumnOriginsUnion() {
@Test void testRowCountSortLimitBeyondLong() {
final BigDecimal fetch = BigDecimal.valueOf(Long.MAX_VALUE).add(BigDecimal.ONE);
final double fetchDouble = fetch.doubleValue();
- final String sql = "select * from emp order by ename limit " + fetch.toPlainString();
+ final String sql = "select * from emp order by ename limit " + fetchDouble;
final RelMetadataFixture fixture = sql(sql);
fixture.assertThatRowCount(is(EMP_SIZE), is(0D), is(fetchDouble));
}
@@ -1496,37 +1496,6 @@ void testColumnOriginsUnion() {
fixture.assertThatRowCount(is(1d), is(0D), is(0d));
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testMinRowCountFetchExpression() {
- final String sql = "select * from (values (1), (2)) as t(x)\n"
- + "fetch next (2 - 2) rows only";
- final RelMetadataFixture fixture = sql(sql);
- fixture.assertThatRowCount(is(2D), is(0D), is(2D));
-
- fixture
- .withCluster(cluster -> {
- final RelOptPlanner planner = new VolcanoPlanner();
- planner.addRule(EnumerableRules.ENUMERABLE_VALUES_RULE);
- planner.addRule(EnumerableRules.ENUMERABLE_PROJECT_RULE);
- planner.addRule(EnumerableRules.ENUMERABLE_LIMIT_RULE);
- planner.addRelTraitDef(ConventionTraitDef.INSTANCE);
- return RelOptCluster.create(planner, cluster.getRexBuilder());
- })
- .withRelTransform(rel -> {
- final RelOptPlanner planner = rel.getCluster().getPlanner();
- planner.setRoot(rel);
- final RelTraitSet requiredOutputTraits =
- rel.getCluster().traitSet().replace(EnumerableConvention.INSTANCE);
- final RelNode root = planner.changeTraits(rel, requiredOutputTraits);
- planner.setRoot(root);
- return planner.findBestExp();
- })
- .assertThatRel(is(instanceOf(EnumerableLimit.class)))
- .assertThatRowCount(is(2D), is(0D), is(2D));
- }
-
@Test void testRowCountSortLimitOffset() {
final String sql = "select * from emp order by ename limit 10 offset 5";
/* 14 - 5 */
diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
index 52c1875e36..60a5c56cba 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1738,34 +1738,6 @@ private void checkJoinProjectTransposeDoesNotMatch(JoinRelType type) {
.check();
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testSortUnionTransposeWithNonDeterministicFetch() {
- final String sql = "select a.name from dept a\n"
- + "union all\n"
- + "select b.name from dept b\n"
- + "order by name fetch next (rand_integer(10)) rows only";
- sql(sql)
- .withPreRule(CoreRules.PROJECT_SET_OP_TRANSPOSE)
- .withRule(CoreRules.SORT_UNION_TRANSPOSE)
- .checkUnchanged();
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testSortUnionTransposePushesParameterizedFetchExpression() {
- final String sql = "select a.name from dept a\n"
- + "union all\n"
- + "select b.name from dept b\n"
- + "order by name fetch next (? + 1) rows only";
- sql(sql)
- .withPreRule(CoreRules.PROJECT_SET_OP_TRANSPOSE)
- .withRule(CoreRules.SORT_UNION_TRANSPOSE)
- .check();
- }
-
@Test void testSortRemovalAllKeysConstant() {
final String sql = "select count(*) as c\n"
+ "from sales.emp\n"
@@ -6009,9 +5981,10 @@ private void checkEmptyJoin(RelOptFixture f) {
}
/** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testSortWithDynamicParamPushesOnce() {
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-6647">[CALCITE-6647]
+ * SortUnionTransposeRule should not push SORT past a UNION when SORT's fetch is DynamicParam
+ </a>. */
+ @Test void testSortWithDynamicParam() {
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleClass(SortProjectTransposeRule.class);
builder.addRuleClass(SortUnionTransposeRule.class);
@@ -9741,19 +9714,6 @@ private void checkSemiJoinRuleOnAntiJoin(RelOptRule rule) {
.check();
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testDecorrelateProjectWithFetchExpression() {
- final String query = "SELECT name, "
- + "(SELECT sal FROM emp where dept.deptno = emp.deptno order by sal "
- + "fetch next (1 + 0) rows only) "
- + "FROM dept";
- sql(query).withRule(CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE)
- .withLateDecorrelate(true)
- .check();
- }
-
/** Test case for <a href="https://issues.apache.org/jira/browse/CALCITE-7289">[CALCITE-7289]
* Select NULL subquery throwing exception</a>. */
@Test void testNullSelect() {
@@ -12242,39 +12202,6 @@ private static RelNode applyAggregateRemoveLiteralAggRule(RelNode rel) {
.check();
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testNondeterministicFetchPreventsDecorrelation() {
- checkNondeterministicFetchPreventsDecorrelation(false);
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testNondeterministicFetchPreventsTopDownDecorrelation() {
- checkNondeterministicFetchPreventsDecorrelation(true);
- }
-
- private void checkNondeterministicFetchPreventsDecorrelation(boolean enableTopDown) {
- final String sql = "select t.deptno, e.ename\n"
- + "from (select distinct deptno from emp) t,\n"
- + "lateral (select ename from emp\n"
- + " where emp.deptno = t.deptno\n"
- + " order by sal\n"
- + " fetch next (rand_integer(2) + 1) rows only) e";
-
- final RelOptFixture fixture = sql(sql)
- .withRule() // empty program
- .withLateDecorrelate(true)
- .withTopDownGeneralDecorrelate(enableTopDown);
- if (enableTopDown) {
- fixture.check();
- } else {
- fixture.checkUnchanged();
- }
- }
-
@Test void testTopDownGeneralDecorrelateForFilterSome() {
final String sql = "select empno from emp where "
+ "empno > SOME(select empno from emp_b where emp.ename = emp_b.ename)";
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index 6ce401502c..6b3255e653 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1263,15 +1263,6 @@ public static void checkActualAndReferenceFiles() {
sql(sql).ok();
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchWithExpression() {
- final String sql =
- "select empno from emp fetch next (1 + abs(-2)) rows only";
- sql(sql).ok();
- }
-
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-439">[CALCITE-439]
* SqlValidatorUtil.uniquify() may not terminate under some conditions</a>. */
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 7bfc58eafe..8f95099ab9 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -1527,7 +1527,7 @@ void testLikeAndSimilarFails() {
expr("cast(ARRAY[1,2,3] AS VARIANT ARRAY)")
.columnType("VARIANT NOT NULL ARRAY NOT NULL");
expr("cast(MAP['a','b','c','d'] AS MAP<VARCHAR, VARIANT>)")
- .columnType("(VARCHAR NOT NULL, VARIANT) MAP NOT NULL");
+ .columnType("(VARCHAR NOT NULL, VARIANT NOT NULL) MAP NOT NULL");
// Test case for [CALCITE-7293] https://issues.apache.org/jira/browse/CALCITE-7293
// MAP constructor cannot handle VARIANT values that need casts
expr("MAP['a', CAST('x' AS VARIANT), 'b', CAST(NULL AS VARIANT)]")
@@ -9656,16 +9656,16 @@ void testGroupExpressionEquivalenceParams() {
@Test void testCastMapType() {
sql("select cast(\"int2IntMapType\" as map<int,int>) from COMPLEXTYPES.CTC_T1")
.withExtendedCatalog()
- .columnType("(INTEGER NOT NULL, INTEGER) MAP NOT NULL");
+ .columnType("(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
sql("select cast(\"int2varcharArrayMapType\" as map<int,varchar array>) "
+ "from COMPLEXTYPES.CTC_T1")
.withExtendedCatalog()
- .columnType("(INTEGER NOT NULL, VARCHAR ARRAY) MAP NOT NULL");
+ .columnType("(INTEGER NOT NULL, VARCHAR NOT NULL ARRAY NOT NULL) MAP NOT NULL");
sql("select cast(\"varcharMultiset2IntIntMapType\" as map<varchar(5) multiset, map<int, int>>)"
+ " from COMPLEXTYPES.CTC_T1")
.withExtendedCatalog()
- .columnType("(VARCHAR(5) MULTISET NOT NULL, "
- + "(INTEGER NOT NULL, INTEGER) MAP) MAP NOT NULL");
+ .columnType("(VARCHAR(5) NOT NULL MULTISET NOT NULL, "
+ + "(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL) MAP NOT NULL");
}
@Test void testCastAsRowType() {
@@ -10723,22 +10723,6 @@ void testGroupExpressionEquivalenceParams() {
.rewritesTo(expected);
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionType() {
- sql("select name from dept fetch next (^upper('x')^) rows only")
- .fails("FETCH expression must have a numeric type; "
- + "actual type is 'CHAR\\(1\\) NOT NULL'");
- sql("select name from dept fetch next (^'x'^) rows only")
- .fails("FETCH expression must have a numeric type; "
- + "actual type is 'CHAR\\(1\\) NOT NULL'");
- sql("select name from dept fetch next 1.5 rows only").ok();
- sql("select name from dept "
- + "fetch next (^row_number() over ()^) rows only")
- .fails("Windowed aggregate expression is illegal in FETCH clause");
- }
-
@Test void testRewriteWithOffsetWithoutOrderBy() {
final String sql = "select name from dept offset 2";
final String expected = "SELECT `NAME`\n"
diff --git a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java
index 44055f7074..68bb56cf36 100644
--- a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java
+++ b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java
@@ -78,36 +78,6 @@ class EnumerableMergeUnionTest {
"empid=45; name=Pascal");
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void mergeUnionDoesNotPushNonDeterministicFetch() {
- tester(false,
- new HrSchemaBig(),
- "select * from (select empid, name from emps "
- + "union all select empid, name from emps) "
- + "order by empid fetch next (rand_integer(10)) rows only")
- .explainContains("EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
- + "fetch=[RAND_INTEGER(10)])\n"
- + " EnumerableMergeUnion(all=[true])\n"
- + " EnumerableSort(sort0=[$0], dir0=[ASC])\n");
- }
-
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void mergeUnionPushesParameterizedFetchExpression() {
- tester(false,
- new HrSchemaBig(),
- "select * from (select empid, name from emps "
- + "union all select empid, name from emps) "
- + "order by empid fetch next (? + 1) rows only")
- .explainContains("EnumerableLimit(fetch=[+(?0, 1)])\n"
- + " EnumerableMergeUnion(all=[true])\n"
- + " EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
- + "fetch=[+(?0, 1)])\n");
- }
-
@Test void mergeUnionAllOrderByName() {
tester(false,
new HrSchemaBig(),
diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
index e2b725a7d6..1e37b2699b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2950,46 +2950,6 @@ LogicalProject(NAME=[$1])
LogicalFilter(condition=[<=($3, 1)])
LogicalProject(SAL=[$5], EXPR$1=[EXTRACT(FLAG(YEAR), $4)], DEPTNO=[$7], rn=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY EXTRACT(FLAG(YEAR), $4) NULLS LAST, $5 DESC NULLS FIRST)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testDecorrelateProjectWithFetchExpression">
- <Resource name="sql">
- <![CDATA[SELECT name, (SELECT sal FROM emp where dept.deptno = emp.deptno order by sal fetch next (1 + 0) rows only) FROM dept]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(variablesSet=[[$cor0]], NAME=[$1], EXPR$1=[$SCALAR_QUERY({
-LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(1, 0)])
- LogicalProject(SAL=[$5])
- LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-})])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- <Resource name="planMid">
- <![CDATA[
-LogicalProject(NAME=[$1], EXPR$1=[$2])
- LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])
- LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(1, 0)])
- LogicalProject(SAL=[$5])
- LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalProject(NAME=[$1], EXPR$1=[$3])
- LogicalJoin(condition=[=($0, $2)], joinType=[left])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])
- LogicalProject(DEPTNO=[$1], SAL=[$0])
- LogicalFilter(condition=[<=($2, 1)])
- LogicalProject(SAL=[$5], DEPTNO=[$7], rn=[ROW_NUMBER() OVER (PARTITION BY $7 ORDER BY $5 NULLS LAST)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -3137,9 +3097,9 @@ LogicalProject(NAME=[$1], EXPR$1=[$2])
</Resource>
<Resource name="planAfter">
<![CDATA[
-LogicalProject(NAME=[$1], EXPR$1=[$4])
- LogicalJoin(condition=[AND(=($0, $5), =($3, $6))], joinType=[left])
- LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$0], NAME0=[CAST($1):VARCHAR(20) NOT NULL])
+LogicalProject(NAME=[$1], EXPR$1=[$3])
+ LogicalJoin(condition=[AND(=($0, $4), =($2, $5))], joinType=[left])
+ LogicalProject(DEPTNO=[$0], NAME=[$1], NAME0=[CAST($1):VARCHAR(20) NOT NULL])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(SAL=[$0], DEPTNO=[$2], ENAME=[$3])
LogicalFilter(condition=[<=($4, 1)])
@@ -8855,19 +8815,19 @@ where n1.SAL IN (
<Resource name="planBefore">
<![CDATA[
LogicalProject(SAL=[$5])
- LogicalJoin(condition=[AND(=($5, $11), IS NOT DISTINCT FROM($9, $12))], joinType=[inner])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], SAL0=[$5], $f9=[=($5, 4)])
+ LogicalJoin(condition=[AND(=($5, $10), IS NOT DISTINCT FROM($8, $11))], joinType=[inner])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8], $f8=[=($5, 4)])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalFilter(condition=[=($1, $0)])
LogicalAggregate(group=[{0, 1, 2}])
- LogicalProject(SAL=[$5], SAL0=[$8], $f9=[$9])
+ LogicalProject(SAL=[$5], SAL0=[$8], $f8=[$9])
LogicalJoin(condition=[OR(=($8, $5), $9)], joinType=[inner])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], SLACKER=[$8])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalAggregate(group=[{0, 1}])
- LogicalProject(SAL=[$5], $f9=[=($5, 4)])
+ LogicalProject(SAL=[$5], $f8=[=($5, 4)])
LogicalFilter(condition=[AND(=($7, 20), >($5, 1000))])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
]]>
@@ -9252,9 +9212,9 @@ LEFT JOIN LATERAL (
<![CDATA[
LogicalProject(A=[$0], TS=[$1], X=[$2], X0=[$4])
LogicalJoin(condition=[=($3, $5)], joinType=[left])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1], X=[$4], EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
- LogicalJoin(condition=[AND(=($1, $5), =($2, $6))], joinType=[left])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20) NOT NULL], EXPR$10=[$1])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], X=[$3], EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
+ LogicalJoin(condition=[AND(=($1, $4), =($2, $5))], joinType=[left])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$00=[CAST($0):VARCHAR(20) NOT NULL])
LogicalValues(tuples=[[{ 'a', 1 }]])
LogicalFilter(condition=[<=($3, 1)])
LogicalProject(X=[$2], EXPR$1=[$3], EXPR$00=[$4], rn=[ROW_NUMBER() OVER (PARTITION BY $3, $4)])
@@ -11325,95 +11285,6 @@ LogicalProject(USER=[USER])
LogicalAggregate(group=[{0}], EXPR$1=[SUM($1)])
LogicalProject(NAME=[$1], DEPTNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testNondeterministicFetchPreventsDecorrelation">
- <Resource name="sql">
- <![CDATA[select t.deptno, e.ename
-from (select distinct deptno from emp) t,
-lateral (select ename from emp
- where emp.deptno = t.deptno
- order by sal
- fetch next (rand_integer(2) + 1) rows only) e]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(DEPTNO=[$0], ENAME=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0])
- LogicalSort(sort0=[$1], dir0=[ASC], fetch=[+(RAND_INTEGER(2), 1)])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- <Resource name="planMid">
- <![CDATA[
-LogicalProject(DEPTNO=[$0], ENAME=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0])
- LogicalSort(sort0=[$1], dir0=[ASC], fetch=[+(RAND_INTEGER(2), 1)])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testNondeterministicFetchPreventsTopDownDecorrelation">
- <Resource name="sql">
- <![CDATA[select t.deptno, e.ename
-from (select distinct deptno from emp) t,
-lateral (select ename from emp
- where emp.deptno = t.deptno
- order by sal
- fetch next (rand_integer(2) + 1) rows only) e]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalProject(DEPTNO=[$0], ENAME=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0])
- LogicalSort(sort0=[$1], dir0=[ASC], fetch=[+(RAND_INTEGER(2), 1)])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- <Resource name="planMid">
- <![CDATA[
-LogicalProject(DEPTNO=[$0], ENAME=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0])
- LogicalSort(sort0=[$1], dir0=[ASC], fetch=[+(RAND_INTEGER(2), 1)])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{0}])
- LogicalAggregate(group=[{0}])
- LogicalProject(DEPTNO=[$7])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(ENAME=[$0])
- LogicalSort(sort0=[$1], dir0=[ASC], fetch=[+(RAND_INTEGER(2), 1)])
- LogicalProject(ENAME=[$1], SAL=[$5])
- LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -20032,55 +19903,7 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
]]>
</Resource>
</TestCase>
- <TestCase name="testSortUnionTransposePushesParameterizedFetchExpression">
- <Resource name="sql">
- <![CDATA[select a.name from dept a
-union all
-select b.name from dept b
-order by name fetch next (? + 1) rows only]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
- LogicalUnion(all=[true])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- <Resource name="planAfter">
- <![CDATA[
-LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
- LogicalUnion(all=[true])
- LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testSortUnionTransposeWithNonDeterministicFetch">
- <Resource name="sql">
- <![CDATA[select a.name from dept a
-union all
-select b.name from dept b
-order by name fetch next (rand_integer(10)) rows only]]>
- </Resource>
- <Resource name="planBefore">
- <![CDATA[
-LogicalSort(sort0=[$0], dir0=[ASC], fetch=[RAND_INTEGER(10)])
- LogicalUnion(all=[true])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testSortWithDynamicParamPushesOnce">
+ <TestCase name="testSortWithDynamicParam">
<Resource name="sql">
<![CDATA[SELECT x.sal
FROM (SELECT emp1.sal
@@ -20114,16 +19937,14 @@ LogicalSort(fetch=[?2])
LogicalProject(SAL=[$0])
LogicalSort(fetch=[?2])
LogicalUnion(all=[true])
- LogicalSort(fetch=[?2])
- LogicalProject(SAL=[$0])
- LogicalProject(SAL=[$5])
- LogicalSort(fetch=[?0])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalSort(fetch=[?2])
- LogicalProject(SAL=[$0])
- LogicalProject(SAL=[$5])
- LogicalSort(fetch=[?1])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(SAL=[$0])
+ LogicalProject(SAL=[$5])
+ LogicalSort(fetch=[?0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(SAL=[$0])
+ LogicalProject(SAL=[$5])
+ LogicalSort(fetch=[?1])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 1aa3c3f661..aafc9c11ef 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1770,15 +1770,15 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()], EXPR$1=[SUM($0)])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], C0=[$4], F=[$5])
- LogicalJoin(condition=[AND(=($0, $6), =($3, $7))], joinType=[inner])
- LogicalProject(C=[$0], D=[$1], C0=[$0], $f3=[+($0, $1)])
+LogicalProject(C=[$0], D=[$1], C0=[$3], F=[$4])
+ LogicalJoin(condition=[AND(=($0, $5), =($2, $6))], joinType=[inner])
+ LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalProject(C=[$1], F=[*($0, $1)], C2=[$1], $f3=[$2])
+ LogicalProject(C=[$1], F=[*($0, $1)], C2=[$1], $f2=[$2])
LogicalJoin(condition=[=($2, *($0, $1))], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
LogicalAggregate(group=[{0, 1}])
- LogicalProject(C=[$0], $f3=[+($0, $1)])
+ LogicalProject(C=[$0], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
]]>
</Resource>
@@ -1796,18 +1796,18 @@ cross join lateral
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$3])
- LogicalJoin(condition=[AND(=($0, $4), =($2, $5))], joinType=[inner])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+LogicalProject(C=[$0], N=[$2])
+ LogicalJoin(condition=[AND(=($0, $3), =($1, $4))], joinType=[inner])
+ LogicalProject(C=[$0], $f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
- LogicalProject(N=[+($0, $1)], EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalProject(N=[+($0, $1)], EXPR$0=[+($0, $1)], $f1=[$2])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalProject(EXPR$0=[$1], $f1=[$1])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 3 }]])
LogicalAggregate(group=[{0}])
- LogicalProject($f2=[+($0, 1)])
+ LogicalProject($f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
@@ -2601,18 +2601,6 @@ LogicalSort(fetch=[5])
LogicalSort(fetch=[?0])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-]]>
- </Resource>
- </TestCase>
- <TestCase name="testFetchWithExpression">
- <Resource name="sql">
- <![CDATA[select empno from emp fetch next (1 + abs(-2)) rows only]]>
- </Resource>
- <Resource name="plan">
- <![CDATA[
-LogicalSort(fetch=[+(1, ABS(-2))])
- LogicalProject(EMPNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
@@ -5234,9 +5222,9 @@ LogicalProject(C=[$0], D=[$1], C0=[$2])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], D0=[$4], C0=[$5])
- LogicalJoin(condition=[AND(=($0, $6), =($2, $5))], joinType=[left])
- LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)], C0=[$0])
+LogicalProject(C=[$0], D=[$1], D0=[$3], C0=[$4])
+ LogicalJoin(condition=[AND(=($0, $5), =($2, $4))], joinType=[left])
+ LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
LogicalJoin(condition=[true], joinType=[inner])
@@ -5259,18 +5247,18 @@ as r(n) on c=n]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$3])
- LogicalJoin(condition=[AND(=($0, $3), =($2, $4))], joinType=[left])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+LogicalProject(C=[$0], N=[$2])
+ LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[left])
+ LogicalProject(C=[$0], $f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
- LogicalProject(EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalProject(EXPR$0=[+($0, $1)], $f1=[$2])
LogicalJoin(condition=[=($0, $1)], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalProject(EXPR$0=[$1], $f1=[$1])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 3 }]])
LogicalAggregate(group=[{0}])
- LogicalProject($f2=[+($0, 1)])
+ LogicalProject($f1=[+($0, 1)])
LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/fetch.iq b/core/src/test/resources/sql/fetch.iq
deleted file mode 100644
index 8f4b0dd53d..0000000000
--- a/core/src/test/resources/sql/fetch.iq
+++ /dev/null
@@ -1,183 +0,0 @@
-# fetch.iq
-#
-# 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.
-#
-
-!use post
-!set outputformat mysql
-
-# FETCH accepts a parenthesized arithmetic expression.
-select *
-from (values (1), (2), (3), (4)) as t(x)
-fetch next (1 + abs(-2)) rows only;
-+---+
-| X |
-+---+
-| 1 |
-| 2 |
-| 3 |
-+---+
-(3 rows)
-
-!ok
-
-# FETCH accepts a parenthesized scalar expression.
-select *
-from (values (1), (2), (3), (4)) as t(x)
-fetch next (abs(2)) rows only;
-+---+
-| X |
-+---+
-| 1 |
-| 2 |
-+---+
-(2 rows)
-
-!ok
-
-# FETCH values are not restricted to the BIGINT range.
-select *
-from (values (1), (2), (3), (4)) as t(x)
-fetch next (cast(9223372036854775808 as decimal(20, 0)) + 1) rows only;
-+---+
-| X |
-+---+
-| 1 |
-| 2 |
-| 3 |
-| 4 |
-+---+
-(4 rows)
-
-!ok
-
-# FETCH expression cannot be negative.
-select *
-from (values (1), (2), (3)) as t(x)
-fetch next (0 - 1) rows only;
-FETCH must not be negative
-!error
-
-# FETCH expression cannot evaluate to NULL.
-select *
-from (values (1), (2), (3)) as t(x)
-fetch next (cast(null as integer)) rows only;
-FETCH expression evaluated to NULL
-!error
-
-# FETCH expression may have a fractional numeric type.
-select *
-from (values (1), (2), (3)) as t(x)
-fetch next (1.5) rows only;
-+---+
-| X |
-+---+
-| 1 |
-| 2 |
-+---+
-(2 rows)
-
-!ok
-
-# FETCH expression cannot reference input columns.
-select *
-from (values (1), (2), (3)) as t(x)
-fetch next (x) rows only;
-FETCH expression cannot reference table column 'X'
-!error
-
-# Expressions without parentheses are not allowed in FETCH.
-select *
-from (values (1), (2), (3)) as t(x)
-fetch next 1 + 2 rows only;
-Encountered "+"
-!error
-
-# FETCH expression works with a table source.
-select deptno, dname
-from dept
-order by deptno
-fetch next (1 + 1) rows only;
-+--------+-------------+
-| DEPTNO | DNAME |
-+--------+-------------+
-| 10 | Sales |
-| 20 | Marketing |
-+--------+-------------+
-(2 rows)
-
-!ok
-
-# FETCH expression works together with OFFSET on a table source.
-select deptno, dname
-from dept
-order by deptno
-offset 1 rows
-fetch next (1 + 1) rows only;
-+--------+-------------+
-| DEPTNO | DNAME |
-+--------+-------------+
-| 20 | Marketing |
-| 30 | Engineering |
-+--------+-------------+
-(2 rows)
-
-!ok
-
-# FETCH expression may contain a scalar function on a table source.
-select deptno
-from dept
-order by deptno
-fetch next (abs(-3)) rows only;
-+--------+
-| DEPTNO |
-+--------+
-| 10 |
-| 20 |
-| 30 |
-+--------+
-(3 rows)
-
-!ok
-
-# FETCH expression cannot reference columns of a table source.
-select deptno, dname
-from dept
-order by deptno
-fetch next (deptno) rows only;
-FETCH expression cannot reference table column 'DEPTNO'
-!error
-
-# FETCH expression cannot reference columns even inside a larger expression.
-select deptno, dname
-from dept
-order by deptno
-fetch next (deptno + 1) rows only;
-FETCH expression cannot reference table column 'DEPTNO'
-!error
-
-# FETCH expression may be zero on a table source.
-select deptno
-from dept
-order by deptno
-fetch next (2 - 2) rows only;
-+--------+
-| DEPTNO |
-+--------+
-+--------+
-(0 rows)
-
-!ok
diff --git a/core/src/test/resources/sql/lateral.iq b/core/src/test/resources/sql/lateral.iq
index 5c82727b89..4c4ffbe170 100644
--- a/core/src/test/resources/sql/lateral.iq
+++ b/core/src/test/resources/sql/lateral.iq
@@ -244,4 +244,98 @@ where job = 'MANAGER';
!ok
+# 3 test cases for [CALCITE-7646] CorrelateProjectExtractor
+# does not handle nested field accesses cor0.field0.field1.
+
+# All queries use LATERAL, which converts directly to a Correlate.
+# The results were validated on Postgres
+
+!use scott
+
+select t.dd, t.x
+from dept d,
+lateral (select d.deptno as dd, u.x
+ from unnest(array[d.deptno + 100]) as u(x)) as t
+where d.dname = 'SALES';
++----+-----+
+| DD | X |
++----+-----+
+| 30 | 130 |
++----+-----+
+(1 row)
+
+!ok
+
+select t.dd, t.dd1, t.x
+from dept d,
+lateral (select d.deptno as dd, d.deptno + 1 as dd1, u.x
+ from unnest(array[1, 2]) as u(x)) as t
+where d.dname = 'SALES';
++----+-----+---+
+| DD | DD1 | X |
++----+-----+---+
+| 30 | 31 | 1 |
+| 30 | 31 | 2 |
++----+-----+---+
+(2 rows)
+
+!ok
+!if (use_old_decorr) {
+# The correlated computation d.deptno + 1 (DD1) has been extracted into the left
+# input of the EnumerableNestedLoopJoin, as $f3. The right input,
+# UNNEST(ARRAY[1, 2]), references no correlation variable, so decorrelation
+# replaces the Correlate with a join.
+EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])
+ EnumerableHashJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[semi])
+ EnumerableCalc(expr#0..2=[{inputs}], proj#0..2=[{exprs}], DEPTNO=[$t0], $f3=[$t1])
+ EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t0, $t3)], expr#5=['SALES':VARCHAR(14)], expr#6=[=($t1, $t5)], DEPTNO=[$t0], $f3=[$t4], $condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableUncollect
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[2], expr#3=[ARRAY($t1, $t2)], EXPR$0=[$t3])
+ EnumerableValues(tuples=[[{ 0 }]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1], expr#4=[+($t0, $t3)], expr#5=['SALES':VARCHAR(14)], expr#6=[=($t1, $t5)], DEPTNO=[$t0], $f3=[$t4], $condition=[$t6])
+ EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+!}
+
+# COALESCE(d.path, ARRAY[CAST(NULL AS INTEGER)]) converts to
+# CASE(IS NOT NULL($cor0.PATH), $cor0.PATH, ARRAY(null:INTEGER)). The constant
+# ARRAY(null:INTEGER) operand must not prevent extracting the CASE to the
+# left input of the Correlate operator
+select d.deptno, t.x
+from (select deptno,
+ case when deptno = 10 then array[deptno, deptno + 1] end as path
+ from dept) as d,
+lateral (select * from unnest(coalesce(d.path, array[cast(null as integer)])) as u(x)) as t
+order by d.deptno, t.x;
++--------+----+
+| DEPTNO | X |
++--------+----+
+| 10 | 10 |
+| 10 | 11 |
+| 20 | |
+| 30 | |
+| 40 | |
++--------+----+
+(5 rows)
+
+!ok
+!if (use_old_decorr) {
+# The entire CASE produced by COALESCE has been extracted into the left input of the
+# EnumerableCorrelate, as $f2. The right input reads the array through $cor0.$f2.
+# The query cannot be decorrelated because of the remaining Uncollect.
+EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0], X=[$t2])
+ EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{1}])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):INTEGER NOT NULL], expr#4=[10], expr#5=[=($t3, $t4)], expr#6=[1], expr#7=[+($t0, $t6)], expr#8=[ARRAY($t3, $t7)], expr#9=[null:INTEGER NOT NULL ARRAY], expr#10=[CASE($t5, $t8, $t9)], expr#11=[IS NOT NULL($t10)], expr#12=[CAST($t10):INTEGER NOT NULL ARRAY NOT NULL], expr#13=[CAST($t12):INTEGER ARRAY NOT NULL], expr#14=[null:INTEGER], expr#15=[ARRAY($t14)], expr#16=[CASE($t11, $t13, $t15)], DEPTNO=[$t0], $f2=[$t16])
+ EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableUncollect
+ EnumerableCalc(expr#0=[{inputs}], expr#1=[$cor0], expr#2=[$t1.$f2], EXPR$0=[$t2])
+ EnumerableValues(tuples=[[{ 0 }]])
+!plan
+!}
+
+!set planner-rules original
+
# End lateral.iq
diff --git a/server/src/test/java/org/apache/calcite/test/ServerTest.java b/server/src/test/java/org/apache/calcite/test/ServerTest.java
index 39d434f23a..355d39de7d 100644
--- a/server/src/test/java/org/apache/calcite/test/ServerTest.java
+++ b/server/src/test/java/org/apache/calcite/test/ServerTest.java
@@ -43,7 +43,6 @@
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
-import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
@@ -453,42 +452,6 @@ static Connection connect() throws SQLException {
}
}
- /** Test case for
- * <a href="https://issues.apache.org/jira/browse/CALCITE-7592">[CALCITE-7592]
- * Add expression support for FETCH</a>. */
- @Test void testFetchExpressionCannotReferenceInputColumn() throws Exception {
- try (Connection c = connect();
- Statement s = c.createStatement()) {
- s.execute("create table person (id int not null, name varchar(20))");
- try (PreparedStatement p =
- c.prepareStatement("insert into person (id, name) values (?, ?)")) {
- p.setInt(1, 1);
- p.setString(2, "foo");
- assertThat(p.executeUpdate(), is(1));
- }
-
- SQLException e =
- assertThrows(
- SQLException.class, () -> s.executeQuery("select * from person "
- + "fetch next id rows only"));
- assertThat(e.getMessage(), containsString("Encountered \"id\""));
-
- e =
- assertThrows(
- SQLException.class, () -> s.executeQuery("select * from person "
- + "fetch next (id) rows only"));
- assertThat(e.getMessage(),
- containsString("FETCH expression cannot reference table column 'ID'"));
-
- e =
- assertThrows(
- SQLException.class, () -> s.executeQuery("select * from person "
- + "fetch next (1 + id) rows only"));
- assertThat(e.getMessage(),
- containsString("FETCH expression cannot reference table column 'ID'"));
- }
- }
-
/** Test case for
* <a href="https://issues.apache.org/jira/browse/CALCITE-6022">[CALCITE-6022]
* Support "CREATE TABLE ... LIKE" DDL in server module</a>. */
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index 56fbf8a86c..904ab04619 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -427,13 +427,8 @@ ## Grammar
"SELECT x, y FROM t ORDER BY x, y"
An optional trailing ASC / DESC and NULLS FIRST / NULLS LAST applies to all keys.
-In *query*, *start* may be either an unsigned numeric literal or a dynamic
-parameter whose value is numeric. The *count* in a LIMIT clause may be either
-an unsigned numeric literal or a dynamic parameter whose value is numeric. The
-*count* in a FETCH clause may be an unsigned numeric literal, a dynamic
-parameter whose value is numeric, or a scalar expression enclosed in
-parentheses. A FETCH *count* expression cannot reference columns from the query
-input, and cannot contain aggregate functions, window functions, or sub-queries.
+In *query*, *count* and *start* may each be either an unsigned numeric literal
+or a dynamic parameter whose value is numeric.
Support for decimal or non-integer values is adapter-dependent.
An aggregate query is a query that contains a GROUP BY or a HAVING
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 394aecf26f..4058d716bf 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
@@ -4104,31 +4104,12 @@ void checkPeriodPredicate(Checker checker) {
+ "FROM `FOO`\n"
+ "OFFSET ? ROWS\n"
+ "FETCH NEXT ? ROWS ONLY");
- // CALCITE-7592: Arithmetic and scalar expressions are allowed within parentheses.
- sql("select a from foo fetch next (1 + abs(-2)) rows only")
- .ok("SELECT `A`\n"
- + "FROM `FOO`\n"
- + "FETCH NEXT (1 + ABS(-2)) ROWS ONLY");
- // Expressions without parentheses are not allowed.
- sql("select a from foo fetch next 1 ^+^ 2 rows only")
- .fails("(?s).*Encountered \"\\+\" at .*");
- sql("select a from foo fetch next ? ^+^ abs(2) rows only")
- .fails("(?s).*Encountered \"\\+\" at .*");
// missing ROWS after FETCH
sql("select a from foo offset 1 fetch next 3 ^only^")
.fails("(?s).*Encountered \"only\" at .*");
// FETCH before OFFSET is illegal
sql("select a from foo fetch next 3 rows only ^offset^ 1")
.fails("(?s).*Encountered \"offset\" at .*");
- // Subqueries are not allowed in FETCH
- sql("select a from foo fetch next ^select^ 2 rows only")
- .fails("(?s).*Encountered \"select\" at .*");
- sql("select a from foo fetch next (^select^ 2) rows only")
- .fails("(?s).*Encountered \"select\" at .*");
- sql("select a from foo fetch next (^select^ ?) rows only")
- .fails("(?s).*Encountered \"select\" at .*");
- sql("select a from foo fetch next (^select^ max(a) from foo) rows only")
- .fails("(?s).*Encountered \"select\" at .*");
}
/**
diff --git a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
index 91f20af681..387915bca6 100644
--- a/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
+++ b/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java
@@ -1843,6 +1843,39 @@ void testCastToBoolean(CastType castType, SqlOperatorFixture f) {
f.checkNull("cast(null as row(f0 varchar, f1 varchar))");
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-7658">
+ * [CALCITE-7658] Type checker rejects
+ * CAST(ARRAY() AS ROW(x INT) ARRAY)</a>.
+ *
+ * <p>The Spark {@code ARRAY()} function creates an empty array whose
+ * element type is UNKNOWN; such an array can be cast to any array type. */
+ @Test void testCastEmptyArray() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.SPARK);
+ f.checkScalar("cast(array() as integer array)", "[]",
+ "INTEGER NOT NULL ARRAY NOT NULL");
+ f.checkScalar("cast(array() as row(x int) array)", "[]",
+ "RecordType(INTEGER NOT NULL X) NOT NULL ARRAY NOT NULL");
+ f.checkScalar("cast(array() as integer array array)", "[]",
+ "INTEGER ARRAY NOT NULL ARRAY NOT NULL");
+ f.checkScalar("cast(array() as map<varchar, integer> array)", "[]",
+ "(VARCHAR NOT NULL, INTEGER) MAP NOT NULL ARRAY NOT NULL");
+ // A non-empty array with UNKNOWN or NULL element type contains only nulls
+ f.checkScalar("cast(array_append(array(), null) as row(x int) array)",
+ "[null]",
+ "RecordType(INTEGER NOT NULL X) ARRAY NOT NULL");
+ f.checkScalar("cast(array(null) as row(x int) array)",
+ "[null]",
+ "RecordType(INTEGER NOT NULL X) ARRAY NOT NULL");
+ // The empty MAP() has UNKNOWN key and value types
+ f.checkScalar("cast(map() as map<varchar, integer>)", "{}",
+ "(VARCHAR NOT NULL, INTEGER NOT NULL) MAP NOT NULL");
+ f.checkScalar("cast(map() as map<varchar, row(x int)>)", "{}",
+ "(VARCHAR NOT NULL, RecordType(INTEGER X) NOT NULL) MAP NOT NULL");
+ f.checkScalar("cast(map() as map<varchar, integer array>)", "{}",
+ "(VARCHAR NOT NULL, INTEGER ARRAY NOT NULL) MAP NOT NULL");
+ }
+
/** Test cases for
* <a href="https://issues.apache.org/jira/browse/CALCITE-4918">
* [CALCITE-4918] Add a VARIANT data type</a>. */
@@ -8229,7 +8262,7 @@ void checkRegexpExtract(SqlOperatorFixture f0, FunctionAlias functionAlias) {
f.checkScalar("array_append(array(null), null)", "[null, null]",
"NULL ARRAY NOT NULL");
f.checkScalar("array_append(array(), null)", "[null]",
- "UNKNOWN ARRAY NOT NULL");
+ "NULL ARRAY NOT NULL");
f.checkScalar("array_append(array(), 1)", "[1]",
"INTEGER NOT NULL ARRAY NOT NULL");
f.checkScalar("array_append(array[array[1, 2]], array[3, 4])", "[[1, 2], [3, 4]]",
@@ -8568,7 +8601,7 @@ void checkRegexpExtract(SqlOperatorFixture f0, FunctionAlias functionAlias) {
f.checkScalar("array_prepend(array(null), null)", "[null, null]",
"NULL ARRAY NOT NULL");
f.checkScalar("array_prepend(array(), null)", "[null]",
- "UNKNOWN ARRAY NOT NULL");
+ "NULL ARRAY NOT NULL");
f.checkScalar("array_prepend(array(), 1)", "[1]",
"INTEGER NOT NULL ARRAY NOT NULL");
f.checkScalar("array_prepend(array[array[1, 2]], array[3, 4])", "[[3, 4], [1, 2]]",
@@ -13623,6 +13656,22 @@ private static void checkArrayConcatAggFuncFails(SqlOperatorFixture t) {
"RecordType(INTEGER EXPR$0, INTEGER EXPR$1) NOT NULL ARRAY NOT NULL");
f2.checkScalar("array(row(1, 2), row(3, 4))", "[{1, 2}, {3, 4}]",
"RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL ARRAY NOT NULL");
+ // Tests for unification of UNKNOWN with other types; array() has a type
+ // of UNKNOWN ARRAY, yet the type of ARRAY() is inferred from other operands.
+ f2.checkScalar("array(array(1), array())", "[[1], []]",
+ "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
+ f2.checkScalar("array(array(), array(1))", "[[], [1]]",
+ "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
+ f2.checkScalar("array(array(row(1, 2)), array())", "[[{1, 2}], []]",
+ "RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL "
+ + "ARRAY NOT NULL ARRAY NOT NULL");
+ f2.checkScalar("array(array(), array(row(1, 2)))", "[[], [{1, 2}]]",
+ "RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) NOT NULL "
+ + "ARRAY NOT NULL ARRAY NOT NULL");
+ f2.checkScalar("array(array(array(1)), array())", "[[[1]], []]",
+ "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
+ f2.checkScalar("array(array(), array(array(1)))", "[[], [[1]]]",
+ "INTEGER NOT NULL ARRAY NOT NULL ARRAY NOT NULL ARRAY NOT NULL");
// checkFails
f2.checkFails("^array(row(1), row(2, 3))^",
"Parameters must be of the same type", false);
@@ -13641,6 +13690,32 @@ private static void checkArrayConcatAggFuncFails(SqlOperatorFixture t) {
f.forEachLibrary(libraries, consumer);
}
+ /** Tests that empty collections created by the Spark
+ * {@code ARRAY()} and {@code MAP()} functions, whose element
+ * types are UNKNOWN, unify with collections with known types. */
+ @Test void testEmptyCollections() {
+ final SqlOperatorFixture f = fixture().withLibrary(SqlLibrary.SPARK);
+ f.checkScalar("array(map(1, 2), map())", "[{1=2}, {}]",
+ "(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array(map(), map(1, 2))", "[{}, {1=2}]",
+ "(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL ARRAY NOT NULL");
+ // Nested: empty collections inside a ROW unify field by field
+ f.checkScalar("array(row(array(), map()))", "[{[], {}}]",
+ "RecordType(UNKNOWN NOT NULL ARRAY NOT NULL EXPR$0, "
+ + "(UNKNOWN NOT NULL, UNKNOWN NOT NULL) MAP NOT NULL EXPR$1) "
+ + "NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array(row(array(1), map(1, 2)), row(array(), map()))",
+ "[{[1], {1=2}}, {[], {}}]",
+ "RecordType(INTEGER NOT NULL ARRAY NOT NULL EXPR$0, "
+ + "(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL EXPR$1) "
+ + "NOT NULL ARRAY NOT NULL");
+ f.checkScalar("array(row(array(), map()), row(array(1), map(1, 2)))",
+ "[{[], {}}, {[1], {1=2}}]",
+ "RecordType(INTEGER NOT NULL ARRAY NOT NULL EXPR$0, "
+ + "(INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL EXPR$1) "
+ + "NOT NULL ARRAY NOT NULL");
+ }
+
@Test void testArrayQueryConstructor() {
final SqlOperatorFixture f = fixture();
f.setFor(SqlStdOperatorTable.ARRAY_QUERY, SqlOperatorFixture.VmName.EXPAND);
@@ -13947,6 +14022,22 @@ private static void checkArrayConcatAggFuncFails(SqlOperatorFixture t) {
f1.checkScalar("map('k1', 1, 'k2', 2.0)",
"{k1=1.0, k2=2.0}",
"(CHAR(2) NOT NULL, DECIMAL(11, 1) NOT NULL) MAP NOT NULL");
+ f1.checkScalar("map('a', array(1), 'b', array())", "{a=[1], b=[]}",
+ "(CHAR(1) NOT NULL, INTEGER NOT NULL ARRAY NOT NULL) MAP NOT NULL");
+ f1.checkScalar("map('a', array(), 'b', array(1))", "{a=[], b=[1]}",
+ "(CHAR(1) NOT NULL, INTEGER NOT NULL ARRAY NOT NULL) MAP NOT NULL");
+ f1.checkScalar("map('a', map(1, 2), 'b', map())", "{a={1=2}, b={}}",
+ "(CHAR(1) NOT NULL, (INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL) MAP NOT NULL");
+ f1.checkScalar("map('a', map(), 'b', map(1, 2))", "{a={}, b={1=2}}",
+ "(CHAR(1) NOT NULL, (INTEGER NOT NULL, INTEGER NOT NULL) MAP NOT NULL) MAP NOT NULL");
+ // Avatica's conversion of MAP to STRING is broken, so we only check
+ // the type for the following 2 tests
+ f1.checkType("map('a', array(row(1, 2)), 'b', array())",
+ "(CHAR(1) NOT NULL, RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) "
+ + "NOT NULL ARRAY NOT NULL) MAP NOT NULL");
+ f1.checkType("map('a', array(), 'b', array(row(1, 2)))",
+ "(CHAR(1) NOT NULL, RecordType(INTEGER NOT NULL EXPR$0, INTEGER NOT NULL EXPR$1) "
+ + "NOT NULL ARRAY NOT NULL) MAP NOT NULL");
}
@Test void testMapQueryConstructor() {