This is an automated email from the ASF dual-hosted git repository. liyafan pushed a commit to branch master in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 82be1ec43f332b65148a3985cd5862612b609d40 Author: Volodymyr Vysotskyi <[email protected]> AuthorDate: Fri Dec 31 21:27:40 2021 +0200 [CALCITE-4968] Use TOP N for MsSQL instead of FETCH without OFFSET --- .../calcite/sql/dialect/MssqlSqlDialect.java | 6 +++--- .../calcite/sql/dialect/SybaseSqlDialect.java | 7 +++++-- .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 22 +++++++++++++++++++--- 3 files changed, 27 insertions(+), 8 deletions(-) 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 9630916..b59bb29 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 @@ -116,21 +116,21 @@ public class MssqlSqlDialect extends SqlDialect { @Override public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch) { - if (!top) { + if (!top && offset != null) { super.unparseOffsetFetch(writer, offset, fetch); } } @Override public void unparseTopN(SqlWriter writer, @Nullable SqlNode offset, @Nullable SqlNode fetch) { - if (top) { + if (top || offset == null) { // 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. + // Note that "offset" is ignored. writer.keyword("TOP"); writer.keyword("("); requireNonNull(fetch, "fetch"); 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 b611d07..f62e1d8 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 @@ -48,12 +48,15 @@ public class SybaseSqlDialect extends SqlDialect { @Nullable 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("("); requireNonNull(fetch, "fetch"); fetch.unparse(writer, -1, -1); writer.keyword(")"); + if (offset != null) { + writer.keyword("START"); + writer.keyword("AT"); + offset.unparse(writer, -1, -1); + } } } 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 89f48a6..b651de7 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 @@ -2652,10 +2652,9 @@ class RelToSqlConverterTest { final String expectedMssql10 = "SELECT TOP (100) [product_id]\n" + "FROM [foodmart].[product]\n" + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]"; - final String expectedMssql = "SELECT [product_id]\n" + final String expectedMssql = "SELECT TOP (100) [product_id]\n" + "FROM [foodmart].[product]\n" - + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]\n" - + "FETCH NEXT 100 ROWS ONLY"; + + "ORDER BY CASE WHEN [product_id] IS NULL THEN 1 ELSE 0 END, [product_id]"; final String expectedSybase = "SELECT TOP (100) product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id"; @@ -3588,6 +3587,23 @@ class RelToSqlConverterTest { .withMssql().ok(expected); } + @Test void testFetchMssql() { + String query = "SELECT * FROM \"employee\" LIMIT 1"; + String expected = "SELECT TOP (1) *\nFROM [foodmart].[employee]"; + sql(query) + .withMssql().ok(expected); + } + + @Test void testFetchOffset() { + String query = "SELECT * FROM \"employee\" LIMIT 1 OFFSET 1"; + String expectedMssql = "SELECT *\nFROM [foodmart].[employee]\nOFFSET 1 ROWS\n" + + "FETCH NEXT 1 ROWS ONLY"; + String expectedSybase = "SELECT TOP (1) START AT 1 *\nFROM foodmart.employee"; + sql(query) + .withMssql().ok(expectedMssql) + .withSybase().ok(expectedSybase); + } + @Test void testFloorMssqlMonth() { String query = "SELECT floor(\"hire_date\" TO MONTH) FROM \"employee\""; String expected = "SELECT CONVERT(DATETIME, CONVERT(VARCHAR(7), [hire_date] , 126)+'-01')\n"
