You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/11/27 19:04:10 UTC
[5/5] calcite git commit: [CALCITE-2061] Dynamic parameters in OFFSET,
FETCH and LIMIT clauses (Enrico Olivelli)
[CALCITE-2061] Dynamic parameters in OFFSET, FETCH and LIMIT clauses (Enrico Olivelli)
Fix how dynamic parameters are un-parsed (Julian Hyde)
Close apache/calcite#569
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/a5d520df
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/a5d520df
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/a5d520df
Branch: refs/heads/master
Commit: a5d520df76602d25ed66627f08f5e0db4d048a77
Parents: 238b322
Author: Enrico Olivelli <eo...@apache.org>
Authored: Tue Nov 21 08:59:20 2017 +0100
Committer: Julian Hyde <jh...@apache.org>
Committed: Sun Nov 26 19:42:36 2017 -0800
----------------------------------------------------------------------
core/src/main/codegen/templates/Parser.jj | 27 +++++++++--
.../adapter/enumerable/EnumerableLimit.java | 19 +++++++-
.../calcite/rel/metadata/RelMdRowCount.java | 13 +++++
.../calcite/rel/rules/PruneEmptyRules.java | 2 +
.../org/apache/calcite/sql/SqlDynamicParam.java | 2 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 24 ++++++++--
.../apache/calcite/sql2rel/RelFieldTrimmer.java | 7 +++
.../calcite/sql2rel/SqlToRelConverter.java | 3 +-
.../rel/rel2sql/RelToSqlConverterTest.java | 2 +-
.../calcite/sql/parser/SqlParserTest.java | 7 +++
.../java/org/apache/calcite/test/JdbcTest.java | 41 ++++++++++++++++
.../calcite/test/SqlToRelConverterTest.java | 22 +++++++++
.../apache/calcite/test/SqlValidatorTest.java | 11 +++++
.../calcite/test/SqlToRelConverterTest.xml | 50 ++++++++++++++++++++
site/_docs/reference.md | 3 ++
15 files changed, 220 insertions(+), 13 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/codegen/templates/Parser.jj
----------------------------------------------------------------------
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index fd81f7b..881ab11 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -609,14 +609,14 @@ SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
<LIMIT>
(
// MySQL-style syntax. "LIMIT start, count"
- start = UnsignedNumericLiteral()
- <COMMA> count = UnsignedNumericLiteral() {
+ start = UnsignedNumericLiteralOrParam()
+ <COMMA> count = UnsignedNumericLiteralOrParam() {
if (!this.conformance.isLimitStartCountAllowed()) {
throw new ParseException(RESOURCE.limitStartCountNotAllowed().str());
}
}
|
- count = UnsignedNumericLiteral()
+ count = UnsignedNumericLiteralOrParam()
|
<ALL>
)
@@ -625,12 +625,13 @@ SqlNode OrderedQueryOrExpr(ExprContext exprContext) :
// ROW or ROWS is required in SQL:2008 but we make it optional
// because it is not present in Postgres-style syntax.
// If you specify both LIMIT start and OFFSET, OFFSET wins.
- <OFFSET> start = UnsignedNumericLiteral() [ <ROW> | <ROWS> ]
+ <OFFSET> start = UnsignedNumericLiteralOrParam() [ <ROW> | <ROWS> ]
]
[
// SQL:2008-style syntax. "OFFSET ... FETCH ...".
// If you specify both LIMIT and FETCH, FETCH wins.
- <FETCH> ( <FIRST> | <NEXT> ) count = UnsignedNumericLiteral() ( <ROW> | <ROWS> ) <ONLY>
+ <FETCH> ( <FIRST> | <NEXT> ) count = UnsignedNumericLiteralOrParam()
+ ( <ROW> | <ROWS> ) <ONLY>
]
{
if (orderBy != null || start != null || count != null) {
@@ -3347,6 +3348,22 @@ SqlCollation CollateClause() :
}
/**
+ * Numeric literal or parameter; used in LIMIT, OFFSET and FETCH clauses.
+ */
+SqlNode UnsignedNumericLiteralOrParam() :
+{
+ final SqlNode e;
+}
+{
+ (
+ e = UnsignedNumericLiteral()
+ |
+ e = DynamicParam()
+ )
+ { return e; }
+}
+
+/**
* Parses an atomic row expression.
*/
SqlNode AtomicRowExpression() :
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableLimit.java
----------------------------------------------------------------------
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 0e00dda..577a5a4 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
@@ -16,6 +16,7 @@
*/
package org.apache.calcite.adapter.enumerable;
+import org.apache.calcite.DataContext;
import org.apache.calcite.linq4j.tree.BlockBuilder;
import org.apache.calcite.linq4j.tree.Expression;
import org.apache.calcite.linq4j.tree.Expressions;
@@ -31,6 +32,7 @@ import org.apache.calcite.rel.SingleRel;
import org.apache.calcite.rel.metadata.RelMdCollation;
import org.apache.calcite.rel.metadata.RelMdDistribution;
import org.apache.calcite.rel.metadata.RelMetadataQuery;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.BuiltInMethod;
@@ -117,7 +119,7 @@ public class EnumerableLimit extends SingleRel implements EnumerableRel {
Expressions.call(
v,
BuiltInMethod.SKIP.method,
- Expressions.constant(RexLiteral.intValue(offset))));
+ getExpression(offset)));
}
if (fetch != null) {
v = builder.append(
@@ -125,7 +127,7 @@ public class EnumerableLimit extends SingleRel implements EnumerableRel {
Expressions.call(
v,
BuiltInMethod.TAKE.method,
- Expressions.constant(RexLiteral.intValue(fetch))));
+ getExpression(fetch)));
}
builder.add(
@@ -134,6 +136,19 @@ public class EnumerableLimit extends SingleRel implements EnumerableRel {
v));
return implementor.result(physType, builder.toBlock());
}
+
+ private static Expression getExpression(RexNode offset) {
+ if (offset instanceof RexDynamicParam) {
+ final RexDynamicParam param = (RexDynamicParam) offset;
+ return Expressions.convert_(
+ Expressions.call(DataContext.ROOT,
+ BuiltInMethod.DATA_CONTEXT_GET.method,
+ Expressions.constant("?" + param.getIndex())),
+ Integer.class);
+ } else {
+ return Expressions.constant(RexLiteral.intValue(offset));
+ }
+ }
}
// End EnumerableLimit.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/rel/metadata/RelMdRowCount.java
----------------------------------------------------------------------
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 c9113a1..b5f035b 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
@@ -32,6 +32,7 @@ import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Union;
import org.apache.calcite.rel.core.Values;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
import org.apache.calcite.util.Bug;
@@ -137,10 +138,16 @@ public class RelMdRowCount
if (rowCount == null) {
return null;
}
+ if (rel.offset instanceof RexDynamicParam) {
+ return rowCount;
+ }
final int offset = rel.offset == null ? 0 : RexLiteral.intValue(rel.offset);
rowCount = Math.max(rowCount - offset, 0D);
if (rel.fetch != null) {
+ if (rel.fetch instanceof RexDynamicParam) {
+ return rowCount;
+ }
final int limit = RexLiteral.intValue(rel.fetch);
if (limit < rowCount) {
return (double) limit;
@@ -154,10 +161,16 @@ public class RelMdRowCount
if (rowCount == null) {
return null;
}
+ if (rel.offset instanceof RexDynamicParam) {
+ return rowCount;
+ }
final int offset = rel.offset == null ? 0 : RexLiteral.intValue(rel.offset);
rowCount = Math.max(rowCount - offset, 0D);
if (rel.fetch != null) {
+ if (rel.fetch instanceof RexDynamicParam) {
+ return rowCount;
+ }
final int limit = RexLiteral.intValue(rel.fetch);
if (limit < rowCount) {
return (double) limit;
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/rel/rules/PruneEmptyRules.java
----------------------------------------------------------------------
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 51aefe1..f6a89ad 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
@@ -32,6 +32,7 @@ import org.apache.calcite.rel.logical.LogicalIntersect;
import org.apache.calcite.rel.logical.LogicalMinus;
import org.apache.calcite.rel.logical.LogicalUnion;
import org.apache.calcite.rel.logical.LogicalValues;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.tools.RelBuilder;
import org.apache.calcite.tools.RelBuilderFactory;
@@ -247,6 +248,7 @@ public abstract class PruneEmptyRules {
@Override public void onMatch(RelOptRuleCall call) {
Sort sort = call.rel(0);
if (sort.fetch != null
+ && !(sort.fetch instanceof RexDynamicParam)
&& RexLiteral.intValue(sort.fetch) == 0) {
call.transformTo(call.builder().push(sort).empty().build());
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/sql/SqlDynamicParam.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlDynamicParam.java b/core/src/main/java/org/apache/calcite/sql/SqlDynamicParam.java
index 7db5fd7..db2a6b0 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlDynamicParam.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlDynamicParam.java
@@ -62,7 +62,7 @@ public class SqlDynamicParam extends SqlNode {
int leftPrec,
int rightPrec) {
writer.print("?");
- writer.setNeedWhitespace(false);
+ writer.setNeedWhitespace(true);
}
public void validate(SqlValidator validator, SqlValidatorScope scope) {
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
----------------------------------------------------------------------
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 cb134cf..2e66e54 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
@@ -1063,6 +1063,17 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
}
}
+ private void handleOffsetFetch(SqlNode offset, SqlNode fetch) {
+ if (offset instanceof SqlDynamicParam) {
+ setValidatedNodeType(offset,
+ typeFactory.createSqlType(SqlTypeName.INTEGER));
+ }
+ if (fetch instanceof SqlDynamicParam) {
+ setValidatedNodeType(fetch,
+ typeFactory.createSqlType(SqlTypeName.INTEGER));
+ }
+ }
+
/**
* Performs expression rewrites which are always used unconditionally. These
* rewrites massage the expression tree into a standard form so that the
@@ -1160,6 +1171,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
case ORDER_BY: {
SqlOrderBy orderBy = (SqlOrderBy) node;
+ handleOffsetFetch(orderBy.offset, orderBy.fetch);
if (orderBy.query instanceof SqlSelect) {
SqlSelect select = (SqlSelect) orderBy.query;
@@ -3144,7 +3156,6 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
SqlSelect select,
RelDataType targetRowType) {
assert targetRowType != null;
-
// Namespace is either a select namespace or a wrapper around one.
final SelectNamespace ns =
getNamespace(select).unwrap(SelectNamespace.class);
@@ -3208,6 +3219,7 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
validateGroupClause(select);
validateHavingClause(select);
validateWindowClause(select);
+ handleOffsetFetch(select.getOffset(), select.getFetch());
// Validate the SELECT clause late, because a select item might
// depend on the GROUP BY list, or the window function might reference
@@ -5222,11 +5234,17 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
// NOTE: We assume that bind variables occur in depth-first tree
// traversal in the same order that they occurred in the SQL text.
final List<RelDataType> types = new ArrayList<>();
+ // NOTE: but parameters on fetch/offset would be counted twice
+ // as they are counted in the SqlOrderBy call and the inner SqlSelect call
+ final Set<SqlNode> alreadyVisited = new HashSet<>();
sqlQuery.accept(
new SqlShuttle() {
+
@Override public SqlNode visit(SqlDynamicParam param) {
- RelDataType type = getValidatedNodeType(param);
- types.add(type);
+ if (alreadyVisited.add(param)) {
+ RelDataType type = getValidatedNodeType(param);
+ types.add(type);
+ }
return param;
}
});
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
index 2d7ec59..db6588f 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelFieldTrimmer.java
@@ -43,6 +43,7 @@ import org.apache.calcite.rel.type.RelDataTypeField;
import org.apache.calcite.rel.type.RelDataTypeImpl;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexDynamicParam;
import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexLiteral;
import org.apache.calcite.rex.RexNode;
@@ -527,6 +528,12 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
return result(sort, Mappings.createIdentity(fieldCount));
}
+ // leave the Sort unchanged in case we have dynamic limits
+ if (sort.offset instanceof RexDynamicParam
+ || sort.fetch instanceof RexDynamicParam) {
+ return result(sort, inputMapping);
+ }
+
relBuilder.push(newInput);
final int offset =
sort.offset == null ? 0 : RexLiteral.intValue(sort.offset);
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 24fd111..0672dbe 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -799,7 +799,8 @@ public class SqlToRelConverter {
|| select.getOrderList().getList().isEmpty()) {
assert collation.getFieldCollations().isEmpty();
if ((offset == null
- || ((SqlLiteral) offset).bigDecimalValue().equals(BigDecimal.ZERO))
+ || (offset instanceof SqlLiteral
+ && ((SqlLiteral) offset).bigDecimalValue().equals(BigDecimal.ZERO)))
&& fetch == null) {
return;
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
----------------------------------------------------------------------
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 d89df0b..e194456 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
@@ -592,7 +592,7 @@ public class RelToSqlConverterTest {
+ "AND ? >= \"shelf_width\"";
final String expected = "SELECT *\n"
+ "FROM \"foodmart\".\"product\"\n"
- + "WHERE \"product_id\" = ?"
+ + "WHERE \"product_id\" = ? "
+ "AND ? >= \"shelf_width\"";
sql(query).ok(expected);
}
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
index 90603db..de9ca4d 100644
--- a/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
+++ b/core/src/test/java/org/apache/calcite/sql/parser/SqlParserTest.java
@@ -2506,6 +2506,13 @@ public class SqlParserTest {
+ "FROM `FOO`\n"
+ "OFFSET 1 ROWS\n"
+ "FETCH NEXT 3 ROWS ONLY");
+ // OFFSET and FETCH, with dynamic parameters
+ check(
+ "select a from foo offset ? row fetch next ? rows only",
+ "SELECT `A`\n"
+ + "FROM `FOO`\n"
+ + "OFFSET ? ROWS\n"
+ + "FETCH NEXT ? ROWS ONLY");
// missing ROWS after FETCH
checkFails(
"select a from foo offset 1 fetch next 3 ^only^",
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/java/org/apache/calcite/test/JdbcTest.java
----------------------------------------------------------------------
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 7d6a479..f269ba6 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcTest.java
@@ -100,6 +100,7 @@ import com.google.common.collect.ImmutableList;
import com.google.common.collect.LinkedListMultimap;
import com.google.common.collect.Multimap;
+import org.hamcrest.Matcher;
import org.hsqldb.jdbcDriver;
import org.junit.Ignore;
@@ -116,12 +117,14 @@ import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
+import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
+import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
@@ -4889,6 +4892,44 @@ public class JdbcTest {
});
}
+ /** Test case for
+ * <a href="https://issues.apache.org/jira/browse/CALCITE-2061">[CALCITE-2061]
+ * Dynamic parameters in offset/fetch</a>. */
+ @Test public void testPreparedOffsetFetch() throws Exception {
+ checkPreparedOffsetFetch(0, 0, Matchers.returnsUnordered());
+ checkPreparedOffsetFetch(100, 4, Matchers.returnsUnordered());
+ checkPreparedOffsetFetch(3, 4,
+ Matchers.returnsUnordered("name=Eric"));
+ }
+
+ private void checkPreparedOffsetFetch(final int offset, final int fetch,
+ final Matcher<? super ResultSet> matcher) throws Exception {
+ CalciteAssert.hr()
+ .doWithConnection(
+ new Function<CalciteConnection, Object>() {
+ public Object apply(CalciteConnection connection) {
+ final String sql = "select \"name\"\n"
+ + "from \"hr\".\"emps\"\n"
+ + "order by \"empid\" offset ? fetch next ? rows only";
+ try (final PreparedStatement p =
+ connection.prepareStatement(sql)) {
+ final ParameterMetaData pmd = p.getParameterMetaData();
+ assertThat(pmd.getParameterCount(), is(2));
+ assertThat(pmd.getParameterType(1), is(Types.INTEGER));
+ assertThat(pmd.getParameterType(2), is(Types.INTEGER));
+ p.setInt(1, offset);
+ p.setInt(2, fetch);
+ try (final ResultSet r = p.executeQuery()) {
+ assertThat(r, matcher);
+ return null;
+ }
+ } catch (SQLException e) {
+ throw new RuntimeException(e);
+ }
+ }
+ });
+ }
+
/** Tests a JDBC connection that provides a model (a single schema based on
* a JDBC database). */
@Test public void testModel() {
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
----------------------------------------------------------------------
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 dd46878..5424041 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -669,22 +669,44 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ @Test public void testOrderOffsetFetchWithDynamicParameter() {
+ final String sql = "select empno from emp\n"
+ + "order by empno offset ? rows fetch next ? rows only";
+ sql(sql).ok();
+ }
+
@Test public void testOffsetFetch() {
final String sql = "select empno from emp\n"
+ "offset 10 rows fetch next 5 rows only";
sql(sql).ok();
}
+ @Test public void testOffsetFetchWithDynamicParameter() {
+ final String sql = "select empno from emp\n"
+ + "offset ? rows fetch next ? rows only";
+ sql(sql).ok();
+ }
+
@Test public void testOffset() {
final String sql = "select empno from emp offset 10 rows";
sql(sql).ok();
}
+ @Test public void testOffsetWithDynamicParameter() {
+ final String sql = "select empno from emp offset ? rows";
+ sql(sql).ok();
+ }
+
@Test public void testFetch() {
final String sql = "select empno from emp fetch next 5 rows only";
sql(sql).ok();
}
+ @Test public void testFetchWithDynamicParameter() {
+ final String sql = "select empno from emp fetch next ? 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
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
----------------------------------------------------------------------
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 c7378a3..c8b9d46 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -7918,6 +7918,17 @@ public class SqlValidatorTest extends SqlValidatorTestCase {
tester.checkRewrite(validator, sql, expected);
}
+ @Test public void testRewriteWithLimitWithDynamicParameters() {
+ SqlValidator validator = tester.getValidator();
+ validator.setIdentifierExpansion(false);
+ final String sql = "select name from dept offset ? rows fetch next ? rows only";
+ final String expected = "SELECT `NAME`\n"
+ + "FROM `DEPT`\n"
+ + "OFFSET ? ROWS\n"
+ + "FETCH NEXT ? ROWS ONLY";
+ tester.checkRewrite(validator, sql, expected);
+ }
+
@Test public void testRewriteWithOffsetWithoutOrderBy() {
SqlValidator validator = tester.getValidator();
validator.setIdentifierExpansion(false);
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
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 34037ba..18f4494 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1461,6 +1461,19 @@ LogicalSort(sort0=[$0], dir0=[ASC], offset=[10], fetch=[5])
]]>
</Resource>
</TestCase>
+ <TestCase name="testOrderOffsetFetchWithDynamicParameter">
+ <Resource name="sql">
+ <![CDATA[select empno from emp
+order by empno offset ? rows fetch next ? rows only]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(sort0=[$0], dir0=[ASC], offset=[?0], fetch=[?1])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testFetch">
<Resource name="sql">
<![CDATA[select empno from emp fetch next 5 rows only]]>
@@ -1473,6 +1486,18 @@ LogicalSort(fetch=[5])
]]>
</Resource>
</TestCase>
+ <TestCase name="testFetchWithDynamicParameter">
+ <Resource name="sql">
+ <![CDATA[select empno from emp fetch next ? rows only]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(fetch=[?0])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testOffsetFetch">
<Resource name="sql">
<![CDATA[select empno from emp
@@ -1486,6 +1511,19 @@ LogicalSort(offset=[10], fetch=[5])
]]>
</Resource>
</TestCase>
+<TestCase name="testOffsetFetchWithDynamicParameter">
+ <Resource name="sql">
+ <![CDATA[select empno from emp
+offset ? rows fetch next ? rows only]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(offset=[?0], fetch=[?1])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testOffset">
<Resource name="sql">
<![CDATA[select empno from emp offset 10 rows]]>
@@ -1498,6 +1536,18 @@ LogicalSort(offset=[10])
]]>
</Resource>
</TestCase>
+ <TestCase name="testOffsetWithDynamicParameter">
+ <Resource name="sql">
+ <![CDATA[select empno from emp offset ? rows]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalSort(offset=[?0])
+ LogicalProject(EMPNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testMultiAnd">
<Resource name="sql">
<![CDATA[select * from emp
http://git-wip-us.apache.org/repos/asf/calcite/blob/a5d520df/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index ce85272..f9b8b14 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -251,6 +251,9 @@ in those same conformance levels, any *column* in *insert* may be replaced by
In *orderItem*, if *expression* is a positive integer *n*, it denotes
the <em>n</em>th item in the SELECT clause.
+In *query*, *count* and *start* may each be either an unsigned integer literal
+or a dynamic parameter whose value is an integer.
+
An aggregate query is a query that contains a GROUP BY or a HAVING
clause, or aggregate functions in the SELECT clause. In the SELECT,
HAVING and ORDER BY clauses of an aggregate query, all expressions