[calcite] branch main updated: [CALCITE-5723] Oracle dialect generates SQL that cannot be recognized by lower version Oracle Server(<12) when unparsing OffsetFetch

[jiajunxie]

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

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


The following commit(s) were added to refs/heads/main by this push:
     new d98f96d830 [CALCITE-5723] Oracle dialect generates SQL that cannot be 
recognized by lower version Oracle Server(<12) when unparsing OffsetFetch
d98f96d830 is described below

commit d98f96d830e2114aff4166476ebedf6bd82a4c4e
Author: ILuffZhe <iluff...@163.com>
AuthorDate: Thu May 25 20:41:28 2023 +0800

    [CALCITE-5723] Oracle dialect generates SQL that cannot be recognized by 
lower version Oracle Server(<12) when unparsing OffsetFetch
---
 .../calcite/sql/dialect/OracleSqlDialect.java      | 12 +++++++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 29 +++++++++++++++++++++-
 2 files changed, 40 insertions(+), 1 deletion(-)

diff --git 
a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java 
b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
index fbd9aef93b..e99ab2a44f 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/OracleSqlDialect.java
@@ -69,9 +69,12 @@ public class OracleSqlDialect extends SqlDialect {
 
   public static final SqlDialect DEFAULT = new 
OracleSqlDialect(DEFAULT_CONTEXT);
 
+  private final int majorVersion;
+
   /** Creates an OracleSqlDialect. */
   public OracleSqlDialect(Context context) {
     super(context);
+    this.majorVersion = context.databaseMajorVersion();
   }
 
   @Override public boolean supportsApproxCountDistinct() {
@@ -188,4 +191,13 @@ public class OracleSqlDialect extends SqlDialect {
       }
     }
   }
+
+  @Override public void unparseOffsetFetch(SqlWriter writer, @Nullable SqlNode 
offset,
+       @Nullable SqlNode fetch) {
+    // majorVersion in SqlDialect.EMPTY_CONTEXT is -1 by default
+    if (this.majorVersion != -1 && this.majorVersion < 12) {
+      throw new RuntimeException("Lower Oracle version(<12) doesn't support 
offset/fetch syntax!");
+    }
+    super.unparseOffsetFetch(writer, offset, fetch);
+  }
 }
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 dc6658162c..412ddb4c16 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
@@ -6866,6 +6866,22 @@ class RelToSqlConverterTest {
         .withCalcite().ok(expectedCalciteX);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-5723";>[CALCITE-5723]
+   * Oracle dialect generates SQL that cannot be recognized by lower version
+   * Oracle Server(<12) when unparsing OffsetFetch</a>. */
+  @Test void testFetchOffsetOracle() {
+    String query = "SELECT \"department_id\" FROM \"employee\" LIMIT 2 OFFSET 
1";
+    String expected = "SELECT \"department_id\"\n"
+        + "FROM \"foodmart\".\"employee\"\n"
+        + "OFFSET 1 ROWS\n"
+        + "FETCH NEXT 2 ROWS ONLY";
+    sql(query)
+        .withOracle().ok(expected)
+        .withOracle(19).ok(expected)
+        .withOracle(11).throws_("Lower Oracle version(<12) doesn't support 
offset/fetch syntax!");
+  }
+
   /** Test case for
    * <a 
href="https://issues.apache.org/jira/browse/CALCITE-5265";>[CALCITE-5265]
    * JDBC adapter sometimes adds unnecessary parentheses around SELECT in 
INSERT</a>. */
@@ -7122,7 +7138,18 @@ class RelToSqlConverterTest {
     }
 
     Sql withOracle() {
-      return dialect(DatabaseProduct.ORACLE.getDialect());
+      return withOracle(12);
+    }
+
+    Sql withOracle(int majorVersion) {
+      final SqlDialect oracleDialect = DatabaseProduct.ORACLE.getDialect();
+      return dialect(
+          new OracleSqlDialect(OracleSqlDialect.DEFAULT_CONTEXT
+              .withDatabaseProduct(DatabaseProduct.ORACLE)
+              .withDatabaseMajorVersion(majorVersion)
+              .withIdentifierQuoteString(oracleDialect.quoteIdentifier("")
+                  .substring(0, 1))
+              .withNullCollation(oracleDialect.getNullCollation())));
     }
 
     Sql withPostgresql() {