This is an automated email from the ASF dual-hosted git repository.
xiedeyantu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new c0295dc6b4 [CALCITE-7592] Add expression support for FETCH
c0295dc6b4 is described below
commit c0295dc6b4380ecee0a3935a6f971ff4deab4c8d
Author: Kirill Tkalenko <[email protected]>
AuthorDate: Fri Jul 17 14:33:04 2026 +0300
[CALCITE-7592] Add expression support for FETCH
---
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 +-
.../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 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 29 ++
.../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 | 68 +++++
.../org/apache/calcite/rex/RexProgramTest.java | 30 ++
.../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 | 16 +
.../test/enumerable/EnumerableMergeUnionTest.java | 30 ++
.../org/apache/calcite/test/RelOptRulesTest.xml | 197 +++++++++++-
.../apache/calcite/test/SqlToRelConverterTest.xml | 12 +
core/src/test/resources/sql/fetch.iq | 183 +++++++++++
.../java/org/apache/calcite/test/ServerTest.java | 37 +++
site/_docs/reference.md | 9 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 19 ++
41 files changed, 1619 insertions(+), 93 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index 7246e60842..ce69124c4b 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 ] { ROW | ROWS } ONLY ]</pre>
+ * [ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY
]</pre>
* </blockquote>
*/
SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
@@ -796,10 +796,26 @@ void FetchClause(SqlNode[] offsetFetch) :
{
// SQL:2008-style syntax. "OFFSET ... FETCH ...".
// If you specify both LIMIT and FETCH, FETCH wins.
- <FETCH> ( <FIRST> | <NEXT> ) offsetFetch[1] =
UnsignedNumericLiteralOrParam()
+ <FETCH> ( <FIRST> | <NEXT> ) offsetFetch[1] = FetchCount()
( <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 d3da43466d..40a824536b 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(Object value, String kind) {
+ public static BigDecimal numberToBigDecimal(@Nullable Object value, String
kind) {
return numberToBigDecimal(value, kind, FetchOffsetRoundingPolicy.NONE);
}
@@ -124,8 +124,11 @@ public static BigDecimal numberToBigDecimal(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(Object value, String kind,
+ public static BigDecimal numberToBigDecimal(@Nullable 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 02fd54bdad..de1f94d562 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,13 +106,15 @@ 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", roundingPolicyExp)));
+ getExpression(offset, "OFFSET", implementor, builder,
+ roundingPolicyExp, false)));
}
if (fetch != null) {
v =
builder.append("fetch",
Expressions.call(BuiltInMethod.TAKE_BIG_DECIMAL.method, v,
- getExpression(fetch, "FETCH", roundingPolicyExp)));
+ getExpression(fetch, "FETCH", implementor, builder,
+ roundingPolicyExp, true)));
}
builder.add(Expressions.return_(null, v));
@@ -120,7 +122,8 @@ public static EnumerableLimit create(final RelNode input,
@Nullable RexNode offs
}
static Expression getExpression(RexNode rexNode, String kind,
- Expression roundingPolicy) {
+ EnumerableRelImplementor implementor, BlockBuilder builder,
+ Expression roundingPolicy, boolean translateExpression) {
final Expression value;
if (rexNode instanceof RexDynamicParam) {
final RexDynamicParam param = (RexDynamicParam) rexNode;
@@ -128,8 +131,17 @@ static Expression getExpression(RexNode rexNode, String
kind,
Expressions.call(DataContext.ROOT,
BuiltInMethod.DATA_CONTEXT_GET.method,
Expressions.constant("?" + param.getIndex()));
- } else {
+ } 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);
}
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 325fe687ba..97d9fd8169 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,14 +104,16 @@ public static EnumerableLimitSort create(
if (this.fetch == null) {
fetchVal = Expressions.constant(BigDecimal.valueOf(Integer.MAX_VALUE));
} else {
- fetchVal = getExpression(this.fetch, "FETCH", roundingPolicyExp);
+ fetchVal =
+ getExpression(this.fetch, "FETCH", implementor, builder,
roundingPolicyExp, true);
}
final Expression offsetVal;
if (this.offset == null) {
offsetVal = Expressions.constant(BigDecimal.ZERO);
} else {
- offsetVal = getExpression(this.offset, "OFFSET", roundingPolicyExp);
+ offsetVal =
+ getExpression(this.offset, "OFFSET", implementor, builder,
roundingPolicyExp, false);
}
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 7d47e639b7..57f864794a 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,6 +29,7 @@
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;
@@ -88,9 +89,13 @@ public EnumerableMergeUnionRule(Config config) {
// Push down sort limit, if possible.
RexNode inputFetch = null;
if (sort.fetch != null) {
- if (sort.offset == null) {
+ final boolean safeToReevaluate =
+ RexUtil.isDeterministic(sort.fetch);
+ if (sort.offset == null && safeToReevaluate) {
inputFetch = sort.fetch;
- } else if (sort.fetch instanceof RexLiteral && sort.offset instanceof
RexLiteral) {
+ } else if (safeToReevaluate
+ && 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/interpreter/SortNode.java
b/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
index 71d9f2b22e..0f393a3e68 100644
--- a/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
+++ b/core/src/main/java/org/apache/calcite/interpreter/SortNode.java
@@ -16,14 +16,22 @@
*/
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;
@@ -35,37 +43,57 @@
* {@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);
- }
-
- private static int getValueAsInt(RexNode node) {
- return requireNonNull(((RexLiteral) node).getValueAs(Integer.class),
- () -> "getValueAs(Integer.class) for " + node);
+ 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;
}
@Override public void run() throws InterruptedException {
- final int offset =
- rel.offset == null
- ? 0
- : getValueAsInt(rel.offset);
- final int fetch =
- rel.fetch == null
- ? -1
- : getValueAsInt(rel.fetch);
+ final BigDecimal offset = getOffset();
+ final @Nullable BigDecimal fetch = getFetch();
// In pure limit mode. No sort required.
Row row;
loop:
if (rel.getCollation().getFieldCollations().isEmpty()) {
- for (int i = 0; i < offset; i++) {
+ BigDecimal skipped = BigDecimal.ZERO;
+ while (skipped.compareTo(offset) < 0) {
row = source.receive();
if (row == null) {
break loop;
}
+ skipped = skipped.add(BigDecimal.ONE);
}
- if (fetch >= 0) {
- for (int i = 0; i < fetch && (row = source.receive()) != null; i++) {
+ if (fetch != null) {
+ BigDecimal fetched = BigDecimal.ZERO;
+ while (fetched.compareTo(fetch) < 0
+ && (row = source.receive()) != null) {
sink.send(row);
+ fetched = fetched.add(BigDecimal.ONE);
}
} else {
while ((row = source.receive()) != null) {
@@ -79,10 +107,15 @@ private static int getValueAsInt(RexNode node) {
list.add(row);
}
list.sort(comparator());
- final int end = fetch < 0 || offset + fetch > list.size()
+ 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
? list.size()
- : offset + fetch;
- for (int i = offset; i < end; i++) {
+ : start + rowCount(fetch);
+ for (int i = start; i < end; i++) {
sink.send(list.get(i));
}
}
@@ -116,4 +149,35 @@ 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 e728c22e1e..869f1ad50e 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,10 +117,12 @@ 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;
}
@@ -130,10 +132,12 @@ 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;
}
@@ -214,7 +218,8 @@ 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 869d343335..2cb710f398 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,10 +116,13 @@ 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, rowCount);
+ final double limit =
+ literalValueApproximatedByDouble(rel.fetch,
+ rel.fetch == null ? rowCount : 0D);
return limit < rowCount ? limit : rowCount;
}
@@ -129,10 +132,13 @@ 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, rowCount);
+ final double limit =
+ literalValueApproximatedByDouble(rel.fetch,
+ rel.fetch == null ? rowCount : 0D);
return limit < rowCount ? limit : rowCount;
}
@@ -174,7 +180,8 @@ 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 e83f4c1da9..3e7824e1aa 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,10 +165,12 @@ 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;
}
@@ -178,10 +180,12 @@ 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 1f65022436..5b09628938 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,6 +25,7 @@
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;
@@ -56,6 +57,7 @@
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;
@@ -483,6 +485,9 @@ 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");
@@ -1043,8 +1048,16 @@ 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 RexDynamicParam) {
+ if (rowCount == null || offset instanceof RexDynamicParam || !(fetch
instanceof RexLiteral)) {
// 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 8871f24d3e..718e6b1cb9 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,6 +59,7 @@
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;
@@ -1227,7 +1228,7 @@ public Result visit(Sort e) {
sqlSelect.setOffset(offset);
}
if (e.fetch != null) {
- SqlNode fetch = builder.context.toSql(null, e.fetch);
+ SqlNode fetch = toSqlFetch(e, builder.context);
sqlSelect.setFetch(fetch);
}
return result(sqlSelect, ImmutableList.of(Clause.ORDER_BY), e, null);
@@ -1285,13 +1286,20 @@ 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(builder.context.toSql(null, e.fetch));
+ builder.setFetch(toSqlFetch(e, builder.context));
}
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 037a4d6054..f69810a14d 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,7 +30,6 @@
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;
@@ -508,8 +507,7 @@ protected
ProjectSortMeasureRule(ProjectSortMeasureRuleConfig config) {
relBuilder.push(sort.getInput())
.projectPlus(map.keySet())
- .sortLimit(sort.offset == null ? 0 :
RexLiteral.numberValue(sort.offset),
- sort.fetch == null ? -1 : RexLiteral.numberValue(sort.fetch),
+ .sortLimit(sort.offset, 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 02b0bd8af1..95331c69a4 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
@@ -42,7 +42,6 @@
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;
@@ -536,9 +535,8 @@ 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 != null
- && !(sort.fetch instanceof RexDynamicParam)
- &&
RexLiteral.bigDecimalValue(sort.fetch).equals(BigDecimal.ZERO);
+ return sort.fetch instanceof RexLiteral
+ &&
BigDecimal.ZERO.equals(RexLiteral.bigDecimalValue(sort.fetch));
}
};
}
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 4310d6d655..df967e56aa 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);
- // Do nothing if SORT contains dynamic parameters in offset or fetch
+ // The pushed fetch is calculated from literal offset and fetch values.
if (sort.offset instanceof RexDynamicParam
- || sort.fetch instanceof RexDynamicParam) {
+ || sort.fetch != null && !(sort.fetch instanceof RexLiteral)) {
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 9bcf026fc6..08563cdcdb 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,6 +133,9 @@ 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 416825ee92..93b6af657c 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.RexDynamicParam;
+import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.tools.RelBuilderFactory;
import org.immutables.value.Value;
@@ -67,13 +67,14 @@ public SortUnionTransposeRule(
@Override public boolean matches(RelOptRuleCall call) {
final Sort sort = call.rel(0);
final Union union = call.rel(1);
- // We only apply this rule if Union.all is true, Sort.offset is null and
Sort.fetch is not
- // a dynamic param.
+ // Re-evaluating a non-deterministic FETCH in every branch can produce a
+ // different limit from the top Sort.
// There is a flag indicating if this rule should be applied when
// Sort.fetch is null.
return union.all
&& sort.offset == null
- && !(sort.fetch instanceof RexDynamicParam)
+ && (sort.fetch == null
+ || RexUtil.isDeterministic(sort.fetch))
&& (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 3604e98dfd..b592093a5a 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -19,6 +19,7 @@
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;
@@ -48,6 +49,7 @@
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;
@@ -63,9 +65,11 @@
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;
@@ -840,6 +844,82 @@ 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 c5047574a3..452c2bf84a 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -164,6 +164,15 @@ 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 e659d1d17e..164f212c6c 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1088,7 +1088,18 @@ protected static void unparseFetchUsingAnsi(SqlWriter
writer, @Nullable SqlNode
writer.startList(SqlWriter.FrameTypeEnum.FETCH);
writer.keyword("FETCH");
writer.keyword("NEXT");
- fetch.unparse(writer, -1, -1);
+ 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);
+ }
writer.keyword("ROWS");
writer.keyword("ONLY");
writer.endList(fetchFrame);
@@ -1098,13 +1109,32 @@ 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);
+ unparseLimit(writer, fetch, allowExpression);
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 82376ae576..f312764136 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);
+ unparseFetchUsingLimit(writer, offset, fetch, true);
}
@Override public void unparseCall(SqlWriter writer, SqlCall call,
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 7021696b67..0ea01a3514 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,6 +1771,34 @@ 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
@@ -4499,6 +4527,7 @@ 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/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 300ff959bc..4e4104ad48 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -575,6 +575,10 @@ 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) {
@@ -1137,8 +1141,31 @@ 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 == null) {
+ if (sort.offset != null || !(sort.fetch instanceof RexLiteral)) {
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 291eb619d0..4139cf4b2b 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/TopDownGeneralDecorrelator.java
@@ -233,12 +233,14 @@ public static RelNode decorrelateQuery(RelNode rel,
RelBuilder builder) {
RelNode preparedRel = prePlanner.findBestExp();
// start decorrelating
- TopDownGeneralDecorrelator decorrelator = createEmptyDecorrelator(builder);
RelNode decorrelateNode = rel;
- try {
- decorrelateNode = decorrelator.correlateElimination(preparedRel, true);
- } catch (UnsupportedOperationException e) {
- // if the correlation exists in an unsupported operator, retain the
original plan.
+ 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.
+ }
}
HepProgram postProgram = HepProgram.builder()
@@ -255,6 +257,29 @@ 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 36d56a9f04..2309102ff8 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -86,6 +86,7 @@
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;
@@ -108,6 +109,7 @@
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;
@@ -3801,8 +3803,7 @@ 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 RexLiteral means maximum number of rows to fetch is
deterministic,
- * RexDynamicParam mean maximum number is dynamic.
+ * @param fetchNode Maximum number of rows to fetch
* @param nodes Sort expressions
*/
public RelBuilder sortLimit(@Nullable RexNode offsetNode, @Nullable RexNode
fetchNode,
@@ -3812,12 +3813,17 @@ public RelBuilder sortLimit(@Nullable RexNode
offsetNode, @Nullable RexNode fetc
throw new IllegalArgumentException("OFFSET node must be RexLiteral or
RexDynamicParam");
}
}
- if (fetchNode != null) {
- if (!(fetchNode instanceof RexLiteral || fetchNode instanceof
RexDynamicParam)) {
- throw new IllegalArgumentException("FETCH 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());
}
-
final Registrar registrar = new Registrar(fields(), ImmutableList.of());
final List<RelFieldCollation> fieldCollations =
registrar.registerFieldCollations(nodes);
@@ -3884,6 +3890,38 @@ 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 a90099d7cb..703536de88 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -61,6 +61,9 @@ 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 40b825939d..70370d7bb1 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,6 +34,7 @@
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertThrows;
/**
* Tests for {@link EnumUtils}.
@@ -186,6 +187,16 @@ 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 772c409692..01f1578936 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
@@ -4950,6 +4950,74 @@ 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 "
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 b22218b955..5f8b8edfb1 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -83,6 +83,7 @@
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;
@@ -3631,6 +3632,35 @@ 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/test/JdbcTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
index 83ce5b1d78..f213b12dcb 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -3581,6 +3581,181 @@ 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()
@@ -6058,6 +6233,169 @@ 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 493df7c30b..7ad9a4d733 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -52,7 +52,10 @@
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;
@@ -5647,6 +5650,108 @@ 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 9c3c30e344..460e066051 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 " + fetchDouble;
+ final String sql = "select * from emp order by ename limit " +
fetch.toPlainString();
final RelMetadataFixture fixture = sql(sql);
fixture.assertThatRowCount(is(EMP_SIZE), is(0D), is(fetchDouble));
}
@@ -1496,6 +1496,37 @@ 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 4ba2f4aa96..5c687519da 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1738,6 +1738,34 @@ 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"
@@ -5997,10 +6025,9 @@ private void checkEmptyJoin(RelOptFixture f) {
}
/** Test case for
- * <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() {
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
+ * Add expression support for FETCH</a>. */
+ @Test void testSortWithDynamicParamPushesOnce() {
HepProgramBuilder builder = new HepProgramBuilder();
builder.addRuleClass(SortProjectTransposeRule.class);
builder.addRuleClass(SortUnionTransposeRule.class);
@@ -9730,6 +9757,19 @@ 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() {
@@ -12218,6 +12258,39 @@ 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 6b3255e653..6ce401502c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1263,6 +1263,15 @@ 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 018c27e898..2e472a968c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -10747,6 +10747,22 @@ 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 68bb56cf36..44055f7074 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,6 +78,36 @@ 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 ab26ca8524..32f423c3e1 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -2950,6 +2950,46 @@ 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>
@@ -11305,6 +11345,95 @@ 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>
@@ -19923,7 +20052,55 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
]]>
</Resource>
</TestCase>
- <TestCase name="testSortWithDynamicParam">
+ <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">
<Resource name="sql">
<![CDATA[SELECT x.sal
FROM (SELECT emp1.sal
@@ -19957,14 +20134,16 @@ LogicalSort(fetch=[?2])
LogicalProject(SAL=[$0])
LogicalSort(fetch=[?2])
LogicalUnion(all=[true])
- 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]])
+ 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]])
]]>
</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 217b2bbc03..6e98c11baa 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -2601,6 +2601,18 @@ 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>
diff --git a/core/src/test/resources/sql/fetch.iq
b/core/src/test/resources/sql/fetch.iq
new file mode 100644
index 0000000000..8f4b0dd53d
--- /dev/null
+++ b/core/src/test/resources/sql/fetch.iq
@@ -0,0 +1,183 @@
+# 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/server/src/test/java/org/apache/calcite/test/ServerTest.java
b/server/src/test/java/org/apache/calcite/test/ServerTest.java
index 355d39de7d..39d434f23a 100644
--- a/server/src/test/java/org/apache/calcite/test/ServerTest.java
+++ b/server/src/test/java/org/apache/calcite/test/ServerTest.java
@@ -43,6 +43,7 @@
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;
@@ -452,6 +453,42 @@ 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 904ab04619..56fbf8a86c 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -427,8 +427,13 @@ ## 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*, *count* and *start* may each be either an unsigned numeric literal
-or a dynamic parameter whose value is numeric.
+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.
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 4058d716bf..394aecf26f 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,12 +4104,31 @@ 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 .*");
}
/**
