This is an automated email from the ASF dual-hosted git repository. jhyde pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 02a4ced35cb1f9fdc18b16528db1f922365a33c2 Author: Julian Hyde <[email protected]> AuthorDate: Fri Sep 13 20:09:57 2019 -0700 [CALCITE-3344] In JDBC adapter, generate "SELECT TOP(n)" for MSSQL 2008 and earlier, and for Sybase ASE --- .../java/org/apache/calcite/sql/SqlDialect.java | 15 ++++++++ .../org/apache/calcite/sql/SqlSelectOperator.java | 1 + .../java/org/apache/calcite/sql/SqlWriter.java | 7 ++++ .../calcite/sql/dialect/MssqlSqlDialect.java | 31 ++++++++++++++++ .../calcite/sql/dialect/SybaseSqlDialect.java | 20 ++++++++++ .../apache/calcite/sql/pretty/SqlPrettyWriter.java | 7 ++++ .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 43 +++++++++++++++++++++- 7 files changed, 123 insertions(+), 1 deletion(-) 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 7d802b4..bfdac48 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlDialect.java @@ -849,6 +849,21 @@ public class SqlDialect { unparseFetchUsingAnsi(writer, offset, fetch); } + /** + * Converts a fetch into a "SELECT TOP(fetch)". + * + * <p>A dialect that uses "TOP" syntax should override this method to print + * "TOP(fetch)", and override {@link #unparseOffsetFetch} to no-op. + * + * <p>The default implementation of this method is no-op. + * + * @param writer Writer + * @param offset Number of rows to skip before emitting, or null + * @param fetch Number of rows to fetch, or null + */ + public void unparseTopN(SqlWriter writer, SqlNode offset, SqlNode fetch) { + } + /** Unparses offset/fetch using ANSI standard "OFFSET offset ROWS FETCH NEXT * fetch ROWS ONLY" syntax. */ protected final void unparseFetchUsingAnsi(SqlWriter writer, SqlNode offset, diff --git a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java index b5b4a0b..80961d4 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlSelectOperator.java @@ -143,6 +143,7 @@ public class SqlSelectOperator extends SqlOperator { final SqlNode keyword = select.keywordList.get(i); keyword.unparse(writer, 0, 0); } + writer.topN(select.fetch, select.offset); SqlNode selectClause = select.selectList; if (selectClause == null) { selectClause = SqlIdentifier.star(SqlParserPos.ZERO); diff --git a/core/src/main/java/org/apache/calcite/sql/SqlWriter.java b/core/src/main/java/org/apache/calcite/sql/SqlWriter.java index 46c4e76..804bec7 100644 --- a/core/src/main/java/org/apache/calcite/sql/SqlWriter.java +++ b/core/src/main/java/org/apache/calcite/sql/SqlWriter.java @@ -340,6 +340,13 @@ public interface SqlWriter { void fetchOffset(SqlNode fetch, SqlNode offset); /** + * Prints the TOP(n) clause. + * + * @see #fetchOffset + */ + void topN(SqlNode fetch, SqlNode offset); + + /** * Prints a new line, and indents. */ void newlineAndIndent(); diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java index 47b647b..0eaf814 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/MssqlSqlDialect.java @@ -49,9 +49,40 @@ public class MssqlSqlDialect extends SqlDialect { ReturnTypes.ARG0_NULLABLE_VARYING, null, null, SqlFunctionCategory.STRING); + /** Whether to generate "SELECT TOP(fetch)" rather than + * "SELECT ... FETCH NEXT fetch ROWS ONLY". */ + private final boolean top; + /** Creates a MssqlSqlDialect. */ public MssqlSqlDialect(Context context) { super(context); + // MSSQL 2008 (version 10) and earlier only supports TOP + // MSSQL 2012 (version 11) and higher supports OFFSET and FETCH + top = context.databaseMajorVersion() < 11; + } + + @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset, + SqlNode fetch) { + if (!top) { + super.unparseOffsetFetch(writer, offset, fetch); + } + } + + @Override public void unparseTopN(SqlWriter writer, SqlNode offset, + SqlNode fetch) { + if (top) { + // Per Microsoft: + // "For backward compatibility, the parentheses are optional in SELECT + // statements. We recommend that you always use parentheses for TOP in + // SELECT statements. Doing so provides consistency with its required + // use in INSERT, UPDATE, MERGE, and DELETE statements." + // + // Note that "fetch" is ignored. + writer.keyword("TOP"); + writer.keyword("("); + fetch.unparse(writer, -1, -1); + writer.keyword(")"); + } } @Override public void unparseDateTimeLiteral(SqlWriter writer, diff --git a/core/src/main/java/org/apache/calcite/sql/dialect/SybaseSqlDialect.java b/core/src/main/java/org/apache/calcite/sql/dialect/SybaseSqlDialect.java index 0b50e4b..c68b678 100644 --- a/core/src/main/java/org/apache/calcite/sql/dialect/SybaseSqlDialect.java +++ b/core/src/main/java/org/apache/calcite/sql/dialect/SybaseSqlDialect.java @@ -17,6 +17,8 @@ package org.apache.calcite.sql.dialect; import org.apache.calcite.sql.SqlDialect; +import org.apache.calcite.sql.SqlNode; +import org.apache.calcite.sql.SqlWriter; /** * A <code>SqlDialect</code> implementation for the Sybase database. @@ -30,6 +32,24 @@ public class SybaseSqlDialect extends SqlDialect { public SybaseSqlDialect(Context context) { super(context); } + + @Override public void unparseOffsetFetch(SqlWriter writer, SqlNode offset, + SqlNode fetch) { + // No-op; see unparseTopN. + // Sybase uses "SELECT TOP (n)" rather than "FETCH NEXT n ROWS". + } + + @Override public void unparseTopN(SqlWriter writer, SqlNode offset, + SqlNode fetch) { + // Parentheses are not required, but we use them to be consistent with + // Microsoft SQL Server, which recommends them but does not require them. + // + // Note that "fetch" is ignored. + writer.keyword("TOP"); + writer.keyword("("); + fetch.unparse(writer, -1, -1); + writer.keyword(")"); + } } // End SybaseSqlDialect.java diff --git a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java index a6e5a86..a3a622c 100644 --- a/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java +++ b/core/src/main/java/org/apache/calcite/sql/pretty/SqlPrettyWriter.java @@ -925,6 +925,13 @@ public class SqlPrettyWriter implements SqlWriter { dialect.unparseOffsetFetch(this, offset, fetch); } + public void topN(SqlNode fetch, SqlNode offset) { + if (fetch == null && offset == null) { + return; + } + dialect.unparseTopN(this, offset, fetch); + } + public Frame startFunCall(String funName) { keyword(funName); setNeedWhitespace(false); 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 8d67024..3782504 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 @@ -43,6 +43,7 @@ import org.apache.calcite.sql.SqlWriter; import org.apache.calcite.sql.dialect.CalciteSqlDialect; import org.apache.calcite.sql.dialect.HiveSqlDialect; import org.apache.calcite.sql.dialect.JethroDataSqlDialect; +import org.apache.calcite.sql.dialect.MssqlSqlDialect; import org.apache.calcite.sql.dialect.MysqlSqlDialect; import org.apache.calcite.sql.dialect.OracleSqlDialect; import org.apache.calcite.sql.dialect.PostgresqlSqlDialect; @@ -1325,6 +1326,31 @@ public class RelToSqlConverterTest { sql(query).ok(expected); } + @Test public void testSelectQueryWithFetchClause() { + String query = "select \"product_id\"\n" + + "from \"product\"\n" + + "order by \"product_id\" fetch next 100 rows only"; + final String expected = "SELECT \"product_id\"\n" + + "FROM \"foodmart\".\"product\"\n" + + "ORDER BY \"product_id\"\n" + + "FETCH NEXT 100 ROWS ONLY"; + final String expectedMssql10 = "SELECT TOP (100) [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY [product_id]"; + final String expectedMssql = "SELECT [product_id]\n" + + "FROM [foodmart].[product]\n" + + "ORDER BY [product_id]\n" + + "FETCH NEXT 100 ROWS ONLY"; + final String expectedSybase = "SELECT TOP (100) product_id\n" + + "FROM foodmart.product\n" + + "ORDER BY product_id"; + sql(query).ok(expected) + .withMssql(10).ok(expectedMssql10) + .withMssql(11).ok(expectedMssql) + .withMssql(14).ok(expectedMssql) + .withSybase().ok(expectedSybase); + } + @Test public void testSelectQueryComplex() { String query = "select count(*), \"units_per_case\" from \"product\" where \"cases_per_pallet\" > 100 " @@ -3932,7 +3958,18 @@ public class RelToSqlConverterTest { } Sql withMssql() { - return dialect(SqlDialect.DatabaseProduct.MSSQL.getDialect()); + return withMssql(14); // MSSQL 2008 = 10.0, 2012 = 11.0, 2017 = 14.0 + } + + Sql withMssql(int majorVersion) { + final SqlDialect mssqlDialect = DatabaseProduct.MSSQL.getDialect(); + return dialect( + new MssqlSqlDialect(SqlDialect.EMPTY_CONTEXT + .withDatabaseProduct(DatabaseProduct.MSSQL) + .withDatabaseMajorVersion(majorVersion) + .withIdentifierQuoteString(mssqlDialect.quoteIdentifier("") + .substring(0, 1)) + .withNullCollation(mssqlDialect.getNullCollation()))); } Sql withMysql() { @@ -3966,6 +4003,10 @@ public class RelToSqlConverterTest { return dialect(DatabaseProduct.SNOWFLAKE.getDialect()); } + Sql withSybase() { + return dialect(DatabaseProduct.SYBASE.getDialect()); + } + Sql withVertica() { return dialect(SqlDialect.DatabaseProduct.VERTICA.getDialect()); }
