[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 <[email protected]> Authored: Tue Nov 21 08:59:20 2017 +0100 Committer: Julian Hyde <[email protected]> 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"> <