[calcite] branch master updated: [CALCITE-4968] Use TOP N for MsSQL instead of FETCH without OFFSET

[jbalint]

This is an automated email from the ASF dual-hosted git repository.

jbalint pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/master by this push:
     new cc40a48  [CALCITE-4968] Use TOP N for MsSQL instead of FETCH without 
OFFSET
cc40a48 is described below

commit cc40a48cb8ca16f91bfdc66eaed6151805355d4b
Author: Volodymyr Vysotskyi <vvo...@gmail.com>
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"