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 e72d54dfa1 [CALCITE-7662] Add expression support for OFFSET
e72d54dfa1 is described below
commit e72d54dfa13aead34628606703352a5ff6eec6f3
Author: Kirill Tkalenko <[email protected]>
AuthorDate: Sun Jul 26 07:06:42 2026 +0300
[CALCITE-7662] Add expression support for OFFSET
---
core/src/main/codegen/templates/Parser.jj | 25 ++-
.../adapter/enumerable/EnumerableLimit.java | 10 +-
.../adapter/enumerable/EnumerableLimitSort.java | 8 +-
.../enumerable/EnumerableMergeUnionRule.java | 20 +--
.../calcite/rel/metadata/RelMdMinRowCount.java | 6 +-
.../org/apache/calcite/rel/metadata/RelMdUtil.java | 5 +-
.../calcite/rel/rel2sql/RelToSqlConverter.java | 11 +-
.../calcite/rel/rules/SortJoinTransposeRule.java | 5 +-
.../calcite/rel/rules/SortUnionTransposeRule.java | 24 ++-
.../main/java/org/apache/calcite/rex/RexUtil.java | 69 ++++++--
.../apache/calcite/runtime/CalciteResource.java | 17 +-
.../java/org/apache/calcite/sql/SqlDialect.java | 54 ++++--
.../calcite/sql/dialect/MysqlSqlDialect.java | 10 +-
.../calcite/sql/dialect/SqliteSqlDialect.java | 9 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 39 +++--
.../apache/calcite/sql2rel/RelDecorrelator.java | 6 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 48 +++---
.../calcite/runtime/CalciteResource.properties | 7 +-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 71 ++++++++
.../org/apache/calcite/rex/RexProgramTest.java | 14 ++
.../java/org/apache/calcite/test/JdbcTest.java | 184 +++++++++++++++++---
.../org/apache/calcite/test/RelBuilderTest.java | 78 +++++++++
.../org/apache/calcite/test/RelMetadataTest.java | 31 ++++
.../org/apache/calcite/test/RelOptRulesTest.java | 61 +++++++
.../apache/calcite/test/SqlToRelConverterTest.java | 9 +
.../org/apache/calcite/test/SqlValidatorTest.java | 27 ++-
.../test/enumerable/EnumerableMergeUnionTest.java | 62 ++++++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 117 +++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 12 ++
core/src/test/resources/sql/offset.iq | 186 +++++++++++++++++++++
.../java/org/apache/calcite/test/ServerTest.java | 25 +++
site/_docs/reference.md | 18 +-
.../apache/calcite/sql/parser/SqlParserTest.java | 21 ++-
33 files changed, 1140 insertions(+), 149 deletions(-)
diff --git a/core/src/main/codegen/templates/Parser.jj
b/core/src/main/codegen/templates/Parser.jj
index ce69124c4b..185a85f508 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -689,13 +689,13 @@ SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext)
:
*
* <blockquote><pre>
* [ LIMIT { count | ALL } ]
- * [ OFFSET start ]</pre>
+ * [ OFFSET { start | expression } ]</pre>
* </blockquote>
*
* <p>Trino syntax for limit:
*
* <blockquote><pre>
- * [ OFFSET start ]
+ * [ OFFSET { start | expression } ]
* [ LIMIT { count | ALL } ]</pre>
* </blockquote>
*
@@ -708,7 +708,7 @@ SqlNode ExprOrJoinOrOrderedQuery(ExprContext exprContext) :
* <p>SQL:2008 syntax for limit:
*
* <blockquote><pre>
- * [ OFFSET start { ROW | ROWS } ]
+ * [ OFFSET { start | expression } { ROW | ROWS } ]
* [ FETCH { FIRST | NEXT } [ count | (expression) ] { ROW | ROWS } ONLY
]</pre>
* </blockquote>
*/
@@ -783,7 +783,7 @@ void OffsetClause(Span s, SqlNode[] offsetFetch) :
// ROW or ROWS is required in SQL:2008 but we make it optional
// because it is not present in Postgres-style syntax.
<OFFSET> { s.add(this); }
- offsetFetch[0] = UnsignedNumericLiteralOrParam()
+ offsetFetch[0] = OffsetCount()
[ <ROW> | <ROWS> ]
}
@@ -800,6 +800,23 @@ void FetchClause(SqlNode[] offsetFetch) :
( <ROW> | <ROWS> ) <ONLY>
}
+/**
+ * Parses the start value or expression of an OFFSET clause.
+ */
+SqlNode OffsetCount() :
+{
+ final SqlNode e;
+}
+{
+ // Unlike FETCH expressions, OFFSET expressions do not require parentheses
+ // and may start with a numeric literal or dynamic parameter. Therefore, a
+ // separate UnsignedNumericLiteralOrParam alternative would consume only
+ // the prefix of expressions such as "OFFSET 1 + 2" or "OFFSET ? + 1".
+ // Expression also covers standalone start values.
+ e = Expression(ExprContext.ACCEPT_NON_QUERY)
+ { return e; }
+}
+
/**
* Parses the row count of a FETCH clause. Expressions must be parenthesized.
*/
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
index de1f94d562..96a04be1b8 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
@@ -107,14 +107,14 @@ public static EnumerableLimit create(final RelNode input,
@Nullable RexNode offs
builder.append("offset",
Expressions.call(BuiltInMethod.SKIP_BIG_DECIMAL.method, v,
getExpression(offset, "OFFSET", implementor, builder,
- roundingPolicyExp, false)));
+ roundingPolicyExp)));
}
if (fetch != null) {
v =
builder.append("fetch",
Expressions.call(BuiltInMethod.TAKE_BIG_DECIMAL.method, v,
getExpression(fetch, "FETCH", implementor, builder,
- roundingPolicyExp, true)));
+ roundingPolicyExp)));
}
builder.add(Expressions.return_(null, v));
@@ -123,7 +123,7 @@ public static EnumerableLimit create(final RelNode input,
@Nullable RexNode offs
static Expression getExpression(RexNode rexNode, String kind,
EnumerableRelImplementor implementor, BlockBuilder builder,
- Expression roundingPolicy, boolean translateExpression) {
+ Expression roundingPolicy) {
final Expression value;
if (rexNode instanceof RexDynamicParam) {
final RexDynamicParam param = (RexDynamicParam) rexNode;
@@ -134,10 +134,6 @@ static Expression getExpression(RexNode rexNode, String
kind,
} 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())
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimitSort.java
index 97d9fd8169..68759436d0 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
@@ -105,7 +105,7 @@ public static EnumerableLimitSort create(
fetchVal = Expressions.constant(BigDecimal.valueOf(Integer.MAX_VALUE));
} else {
fetchVal =
- getExpression(this.fetch, "FETCH", implementor, builder,
roundingPolicyExp, true);
+ getExpression(this.fetch, "FETCH", implementor, builder,
roundingPolicyExp);
}
final Expression offsetVal;
@@ -113,7 +113,7 @@ public static EnumerableLimitSort create(
offsetVal = Expressions.constant(BigDecimal.ZERO);
} else {
offsetVal =
- getExpression(this.offset, "OFFSET", implementor, builder,
roundingPolicyExp, false);
+ getExpression(this.offset, "OFFSET", implementor, builder,
roundingPolicyExp);
}
builder.add(
@@ -125,10 +125,10 @@ public static EnumerableLimitSort create(
builder.appendIfNotNull("comparator", pair.right))
.appendIfNotNull(
builder.appendIfNotNull("offset",
- Expressions.constant(offsetVal)))
+ offsetVal))
.appendIfNotNull(
builder.appendIfNotNull("fetch",
- Expressions.constant(fetchVal))))));
+ fetchVal)))));
return implementor.result(physType, builder.toBlock());
}
}
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableMergeUnionRule.java
index 57f864794a..3f2bdc5fe1 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
@@ -27,7 +27,6 @@
import org.apache.calcite.rel.logical.LogicalUnion;
import org.apache.calcite.rel.type.RelDataType;
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;
@@ -90,15 +89,16 @@ public EnumerableMergeUnionRule(Config config) {
RexNode inputFetch = null;
if (sort.fetch != null) {
final boolean safeToReevaluate =
- RexUtil.isDeterministic(sort.fetch);
- if (sort.offset == null && safeToReevaluate) {
- inputFetch = sort.fetch;
- } else if (safeToReevaluate
- && sort.fetch instanceof RexLiteral
- && sort.offset instanceof RexLiteral) {
- inputFetch =
- call.builder().literal(RexLiteral.bigDecimalValue(sort.fetch)
- .add(RexLiteral.bigDecimalValue(sort.offset)));
+ RexUtil.isDeterministic(sort.fetch)
+ && (sort.offset == null || RexUtil.isDeterministic(sort.offset));
+ if (safeToReevaluate) {
+ if (sort.offset == null) {
+ inputFetch = sort.fetch;
+ } else {
+ inputFetch =
+ RexUtil.makeOffsetFetchSum(
+ sort.getCluster().getRexBuilder(), sort.offset, sort.fetch);
+ }
}
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java
b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdMinRowCount.java
index 2cb710f398..298e77f2d8 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
@@ -117,7 +117,8 @@ public Double getMinRowCount(Sort rel, RelMetadataQuery mq)
{
}
final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ literalValueApproximatedByDouble(rel.offset,
+ rel.offset == null ? 0D : rowCount);
rowCount = Math.max(rowCount - offset, 0D);
final double limit =
@@ -133,7 +134,8 @@ public Double getMinRowCount(EnumerableLimit rel,
RelMetadataQuery mq) {
}
final double offset =
- literalValueApproximatedByDouble(rel.offset, 0D);
+ literalValueApproximatedByDouble(rel.offset,
+ rel.offset == null ? 0D : rowCount);
rowCount = Math.max(rowCount - offset, 0D);
final double limit =
diff --git a/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java
b/core/src/main/java/org/apache/calcite/rel/metadata/RelMdUtil.java
index 5b09628938..ab7dab016b 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
@@ -29,7 +29,6 @@
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
-import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexLocalRef;
@@ -1057,7 +1056,9 @@ private static boolean alreadySmaller(RelMetadataQuery
mq, RelNode input,
}
}
final Double rowCount = mq.getMaxRowCount(input);
- if (rowCount == null || offset instanceof RexDynamicParam || !(fetch
instanceof RexLiteral)) {
+ if (rowCount == null
+ || (offset != null && !(offset instanceof RexLiteral))
+ || !(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 2f96e8f73b..a5b8858a3a 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
@@ -1328,7 +1328,7 @@ public Result visit(Sort e) {
SqlNodeList sortExps = exprList(builder.context, e.getSortExps());
sqlSelect.setOrderBy(sortExps);
if (e.offset != null) {
- SqlNode offset = builder.context.toSql(null, e.offset);
+ SqlNode offset = toSqlOffset(e, builder.context);
sqlSelect.setOffset(offset);
}
if (e.fetch != null) {
@@ -1393,10 +1393,17 @@ void offsetFetch(Sort e, Builder builder) {
builder.setFetch(toSqlFetch(e, builder.context));
}
if (e.offset != null) {
- builder.setOffset(builder.context.toSql(null, e.offset));
+ builder.setOffset(toSqlOffset(e, builder.context));
}
}
+ private static SqlNode toSqlOffset(Sort sort, Context context) {
+ final RexNode offset = requireNonNull(sort.offset, "offset");
+ final @Nullable RexLiteral reduced =
+ RexUtil.reduceOffsetToLiteral(sort.getCluster(), offset);
+ return context.toSql(null, reduced == null ? offset : reduced);
+ }
+
private static SqlNode toSqlFetch(Sort sort, Context context) {
final RexNode fetch = requireNonNull(sort.fetch, "fetch");
final @Nullable RexLiteral reduced =
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SortJoinTransposeRule.java
index df967e56aa..dfb2d8401d 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
@@ -30,7 +30,6 @@
import org.apache.calcite.rel.metadata.RelMdUtil;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rex.RexBuilder;
-import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.tools.RelBuilderFactory;
@@ -106,8 +105,8 @@ public SortJoinTransposeRule(Class<? extends Sort>
sortClass,
final Join join = call.rel(1);
// The pushed fetch is calculated from literal offset and fetch values.
- if (sort.offset instanceof RexDynamicParam
- || sort.fetch != null && !(sort.fetch instanceof RexLiteral)) {
+ if ((sort.offset != null && !(sort.offset instanceof RexLiteral))
+ || (sort.fetch != null && !(sort.fetch instanceof RexLiteral))) {
return false;
}
diff --git
a/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java
b/core/src/main/java/org/apache/calcite/rel/rules/SortUnionTransposeRule.java
index 93b6af657c..21ec54ec60 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,6 +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.RexNode;
import org.apache.calcite.rex.RexUtil;
import org.apache.calcite.tools.RelBuilderFactory;
@@ -67,12 +68,14 @@ public SortUnionTransposeRule(
@Override public boolean matches(RelOptRuleCall call) {
final Sort sort = call.rel(0);
final Union union = call.rel(1);
- // Re-evaluating a non-deterministic FETCH in every branch can produce a
- // different limit from the top Sort.
+ // Re-evaluating a non-deterministic OFFSET or 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.offset == null
+ || sort.fetch != null
+ && RexUtil.isDeterministic(sort.offset))
&& (sort.fetch == null
|| RexUtil.isDeterministic(sort.fetch))
&& (config.matchNullFetch() || sort.fetch != null);
@@ -81,6 +84,17 @@ public SortUnionTransposeRule(
@Override public void onMatch(RelOptRuleCall call) {
final Sort sort = call.rel(0);
final Union union = call.rel(1);
+ // OFFSET cannot be pushed into each input independently. However, only
+ // the first OFFSET + FETCH rows of an input can contribute to the final
+ // result, so use that value as the input FETCH and retain the original
+ // OFFSET and FETCH in the top Sort.
+ final RexNode inputFetch;
+ if (sort.fetch == null || sort.offset == null) {
+ inputFetch = sort.fetch;
+ } else {
+ inputFetch =
+ RexUtil.makeOffsetFetchSum(sort.getCluster().getRexBuilder(),
sort.offset, sort.fetch);
+ }
List<RelNode> inputs = new ArrayList<>();
// Thus we use 'ret' as a flag to identify if we have finished pushing the
// sort past a union.
@@ -88,11 +102,11 @@ public SortUnionTransposeRule(
final RelMetadataQuery mq = call.getMetadataQuery();
for (RelNode input : union.getInputs()) {
if (!RelMdUtil.checkInputForCollationAndLimit(mq, input,
- sort.getCollation(), sort.offset, sort.fetch)) {
+ sort.getCollation(), null, inputFetch)) {
ret = false;
Sort branchSort =
sort.copy(sort.getTraitSet(), input,
- sort.getCollation(), sort.offset, sort.fetch);
+ sort.getCollation(), null, inputFetch);
inputs.add(branchSort);
} else {
inputs.add(input);
diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
index b592093a5a..b7c30b7608 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java
@@ -66,6 +66,7 @@
import org.checkerframework.checker.nullness.qual.Nullable;
import java.math.BigDecimal;
+import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
@@ -881,12 +882,49 @@ public static boolean containsDynamicParam(RexNode e) {
/** Converts a FETCH expression result to its validated canonical
representation. */
public static BigDecimal validateFetchValue(@Nullable Number value) {
+ return validateOffsetFetchValue(value, "FETCH");
+ }
+
+ /** Creates the FETCH needed when OFFSET and FETCH are pushed into an input.
+ *
+ * <p>Enumerable execution rounds OFFSET and FETCH independently to whole
+ * row counts. Therefore, the input must fetch
+ * {@code CEIL(offset) + CEIL(fetch)} rows rather than
+ * {@code CEIL(offset + fetch)} rows. The latter can be one row smaller when
+ * both values have a fractional part. */
+ public static RexNode makeOffsetFetchSum(RexBuilder rexBuilder,
+ RexNode offset, RexNode fetch) {
+ if (offset instanceof RexLiteral && fetch instanceof RexLiteral) {
+ return rexBuilder.makeExactLiteral(
+ RexLiteral.bigDecimalValue(offset).setScale(0, RoundingMode.CEILING)
+ .add(RexLiteral.bigDecimalValue(fetch)
+ .setScale(0, RoundingMode.CEILING)));
+ }
+ return rexBuilder.makeCall(SqlStdOperatorTable.PLUS,
+ ceil(rexBuilder, offset), ceil(rexBuilder, fetch));
+ }
+
+ private static RexNode ceil(RexBuilder rexBuilder, RexNode node) {
+ if (node instanceof RexLiteral) {
+ return rexBuilder.makeExactLiteral(
+ RexLiteral.bigDecimalValue(node).setScale(0, RoundingMode.CEILING));
+ }
+ return rexBuilder.makeCall(SqlStdOperatorTable.CEIL, node);
+ }
+
+ /** Converts an OFFSET expression result to its validated canonical
representation. */
+ public static BigDecimal validateOffsetValue(@Nullable Number value) {
+ return validateOffsetFetchValue(value, "OFFSET");
+ }
+
+ private static BigDecimal validateOffsetFetchValue(@Nullable Number value,
+ String kind) {
if (value == null) {
- throw new IllegalArgumentException("FETCH expression evaluated to NULL");
+ throw new IllegalArgumentException(kind + " expression evaluated to
NULL");
}
final BigDecimal decimal = NumberUtil.toBigDecimal(value);
if (decimal.signum() < 0) {
- throw new IllegalArgumentException("FETCH value " + value
+ throw new IllegalArgumentException(kind + " value " + value
+ " is out of range; expected a non-negative value");
}
return decimal;
@@ -895,28 +933,39 @@ public static BigDecimal validateFetchValue(@Nullable
Number value) {
/** Reduces a constant FETCH expression to a validated literal. */
public static @Nullable RexLiteral reduceFetchToLiteral(
RelOptCluster cluster, RexNode fetch) {
+ return reduceOffsetFetchToLiteral(cluster, fetch, "FETCH");
+ }
+
+ /** Reduces a constant OFFSET expression to a validated literal. */
+ public static @Nullable RexLiteral reduceOffsetToLiteral(
+ RelOptCluster cluster, RexNode offset) {
+ return reduceOffsetFetchToLiteral(cluster, offset, "OFFSET");
+ }
+
+ private static @Nullable RexLiteral reduceOffsetFetchToLiteral(
+ RelOptCluster cluster, RexNode node, String kind) {
final RexLiteral literal;
- if (fetch instanceof RexLiteral) {
- literal = (RexLiteral) fetch;
+ if (node instanceof RexLiteral) {
+ literal = (RexLiteral) node;
} else {
- if (!isConstant(fetch)
- || !isDeterministic(fetch)
- || containsDynamicFunction(fetch)
- || containsDynamicParam(fetch)) {
+ if (!isConstant(node)
+ || !isDeterministic(node)
+ || containsDynamicFunction(node)
+ || containsDynamicParam(node)) {
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);
+ Collections.singletonList(node), reducedValues);
final RexNode reduced = reducedValues.get(0);
if (!(reduced instanceof RexLiteral)) {
return null;
}
literal = (RexLiteral) reduced;
}
- validateFetchValue(literal.getValueAs(Number.class));
+ validateOffsetFetchValue(literal.getValueAs(Number.class), kind);
return literal;
}
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 452c2bf84a..2e5056da1d 100644
--- a/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
+++ b/core/src/main/java/org/apache/calcite/runtime/CalciteResource.java
@@ -164,14 +164,19 @@ 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("{0} expression must have a numeric type; actual type is
''{1}''")
+ ExInst<SqlValidatorException> offsetFetchExpressionMustBeNumeric(String kind,
+ String type);
- @BaseMessage("FETCH expression cannot reference table column ''{0}''")
- ExInst<SqlValidatorException> fetchExpressionCannotReferenceColumn(String
column);
+ @BaseMessage("{0} expression cannot reference table column ''{1}''")
+ ExInst<SqlValidatorException> offsetFetchExpressionCannotReferenceColumn(
+ String kind, String column);
- @BaseMessage("FETCH expression evaluated to NULL")
- ExInst<SqlValidatorException> fetchExpressionEvaluatedToNull();
+ @BaseMessage("{0} expression evaluated to NULL")
+ ExInst<SqlValidatorException> offsetFetchExpressionEvaluatedToNull(String
kind);
+
+ @BaseMessage("{0} must not be negative")
+ ExInst<SqlValidatorException> offsetFetchValueMustNotBeNegative(String kind);
@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 164f212c6c..eef1c386d5 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java
@@ -1078,7 +1078,7 @@ protected static void unparseFetchUsingAnsi(SqlWriter
writer, @Nullable SqlNode
final SqlWriter.Frame offsetFrame =
writer.startList(SqlWriter.FrameTypeEnum.OFFSET);
writer.keyword("OFFSET");
- offset.unparse(writer, -1, -1);
+ unparseOffsetExpression(writer, offset);
writer.keyword("ROWS");
writer.endList(offsetFrame);
}
@@ -1088,24 +1088,36 @@ protected static void unparseFetchUsingAnsi(SqlWriter
writer, @Nullable SqlNode
writer.startList(SqlWriter.FrameTypeEnum.FETCH);
writer.keyword("FETCH");
writer.keyword("NEXT");
- if (fetch instanceof SqlLiteral
- || fetch instanceof SqlDynamicParam) {
- fetch.unparse(writer, -1, -1);
- } else {
- final SqlWriter.Frame expressionFrame = writer.startList("(", ")");
- if (fetch instanceof SqlCall) {
- writer.getDialect().unparseCall(writer, (SqlCall) fetch, 0, 0);
- } else {
- fetch.unparse(writer, 0, 0);
- }
- writer.endList(expressionFrame);
- }
+ unparseFetchExpression(writer, fetch);
writer.keyword("ROWS");
writer.keyword("ONLY");
writer.endList(fetchFrame);
}
}
+ private static void unparseOffsetExpression(SqlWriter writer, SqlNode
offset) {
+ unparseExpression(writer, offset);
+ }
+
+ private static void unparseFetchExpression(SqlWriter writer, SqlNode fetch) {
+ if (fetch instanceof SqlLiteral
+ || fetch instanceof SqlDynamicParam) {
+ fetch.unparse(writer, -1, -1);
+ return;
+ }
+ final SqlWriter.Frame expressionFrame = writer.startList("(", ")");
+ unparseExpression(writer, fetch);
+ writer.endList(expressionFrame);
+ }
+
+ private static void unparseExpression(SqlWriter writer, SqlNode node) {
+ if (node instanceof SqlCall) {
+ writer.getDialect().unparseCall(writer, (SqlCall) node, 0, 0);
+ } else {
+ node.unparse(writer, 0, 0);
+ }
+ }
+
/** Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax. */
protected static void unparseFetchUsingLimit(SqlWriter writer, @Nullable
SqlNode offset,
@Nullable SqlNode fetch) {
@@ -1113,12 +1125,12 @@ protected static void unparseFetchUsingLimit(SqlWriter
writer, @Nullable SqlNode
}
/** Unparses offset/fetch using "LIMIT fetch OFFSET offset" syntax,
- * optionally allowing a scalar expression as fetch. */
+ * optionally allowing scalar expressions as fetch and offset. */
protected static void unparseFetchUsingLimit(SqlWriter writer, @Nullable
SqlNode offset,
@Nullable SqlNode fetch, boolean allowExpression) {
checkArgument(fetch != null || offset != null);
unparseLimit(writer, fetch, allowExpression);
- unparseOffset(writer, offset);
+ unparseOffset(writer, offset, allowExpression);
}
protected static void unparseLimit(SqlWriter writer, @Nullable SqlNode
fetch) {
@@ -1145,7 +1157,19 @@ private static void unparseLimit(SqlWriter writer,
@Nullable SqlNode fetch,
}
protected static void unparseOffset(SqlWriter writer, @Nullable SqlNode
offset) {
+ unparseOffset(writer, offset, false);
+ }
+
+ private static void unparseOffset(SqlWriter writer, @Nullable SqlNode offset,
+ boolean allowExpression) {
if (offset != null) {
+ if (!allowExpression
+ && !(offset instanceof SqlLiteral)
+ && !(offset instanceof SqlDynamicParam)) {
+ throw new IllegalArgumentException(
+ "LIMIT dialect does not support OFFSET expressions that cannot "
+ + "be reduced to a literal");
+ }
writer.newlineAndIndent();
final SqlWriter.Frame offsetFrame =
writer.startList(SqlWriter.FrameTypeEnum.OFFSET);
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
index 03d4d2c504..1f114a1553 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/MysqlSqlDialect.java
@@ -147,7 +147,15 @@ public MysqlSqlDialect(Context context) {
@Override public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode
offset,
@Nullable SqlNode fetch) {
- unparseFetchUsingLimit(writer, offset, fetch);
+ if (offset != null && fetch == null) {
+ // MySQL has no OFFSET-only syntax. Its documented unlimited-row form
+ // uses the maximum unsigned BIGINT value as LIMIT.
+ final SqlNode unlimited =
+ SqlLiteral.createExactNumeric("18446744073709551615",
SqlParserPos.ZERO);
+ unparseFetchUsingLimit(writer, offset, unlimited);
+ } else {
+ unparseFetchUsingLimit(writer, offset, fetch);
+ }
}
@Override public @Nullable SqlNode emulateNullDirection(SqlNode node,
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/SqliteSqlDialect.java
index f312764136..833ac5c10d 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,14 @@ public SqliteSqlDialect(SqlDialect.Context context) {
@Override public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode
offset,
@Nullable SqlNode fetch) {
- unparseFetchUsingLimit(writer, offset, fetch, true);
+ if (offset != null && fetch == null) {
+ // SQLite has no OFFSET-only syntax. LIMIT -1 means no upper bound.
+ final SqlNode unlimited =
+ SqlLiteral.createExactNumeric("-1", SqlParserPos.ZERO);
+ unparseFetchUsingLimit(writer, offset, unlimited, true);
+ } else {
+ 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 0ea01a3514..8a5d3dfe9c 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
@@ -70,6 +70,7 @@
import org.apache.calcite.sql.SqlMerge;
import org.apache.calcite.sql.SqlNode;
import org.apache.calcite.sql.SqlNodeList;
+import org.apache.calcite.sql.SqlNumericLiteral;
import org.apache.calcite.sql.SqlOperator;
import org.apache.calcite.sql.SqlOperatorTable;
import org.apache.calcite.sql.SqlOrderBy;
@@ -1771,31 +1772,40 @@ private void handleOffsetFetch(@Nullable SqlNode
offset, @Nullable SqlNode fetch
}
}
- private void validateFetchExpression(@Nullable SqlNode fetch) {
- if (fetch == null || fetch instanceof SqlDynamicParam) {
+ private void validateOffsetFetchExpression(@Nullable SqlNode node,
+ String kind) {
+ if (node == null || node instanceof SqlDynamicParam) {
return;
}
- if (SqlUtil.isNullLiteral(fetch, true)) {
- throw newValidationError(fetch,
- RESOURCE.fetchExpressionEvaluatedToNull());
+ if (SqlUtil.isNullLiteral(node, true)) {
+ throw newValidationError(node,
+ RESOURCE.offsetFetchExpressionEvaluatedToNull(kind));
+ }
+ if (node instanceof SqlNumericLiteral
+ && requireNonNull(((SqlNumericLiteral) node).bigDecimalValue())
+ .signum() < 0) {
+ throw newValidationError(node,
+ RESOURCE.offsetFetchValueMustNotBeNegative(kind));
}
- validateNoAggs(aggOrOverFinder, fetch, "FETCH");
- fetch.accept(new SqlBasicVisitor<Void>() {
+ validateNoAggs(aggOrOverFinder, node, kind);
+ node.accept(new SqlBasicVisitor<Void>() {
@Override public Void visit(SqlIdentifier id) {
if (makeNullaryCall(id) != null) {
return null;
}
throw newValidationError(id,
- RESOURCE.fetchExpressionCannotReferenceColumn(id.toString()));
+ RESOURCE.offsetFetchExpressionCannotReferenceColumn(kind,
+ id.toString()));
}
});
final SqlValidatorScope scope = getEmptyScope();
- inferUnknownTypes(typeFactory.createSqlType(SqlTypeName.DECIMAL), scope,
fetch);
- validateExpr(fetch, scope);
- final RelDataType type = getValidatedNodeType(fetch);
+ inferUnknownTypes(typeFactory.createSqlType(SqlTypeName.DECIMAL), scope,
node);
+ validateExpr(node, scope);
+ final RelDataType type = getValidatedNodeType(node);
if (!SqlTypeUtil.isNumeric(type)) {
- throw newValidationError(fetch,
- RESOURCE.fetchExpressionMustBeNumeric(type.getFullTypeString()));
+ throw newValidationError(node,
+ RESOURCE.offsetFetchExpressionMustBeNumeric(kind,
+ type.getFullTypeString()));
}
}
@@ -4527,7 +4537,8 @@ protected void validateSelect(
validateWindowClause(select);
validateQualifyClause(select);
handleOffsetFetch(select.getOffset(), select.getFetch());
- validateFetchExpression(select.getFetch());
+ validateOffsetFetchExpression(select.getOffset(), "OFFSET");
+ validateOffsetFetchExpression(select.getFetch(), "FETCH");
// 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 4e4104ad48..daabe37b89 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1142,10 +1142,14 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
}
static boolean canDecorrelateOffsetFetch(Sort sort) {
+ final @Nullable RexLiteral offset = sort.offset == null
+ ? null
+ : RexUtil.reduceOffsetToLiteral(sort.getCluster(), sort.offset);
final @Nullable RexLiteral fetch = sort.fetch == null
? null
: RexUtil.reduceFetchToLiteral(sort.getCluster(), sort.fetch);
- return isNonNegativeIntegralLiteral(sort.offset)
+ return (sort.offset == null
+ || offset != null && isNonNegativeIntegralLiteral(offset))
&& (sort.fetch == null
|| fetch != null && isNonNegativeIntegralLiteral(fetch));
}
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 2309102ff8..92c5a41415 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -3801,29 +3801,14 @@ public RelBuilder sortLimit(Number offset, Number fetch,
/** Creates a {@link Sort} by a list of expressions, with limitNode and
offsetNode.
*
- * @param offsetNode RexLiteral means number of rows to skip is
deterministic,
- * RexDynamicParam means number of rows to skip is dynamic.
+ * @param offsetNode Number of rows to skip
* @param fetchNode Maximum number of rows to fetch
* @param nodes Sort expressions
*/
public RelBuilder sortLimit(@Nullable RexNode offsetNode, @Nullable RexNode
fetchNode,
Iterable<? extends RexNode> nodes) {
- if (offsetNode != null) {
- if (!(offsetNode instanceof RexLiteral || offsetNode instanceof
RexDynamicParam)) {
- throw new IllegalArgumentException("OFFSET node must be RexLiteral or
RexDynamicParam");
- }
- }
- if (fetchNode != null && !isValidFetchExpression(fetchNode)) {
- throw new IllegalArgumentException(
- "FETCH node must not reference input fields or contain aggregate
functions, "
- + "window functions, or subqueries");
- }
- if (fetchNode != null
- && !SqlTypeUtil.isNumeric(fetchNode.getType())) {
- throw new IllegalArgumentException(
- "FETCH node must have a numeric type; actual type is "
- + fetchNode.getType().getFullTypeString());
- }
+ validateOffsetFetchExpression(offsetNode, "OFFSET");
+ validateOffsetFetchExpression(fetchNode, "FETCH");
final Registrar registrar = new Registrar(fields(), ImmutableList.of());
final List<RelFieldCollation> fieldCollations =
registrar.registerFieldCollations(nodes);
@@ -3890,14 +3875,31 @@ 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()));
+ private static void validateOffsetFetchExpression(@Nullable RexNode node,
+ String kind) {
+ if (node == null) {
+ return;
+ }
+ if (!isValidOffsetFetchExpression(node)) {
+ throw new IllegalArgumentException(
+ kind + " node must not reference input fields or contain aggregate
functions, "
+ + "window functions, or subqueries");
+ }
+ if (!SqlTypeUtil.isNumeric(node.getType())) {
+ throw new IllegalArgumentException(
+ kind + " node must have a numeric type; actual type is "
+ + node.getType().getFullTypeString());
+ }
+ }
+
+ private static boolean isValidOffsetFetchExpression(RexNode node) {
+ return Boolean.TRUE.equals(node.accept(new
OffsetFetchExpressionVisitor()));
}
- /** Visitor that validates FETCH expressions. */
- private static class FetchExpressionVisitor
+ /** Visitor that validates OFFSET and FETCH expressions. */
+ private static class OffsetFetchExpressionVisitor
extends RexVisitorImpl<@Nullable Boolean> {
- FetchExpressionVisitor() {
+ OffsetFetchExpressionVisitor() {
super(false);
}
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 703536de88..636e117c7e 100644
---
a/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
+++
b/core/src/main/resources/org/apache/calcite/runtime/CalciteResource.properties
@@ -61,9 +61,10 @@ 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
+OffsetFetchExpressionMustBeNumeric={0} expression must have a numeric type;
actual type is ''{1}''
+OffsetFetchExpressionCannotReferenceColumn={0} expression cannot reference
table column ''{1}''
+OffsetFetchExpressionEvaluatedToNull={0} expression evaluated to NULL
+OffsetFetchValueMustNotBeNegative={0} must not be negative
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/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 83a4f87b28..9877bd5779 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
@@ -5060,6 +5060,20 @@ private SqlDialect nonOrdinalDialect() {
sql(query).withMysql().ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionWithLimitDialect() {
+ final String query = "select \"product_id\"\n"
+ + "from \"product\"\n"
+ + "offset 1 + 2 rows";
+ final String expected = "SELECT `product_id`\n"
+ + "FROM `foodmart`.`product`\n"
+ + "LIMIT 18446744073709551615\n"
+ + "OFFSET 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>. */
@@ -5074,6 +5088,20 @@ private SqlDialect nonOrdinalDialect() {
sql(query).withSQLite().throws_(error);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testNegativeOffsetExpressionIsRejectedBeforeSqlGeneration() {
+ final String query = "select \"product_id\"\n"
+ + "from \"product\"\n"
+ + "offset 0 - 1 rows";
+ final String error =
+ "OFFSET 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>. */
@@ -5086,6 +5114,18 @@ private SqlDialect nonOrdinalDialect() {
+ "be reduced to a literal");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testParameterizedOffsetExpressionWithLimitDialect() {
+ final String query = "select \"product_id\"\n"
+ + "from \"product\"\n"
+ + "offset ? + 1 rows";
+ sql(query).withMysql().throws_(
+ "LIMIT dialect does not support OFFSET 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>. */
@@ -5099,6 +5139,37 @@ private SqlDialect nonOrdinalDialect() {
sql(query).withSQLite().ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testParameterizedOffsetExpressionWithSQLite() {
+ final String query = "select \"product_id\"\n"
+ + "from \"product\"\n"
+ + "offset ? + 1 rows";
+ final String expected = "SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "LIMIT -1\n"
+ + "OFFSET ? + 1";
+ sql(query).withSQLite().ok(expected);
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testDynamicOffsetExpressionIsNotReduced() {
+ final String query = "select \"product_id\"\n"
+ + "from \"product\"\n"
+ + "offset extract(day from current_date) rows";
+ final String expected = "SELECT \"product_id\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "OFFSET EXTRACT(DAY FROM CURRENT_DATE) ROWS";
+ sql(query).ok(expected);
+ sql(query).withPostgresql().ok(expected);
+ sql(query).withMysql().throws_(
+ "LIMIT dialect does not support OFFSET 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>. */
diff --git a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
index 5f8b8edfb1..332f1865a7 100644
--- a/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
+++ b/core/src/test/java/org/apache/calcite/rex/RexProgramTest.java
@@ -3661,6 +3661,20 @@ private void assertTypeAndToString(
containsString("FETCH value -1.5 is out of range"));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testValidateOffsetValueAllowsFractionalBigDecimal() {
+ assertThat(RexUtil.validateOffsetValue(new BigDecimal("1.5")),
+ is(new BigDecimal("1.5")));
+
+ final IllegalArgumentException e =
+ assertThrows(IllegalArgumentException.class,
+ () -> RexUtil.validateOffsetValue(new BigDecimal("-1.5")));
+ assertThat(e.getMessage(),
+ containsString("OFFSET 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 f213b12dcb..d235fa327c 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -3593,6 +3593,20 @@ public void checkOrderBy(final boolean desc,
+ "X=3\n");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpression() {
+ final CalciteAssert.AssertThat with = CalciteAssert.that();
+ final String values = "select * from (values (1), (2), (3), (4)) as
t(x)\n";
+ with.query(values + "offset 1 + abs(-1) rows")
+ .returns("X=3\n"
+ + "X=4\n");
+ with.query(values + "order by x desc offset 1 + abs(-1) rows")
+ .returns("X=2\n"
+ + "X=1\n");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
@@ -3618,6 +3632,20 @@ public void checkOrderBy(final boolean desc,
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testBindableOffsetExpression() {
+ try (Hook.Closeable ignored =
Hook.ENABLE_BINDABLE.addThread(Hook.propertyJ(true))) {
+ CalciteAssert.that()
+ .query("select * from (values (1), (2), (3), (4)) as t(x)\n"
+ + "offset rand_integer(1) + 2 rows")
+ .explainContains("BindableSort(offset=[+(RAND_INTEGER(1), 2)])")
+ .returns("X=3\n"
+ + "X=4\n");
+ }
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
@@ -3632,6 +3660,18 @@ public void checkOrderBy(final boolean desc,
+ "X=2\n");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionFunctionArguments() {
+ final CalciteAssert.AssertThat with = CalciteAssert.that();
+ final String values = "select * from (values (1), (2), (3)) as t(x)\n";
+ with.query(values + "offset abs(2) rows")
+ .returns("X=3\n");
+ with.query(values + "offset abs(-2) rows")
+ .returns("X=3\n");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
@@ -3647,6 +3687,20 @@ public void checkOrderBy(final boolean desc,
.throws_("FETCH expression evaluated to NULL");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionInvalidValue() {
+ final CalciteAssert.AssertThat with = CalciteAssert.that();
+ final String values = "select * from (values (1), (2), (3)) as t(x)\n";
+ with.query(values + "offset 0 - 1 rows")
+ .throws_("OFFSET must not be negative");
+ with.query(values + "offset -1 rows")
+ .throws_("OFFSET must not be negative");
+ with.query(values + "offset cast(null as integer) rows")
+ .throws_("OFFSET 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>. */
@@ -3655,13 +3709,13 @@ public void checkOrderBy(final boolean desc,
+ "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");
- }
+ 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 - 1) rows only) e")
+ .throws_("FETCH value -1 is out of range");
+ with.query(sqlPrefix + " fetch next (-1) rows only) e")
+ .throws_("FETCH must not be negative");
}
}
@@ -3681,6 +3735,9 @@ public void checkOrderBy(final boolean desc,
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 0.5 + 1 rows fetch next 1 row only"
+ + sqlSuffix)
+ .returns("DNAME=Sales; ENAME=Sebastian\n");
with.query(sqlPrefix + "offset 1.5 rows fetch next 1 row only" +
sqlSuffix)
.returns("DNAME=Sales; ENAME=Sebastian\n");
}
@@ -3690,20 +3747,24 @@ public void checkOrderBy(final boolean desc,
* <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");
- });
+ for (String offset
+ : new String[] {"?", "cast(? as decimal(2, 1)) + 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\" offset " + 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");
+ });
+ }
}
}
@@ -3739,6 +3800,37 @@ public void checkOrderBy(final boolean desc,
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testCorrelatedPreparedOffsetExpression() throws Exception {
+ for (String offset : 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\" offset " + offset + " rows) 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, 2},
+ new String[] {
+ "DNAME=Sales; ENAME=Theodore\n"
+ + "DNAME=Sales; ENAME=Sebastian\n",
+ "DNAME=Sales; ENAME=Sebastian\n"
+ });
+ checkPreparedParameterFails(connection, sql, -1,
+ "OFFSET must not be negative");
+ checkPreparedParameterNullFails(connection, sql,
+ "OFFSET 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>. */
@@ -3756,6 +3848,22 @@ public void checkOrderBy(final boolean desc,
.returns(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionBeyondLong() {
+ final CalciteAssert.AssertThat with = CalciteAssert.that();
+ final String values = "select * from (values (1), (2), (3), (4)) as
t(x)\n";
+ with.query(values + "offset 9223372036854775808 rows")
+ .returns("");
+ with.query(values + "offset "
+ + "cast(9223372036854775808 as decimal(20, 0)) + 1 rows")
+ .returns("");
+ with.query(values + "order by x offset "
+ + "cast(9223372036854775808 as decimal(20, 0)) + 1 rows")
+ .returns("");
+ }
+
/** Tests ORDER BY ... OFFSET ... FETCH. */
@Test void testOrderByOffsetFetch() {
CalciteAssert.that()
@@ -6277,6 +6385,37 @@ private CalciteAssert.AssertQuery withEmpDept(String
sql) {
});
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testPreparedOffsetExpression() throws Exception {
+ CalciteAssert.that()
+ .doWithConnection(connection -> {
+ final String values =
+ "select * from (values (1), (2), (3), (4)) as t(x)\n";
+ checkPreparedFetch(connection, values + "offset ? + 1 rows",
+ 1, "X=3\nX=4\n");
+ checkPreparedFetch(connection,
+ values + "order by x desc offset ? + 1 rows",
+ 1, "X=2\nX=1\n");
+ checkPreparedBigDecimalParameter(connection,
+ values + "offset cast(? as decimal(2, 1)) + 0 rows",
+ new BigDecimal("1.5"), "X=3\nX=4\n");
+ checkPreparedFetch(connection,
+ values + "offset abs(cast(? as integer)) rows",
+ -2, "X=3\nX=4\n");
+ checkPreparedBigDecimalParameter(connection,
+ values + "offset cast(? as decimal(20, 0)) rows",
+ new BigDecimal("9223372036854775808"), "");
+ checkPreparedParameterFails(connection,
+ values + "offset ? + 1 rows", -2,
+ "OFFSET must not be negative");
+ checkPreparedParameterNullFails(connection,
+ values + "offset ? + 1 rows",
+ "OFFSET 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>. */
@@ -6311,6 +6450,9 @@ private CalciteAssert.AssertQuery withEmpDept(String sql)
{
.doWithConnection(connection -> {
final String values =
"select * from (values (1), (2), (3), (4)) as t(x)\n";
+ checkPreparedFetch(connection,
+ values + "offset ? + 1 rows",
+ 1, "X=3\nX=4\n");
final String offset = values + "offset ? rows";
checkPreparedBigDecimalParameter(connection, offset,
new BigDecimal("1.5"),
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 7ad9a4d733..cce1aea309 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -5670,6 +5670,22 @@ private static RelNode
buildCorrelateWithJoin(JoinRelType type, RelBuilder build
ImmutableList.of()));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionCannotReferenceInputField() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ builder.scan("DEPT");
+ final RexNode field = builder.field("DEPTNO");
+
+ assertThrows(IllegalArgumentException.class,
+ () -> builder.sortLimit(field, null, ImmutableList.of()));
+ assertThrows(IllegalArgumentException.class,
+ () -> builder.sortLimit(
+ builder.call(SqlStdOperatorTable.PLUS, builder.literal(1), field),
+ null, ImmutableList.of()));
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
@@ -5683,6 +5699,19 @@ private static RelNode
buildCorrelateWithJoin(JoinRelType type, RelBuilder build
ImmutableList.of());
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionMustHaveNumericType() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ builder.scan("DEPT");
+
+ assertThrows(IllegalArgumentException.class,
+ () -> builder.sortLimit(builder.literal("x"), null,
ImmutableList.of()));
+ builder.sortLimit(builder.literal(new BigDecimal("1.5")), null,
+ ImmutableList.of());
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
@@ -5702,6 +5731,25 @@ private static RelNode
buildCorrelateWithJoin(JoinRelType type, RelBuilder build
+ " LogicalTableScan(table=[[scott, DEPT]])\n"));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionAllowsScalarCallAndDynamicParameter() {
+ final RelBuilder builder = RelBuilder.create(config().build());
+ final RelDataType intType =
+ builder.getTypeFactory().createSqlType(SqlTypeName.INTEGER);
+ builder.scan("DEPT")
+ .sortLimit(
+ builder.call(SqlStdOperatorTable.PLUS,
+ builder.getRexBuilder().makeDynamicParam(intType, 0),
+ builder.literal(1)),
+ null, ImmutableList.of());
+
+ assertThat(
+ builder.build(), hasTree("LogicalSort(offset=[+(?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>. */
@@ -5732,6 +5780,36 @@ private static RelNode
buildCorrelateWithJoin(JoinRelType type, RelBuilder build
() -> builder.sortLimit(null, subQuery, ImmutableList.of()));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionCannotContainAggregateWindowOrSubQuery() {
+ 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(aggregate, null, 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(over, null, 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(subQuery, null, ImmutableList.of()));
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-7592";>[CALCITE-7592]
* Add expression support for FETCH</a>. */
diff --git a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
index 460e066051..a40927c37a 100644
--- a/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelMetadataTest.java
@@ -1527,6 +1527,37 @@ void testColumnOriginsUnion() {
.assertThatRowCount(is(2D), is(0D), is(2D));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testMinRowCountOffsetExpression() {
+ final String sql = "select * from (values (1), (2)) as t(x)\n"
+ + "offset 2 - 2 rows";
+ 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 bad13247c3..aafd9429d2 100644
--- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java
@@ -1766,6 +1766,48 @@ private void
checkJoinProjectTransposeDoesNotMatch(JoinRelType type) {
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testSortUnionTransposePushesLiteralOffset() {
+ final String sql = "select a.name from dept a\n"
+ + "union all\n"
+ + "select b.name from dept b\n"
+ + "order by name offset 2 rows fetch next 3 rows only";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_SET_OP_TRANSPOSE)
+ .withRule(CoreRules.SORT_UNION_TRANSPOSE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testSortUnionTransposePushesParameterizedOffsetExpression() {
+ final String sql = "select a.name from dept a\n"
+ + "union all\n"
+ + "select b.name from dept b\n"
+ + "order by name offset ? + 1 rows fetch next 2 rows only";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_SET_OP_TRANSPOSE)
+ .withRule(CoreRules.SORT_UNION_TRANSPOSE)
+ .check();
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testSortUnionTransposeWithNonDeterministicOffset() {
+ final String sql = "select a.name from dept a\n"
+ + "union all\n"
+ + "select b.name from dept b\n"
+ + "order by name offset rand_integer(10) rows fetch next 2 rows only";
+ sql(sql)
+ .withPreRule(CoreRules.PROJECT_SET_OP_TRANSPOSE)
+ .withRule(CoreRules.SORT_UNION_TRANSPOSE)
+ .checkUnchanged();
+ }
+
@Test void testSortRemovalAllKeysConstant() {
final String sql = "select count(*) as c\n"
+ "from sales.emp\n"
@@ -12551,6 +12593,25 @@ private void
checkNondeterministicFetchPreventsDecorrelation(boolean enableTopDo
.check();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testTopDownGeneralDecorrelateForSubqueryWithOffsetExpression() {
+ final String sql = "select empno from emp where "
+ + "sal > SOME(select sal from emp_b where emp.deptno = emp_b.deptno "
+ + "order by emp_b.sal offset 1 + 1 rows "
+ + "fetch next (1 + 1) rows only)";
+
+ sql(sql)
+ .withRule(
+ CoreRules.FILTER_SUB_QUERY_TO_MARK_CORRELATE,
+ CoreRules.PROJECT_MERGE,
+ CoreRules.PROJECT_REMOVE)
+ .withLateDecorrelate(true)
+ .withTopDownGeneralDecorrelate(true)
+ .check();
+ }
+
@Test void testTopDownGeneralDecorrelateForSubqueryWithCube() {
final String sql = "select empno from emp where "
+ "sal < SOME(select avg(sal) from emp_b where emp.job = emp_b.job
group by cube(deptno))";
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 2f4ba89194..a3cae00b94 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1253,6 +1253,15 @@ public static void checkActualAndReferenceFiles() {
sql(sql).ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetWithExpression() {
+ final String sql =
+ "select empno from emp offset 1 + abs(-2) rows";
+ sql(sql).ok();
+ }
+
@Test void testFetch() {
final String sql = "select empno from emp fetch next 5 rows only";
sql(sql).ok();
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 4088b0556b..eaa4b17a4b 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -10788,6 +10788,25 @@ void testGroupExpressionEquivalenceParams() {
.fails("Windowed aggregate expression is illegal in FETCH clause");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionType() {
+ sql("select name from dept offset ^upper('x')^ rows")
+ .fails("OFFSET expression must have a numeric type; "
+ + "actual type is 'CHAR\\(1\\) NOT NULL'");
+ sql("select name from dept offset ^'x'^ rows")
+ .fails("OFFSET expression must have a numeric type; "
+ + "actual type is 'CHAR\\(1\\) NOT NULL'");
+ sql("select name from dept offset 1.5 rows").ok();
+ sql("select name from dept offset ^deptno^ rows")
+ .fails("OFFSET expression cannot reference table column 'DEPTNO'");
+ sql("select name from dept offset ^cast(null as integer)^ rows")
+ .fails("OFFSET expression evaluated to NULL");
+ sql("select name from dept offset ^row_number() over ()^ rows")
+ .fails("Windowed aggregate expression is illegal in OFFSET clause");
+ }
+
@Test void testRewriteWithOffsetWithoutOrderBy() {
final String sql = "select name from dept offset 2";
final String expected = "SELECT `NAME`\n"
@@ -10804,14 +10823,14 @@ void testGroupExpressionEquivalenceParams() {
@Test void testNegativeFetchOffsetLimit() {
sql("select name from dept limit ^-^1")
.fails("(?s).*Encountered \"-\".*");
- sql("select name from dept offset ^-^1")
- .fails("(?s).*Encountered \"-\".*");
+ sql("select name from dept offset ^-1^")
+ .fails("OFFSET must not be negative");
sql("select name from dept fetch next ^-^1 rows only")
.fails("(?s).*Encountered \"-\".*");
sql("select name from dept order by name limit ^-^1")
.fails("(?s).*Encountered \"-\".*");
- sql("select name from dept order by name offset ^-^1")
- .fails("(?s).*Encountered \"-\".*");
+ sql("select name from dept order by name offset ^-1^")
+ .fails("OFFSET must not be negative");
sql("select name from dept order by name fetch next ^-^1 rows only")
.fails("(?s).*Encountered \"-\".*");
}
diff --git
a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java
b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableMergeUnionTest.java
index 44055f7074..5ca85c0777 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
@@ -27,6 +27,7 @@
import org.junit.jupiter.api.Test;
+import java.math.BigDecimal;
import java.util.function.Consumer;
/**
@@ -105,7 +106,66 @@ class EnumerableMergeUnionTest {
.explainContains("EnumerableLimit(fetch=[+(?0, 1)])\n"
+ " EnumerableMergeUnion(all=[true])\n"
+ " EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
- + "fetch=[+(?0, 1)])\n");
+ + "fetch=[+(?0, 1)])\n")
+ .consumesPreparedStatement(p -> p.setInt(1, 1))
+ .returnsOrdered(
+ "empid=1; name=Bill",
+ "empid=1; name=Bill");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void mergeUnionPushesParameterizedOffsetExpression() {
+ tester(false,
+ new HrSchemaBig(),
+ "select * from (select empid, name from emps "
+ + "union all select empid, name from emps) "
+ + "order by empid offset ? + 1 rows fetch next 2 rows only")
+ .explainContains("EnumerableLimit(offset=[+(?0, 1)], fetch=[2])\n"
+ + " EnumerableMergeUnion(all=[true])\n"
+ + " EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
+ + "fetch=[+(CEIL(+(?0, 1)), 2)])\n")
+ .consumesPreparedStatement(p -> p.setInt(1, 1))
+ .returnsOrdered(
+ "empid=2; name=Eric",
+ "empid=2; name=Eric");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void mergeUnionRoundsOffsetAndFetchSeparatelyWhenPushingLimit() {
+ tester(false,
+ new HrSchemaBig(),
+ "select * from (select empid from emps where empid <= 3 "
+ + "union all select empid from emps where empid >= 40) "
+ + "order by empid offset ? rows fetch next ? rows only")
+ .explainContains("EnumerableLimit(offset=[?0], fetch=[?1])\n"
+ + " EnumerableMergeUnion(all=[true])\n"
+ + " EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
+ + "fetch=[+(CEIL(?0), CEIL(?1))])\n")
+ .consumesPreparedStatement(p -> {
+ p.setBigDecimal(1, new BigDecimal("0.5"));
+ p.setBigDecimal(2, new BigDecimal("0.5"));
+ })
+ .returnsOrdered("empid=2");
+ }
+
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void mergeUnionDoesNotPushNonDeterministicOffset() {
+ tester(false,
+ new HrSchemaBig(),
+ "select * from (select empid, name from emps "
+ + "union all select empid, name from emps) "
+ + "order by empid offset rand_integer(10) rows "
+ + "fetch next 2 rows only")
+ .explainContains("EnumerableLimitSort(sort0=[$0], dir0=[ASC], "
+ + "offset=[RAND_INTEGER(10)], fetch=[2])\n"
+ + " EnumerableMergeUnion(all=[true])\n"
+ + " EnumerableSort(sort0=[$0], dir0=[ASC])\n");
}
@Test void mergeUnionAllOrderByName() {
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 c623e63038..ba303c062b 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -20106,6 +20106,36 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
LogicalSort(sort0=[$0], dir0=[ASC], fetch=[0])
LogicalProject(NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortUnionTransposePushesLiteralOffset">
+ <Resource name="sql">
+ <![CDATA[select a.name from dept a
+union all
+select b.name from dept b
+order by name offset 2 rows fetch next 3 rows only]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC], offset=[2], fetch=[3])
+ 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], offset=[2], fetch=[3])
+ LogicalUnion(all=[true])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[5])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[5])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -20136,6 +20166,36 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(?0, 1)])
LogicalProject(NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortUnionTransposePushesParameterizedOffsetExpression">
+ <Resource name="sql">
+ <![CDATA[select a.name from dept a
+union all
+select b.name from dept b
+order by name offset ? + 1 rows fetch next 2 rows only]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC], offset=[+(?0, 1)], fetch=[2])
+ 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], offset=[+(?0, 1)], fetch=[2])
+ LogicalUnion(all=[true])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(CEIL(+(?0, 1)), 2)])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[+(CEIL(+(?0, 1)), 2)])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -20154,6 +20214,24 @@ LogicalSort(sort0=[$0], dir0=[ASC],
fetch=[RAND_INTEGER(10)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testSortUnionTransposeWithNonDeterministicOffset">
+ <Resource name="sql">
+ <![CDATA[select a.name from dept a
+union all
+select b.name from dept b
+order by name offset rand_integer(10) rows fetch next 2 rows only]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC], offset=[RAND_INTEGER(10)], fetch=[2])
+ LogicalUnion(all=[true])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -21169,6 +21247,45 @@ LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMPNULLABLES]])
LogicalProject(EMPNO=[$0], DEPTNO=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase
name="testTopDownGeneralDecorrelateForSubqueryWithOffsetExpression">
+ <Resource name="sql">
+ <![CDATA[select empno from emp where sal > SOME(select sal from emp_b
where emp.deptno = emp_b.deptno order by emp_b.sal offset 1 + 1 rows fetch next
(1 + 1) rows only)]]>
+ </Resource>
+ <Resource name="planBefore">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[> SOME($5, {
+LogicalSort(sort0=[$0], dir0=[ASC], offset=[+(1, 1)], fetch=[+(1, 1)])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+})], variablesSet=[[$cor0]])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ <Resource name="planMid">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalFilter(condition=[$9])
+ LogicalConditionalCorrelate(correlation=[$cor0], joinType=[left_mark],
requiredColumns=[{7}], condition=[>($5, $9)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalSort(sort0=[$0], dir0=[ASC], offset=[+(1, 1)], fetch=[+(1, 1)])
+ LogicalProject(SAL=[$5])
+ LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
+]]>
+ </Resource>
+ <Resource name="planAfter">
+ <![CDATA[
+LogicalProject(EMPNO=[$0])
+ LogicalJoin(condition=[AND(>($5, $9), IS NOT DISTINCT FROM($7, $10))],
joinType=[semi])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalFilter(condition=[AND(>($2, +(1, 1)), <=($2, +(+(1, 1), +(1, 1))))])
+ LogicalProject(SAL=[$5], DEPTNO=[$7], $f2=[ROW_NUMBER() OVER (PARTITION
BY $7 ORDER BY $5 NULLS LAST)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP_B]])
]]>
</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 a8da739aa9..ea4e81d044 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -6451,6 +6451,18 @@ LogicalSort(offset=[?0], fetch=[?1])
LogicalSort(offset=[?0])
LogicalProject(EMPNO=[$0])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testOffsetWithExpression">
+ <Resource name="sql">
+ <![CDATA[select empno from emp offset 1 + abs(-2) rows]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(offset=[+(1, ABS(-2))])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/offset.iq
b/core/src/test/resources/sql/offset.iq
new file mode 100644
index 0000000000..3f47691321
--- /dev/null
+++ b/core/src/test/resources/sql/offset.iq
@@ -0,0 +1,186 @@
+# offset.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
+
+# OFFSET accepts an arithmetic expression without parentheses.
+select *
+from (values (1), (2), (3), (4)) as t(x)
+offset 1 + abs(-1) rows;
++---+
+| X |
++---+
+| 3 |
+| 4 |
++---+
+(2 rows)
+
+!ok
+
+# OFFSET also accepts a parenthesized scalar expression.
+select *
+from (values (1), (2), (3), (4)) as t(x)
+offset (abs(2)) rows;
++---+
+| X |
++---+
+| 3 |
+| 4 |
++---+
+(2 rows)
+
+!ok
+
+# OFFSET values are not restricted to the BIGINT range.
+select *
+from (values (1), (2), (3), (4)) as t(x)
+offset cast(9223372036854775808 as decimal(20, 0)) + 1 rows;
++---+
+| X |
++---+
++---+
+(0 rows)
+
+!ok
+
+# OFFSET expression cannot be negative.
+select *
+from (values (1), (2), (3)) as t(x)
+offset 0 - 1 rows;
+OFFSET must not be negative
+!error
+
+# OFFSET expression cannot evaluate to NULL.
+select *
+from (values (1), (2), (3)) as t(x)
+offset cast(null as integer) rows;
+OFFSET expression evaluated to NULL
+!error
+
+# OFFSET expression may have a fractional numeric type.
+select *
+from (values (1), (2), (3)) as t(x)
+offset 1.5 rows;
++---+
+| X |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+# OFFSET expression cannot reference input columns.
+select *
+from (values (1), (2), (3)) as t(x)
+offset x rows;
+OFFSET expression cannot reference table column 'X'
+!error
+
+# Parentheses are not required around an OFFSET expression.
+select *
+from (values (1), (2), (3)) as t(x)
+offset 1 + 1 rows;
++---+
+| X |
++---+
+| 3 |
++---+
+(1 row)
+
+!ok
+
+# OFFSET expression works with a table source.
+select deptno, dname
+from dept
+order by deptno
+offset 1 + 1 rows;
++--------+-------------+
+| DEPTNO | DNAME |
++--------+-------------+
+| 30 | Engineering |
+| 40 | Empty |
++--------+-------------+
+(2 rows)
+
+!ok
+
+# OFFSET expression works together with FETCH on a table source.
+select deptno, dname
+from dept
+order by deptno
+offset 1 + 1 rows
+fetch next (1 + 1) rows only;
++--------+-------------+
+| DEPTNO | DNAME |
++--------+-------------+
+| 30 | Engineering |
+| 40 | Empty |
++--------+-------------+
+(2 rows)
+
+!ok
+
+# OFFSET expression may contain a scalar function on a table source.
+select deptno
+from dept
+order by deptno
+offset abs(-2) rows;
++--------+
+| DEPTNO |
++--------+
+| 30 |
+| 40 |
++--------+
+(2 rows)
+
+!ok
+
+# OFFSET expression cannot reference columns of a table source.
+select deptno, dname
+from dept
+order by deptno
+offset deptno rows;
+OFFSET expression cannot reference table column 'DEPTNO'
+!error
+
+# OFFSET expression cannot reference columns even inside a larger expression.
+select deptno, dname
+from dept
+order by deptno
+offset deptno + 1 rows;
+OFFSET expression cannot reference table column 'DEPTNO'
+!error
+
+# OFFSET expression may be zero on a table source.
+select deptno
+from dept
+order by deptno
+offset 2 - 2 rows;
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
+| 30 |
+| 40 |
++--------+
+(4 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 39d434f23a..0be7fb16ef 100644
--- a/server/src/test/java/org/apache/calcite/test/ServerTest.java
+++ b/server/src/test/java/org/apache/calcite/test/ServerTest.java
@@ -489,6 +489,31 @@ static Connection connect() throws SQLException {
}
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7662";>[CALCITE-7662]
+ * Add expression support for OFFSET</a>. */
+ @Test void testOffsetExpressionCannotReferenceInputColumn() 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));
+ }
+
+ for (String offset : new String[] {"id", "(id)", "1 + id"}) {
+ final SQLException e =
+ assertThrows(
+ SQLException.class, () -> s.executeQuery("select * from person
"
+ + "offset " + offset + " rows"));
+ assertThat(e.getMessage(),
+ containsString("OFFSET 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 fda50d5bfc..befae3b624 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -193,8 +193,8 @@ ## Grammar
}
[ ORDER BY { ALL [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] | orderItem
[, orderItem]* } ]
[ LIMIT [ start, ] { count | ALL } ]
- [ OFFSET start { ROW | ROWS } ]
- [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
+ [ OFFSET { start | expression } { ROW | ROWS } ]
+ [ FETCH { FIRST | NEXT } [ count | '(' expression ')' ] { ROW | ROWS }
ONLY ]
withItem:
name
@@ -215,7 +215,7 @@ ## Grammar
[ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]
[ QUALIFY booleanExpression ]
[ ORDER BY orderItem [, orderItem ]* ]
- [ LIMIT expression [ OFFSET expression ] ]
+ [ LIMIT expression [ OFFSET { start | expression } ] ]
The optional, non-standard `BY` clause groups and orders the query by
the specified expressions, and automatically adds them to the SELECT list
@@ -429,11 +429,13 @@ ## Grammar
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.
+an unsigned numeric literal or a dynamic parameter whose value is numeric. An
+OFFSET clause may contain an unsigned numeric literal, a dynamic parameter
whose
+value is numeric, or a scalar expression with optional parentheses. The *count*
+in a FETCH clause may also be a scalar expression, but it must be enclosed in
+parentheses. An OFFSET expression or 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 3fbe5af9be..5e4271e4c8 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,7 +4104,16 @@ void checkPeriodPredicate(Checker checker) {
+ "FROM `FOO`\n"
+ "OFFSET ? ROWS\n"
+ "FETCH NEXT ? ROWS ONLY");
- // CALCITE-7592: Arithmetic and scalar expressions are allowed within
parentheses.
+ // Arithmetic and scalar expressions are allowed within parentheses.
+ sql("select a from foo offset 1 + abs(-2) rows")
+ .ok("SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET 1 + ABS(-2) ROWS");
+ // Parentheses remain optional in OFFSET.
+ sql("select a from foo offset (1 + abs(-2)) rows")
+ .ok("SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET 1 + ABS(-2) ROWS");
sql("select a from foo fetch next (1 + abs(-2)) rows only")
.ok("SELECT `A`\n"
+ "FROM `FOO`\n"
@@ -4120,7 +4129,9 @@ void checkPeriodPredicate(Checker checker) {
// 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
+ // Subqueries are not allowed in OFFSET or FETCH
+ sql("select a from foo offset ^(^select 2) rows")
+ .fails("Query expression encountered in illegal context");
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")
@@ -4137,6 +4148,12 @@ void checkPeriodPredicate(Checker checker) {
* SQL:2008.
*/
@Test void testLimit() {
+ sql("select a from foo order by b, c limit 2 offset 1 + abs(-2)")
+ .ok("SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "ORDER BY `B`, `C`\n"
+ + "OFFSET 1 + ABS(-2) ROWS\n"
+ + "FETCH NEXT 2 ROWS ONLY");
sql("select a from foo order by b, c limit 2 offset 1")
.ok("SELECT `A`\n"
+ "FROM `FOO`\n"
