[calcite] branch site updated: [CALCITE-5768] JDBC adapter should insert a subquery for a query with ORDER BY ordinal

[github-bot]

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

github-bot pushed a commit to branch site
in repository https://gitbox.apache.org/repos/asf/calcite.git


The following commit(s) were added to refs/heads/site by this push:
     new c9aab6be33 [CALCITE-5768] JDBC adapter should insert a subquery for a 
query with ORDER BY ordinal
c9aab6be33 is described below

commit c9aab6be3377f7cc11a43cf916f7759a1017688a
Author: Will Noble <wno...@google.com>
AuthorDate: Wed Jun 7 18:16:59 2023 -0700

    [CALCITE-5768] JDBC adapter should insert a subquery for a query with ORDER 
BY ordinal
    
    The incorrect query has an ordinal that is not the first item
    of the ORDER BY clause:
    
      SELECT "JOB"
      FROM "scott"."EMP"
      GROUP BY "JOB"
      ORDER BY "JOB", 2
    
    The correct query should have a subquery:
    
      SELECT "JOB"
      FROM (SELECT "JOB", COUNT("ENAME") AS "$f1"
        FROM "scott"."EMP"
        GROUP BY "JOB"
        ORDER BY "JOB", 2) AS "t0"
---
 .../org/apache/calcite/rel/rel2sql/SqlImplementor.java | 12 +++++++-----
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java     | 18 ++++++++++++++++++
 2 files changed, 25 insertions(+), 5 deletions(-)

diff --git 
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java 
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index 6af0bd989e..9e65855c7d 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -1861,12 +1861,14 @@ public abstract class SqlImplementor {
           return false;
         }
         for (SqlNode sqlNode : orderList) {
-          if (!(sqlNode instanceof SqlBasicCall)) {
-            return false;
+          if (sqlNode instanceof SqlNumericLiteral) {
+            return true;
           }
-          for (SqlNode operand : ((SqlBasicCall) sqlNode).getOperandList()) {
-            if (operand instanceof SqlNumericLiteral) {
-              return true;
+          if (sqlNode instanceof SqlBasicCall) {
+            for (SqlNode operand : ((SqlBasicCall) sqlNode).getOperandList()) {
+              if (operand instanceof SqlNumericLiteral) {
+                return true;
+              }
             }
           }
         }
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 e373c073a6..dca39aabbd 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
@@ -401,6 +401,24 @@ class RelToSqlConverterTest {
     relFn(relFn).ok(expected);
   }
 
+  @Test void testUsesSubqueryWhenSortingByIdThenOrdinal() {
+    final Function<RelBuilder, RelNode> relFn = b -> b
+        .scan("EMP")
+        .aggregate(
+            b.groupKey("JOB"),
+            b.aggregateCall(SqlStdOperatorTable.COUNT, b.field("ENAME")))
+        .sort(b.field(0), b.field(1))
+        .project(b.field(0))
+        .build();
+    final String expected = "SELECT \"JOB\"\n"
+        + "FROM (SELECT \"JOB\", COUNT(\"ENAME\") AS \"$f1\"\n"
+        + "FROM \"scott\".\"EMP\"\n"
+        + "GROUP BY \"JOB\"\n"
+        + "ORDER BY \"JOB\", 2) AS \"t0\"";
+
+    relFn(relFn).ok(expected);
+  }
+
   @Test void testSelectQueryWithWhereClauseOfBasicOperators() {
     String query = "select * from \"product\" "
         + "where (\"product_id\" = 10 OR \"product_id\" <= 5) "