[jira] [Commented] (CALCITE-3751) JDBC adapter wrongly pushes ORDER BY into sub-query

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3751?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17020485#comment-17020485 ] 

Julian Hyde commented on CALCITE-3751:
--------------------------------------

Here's a test case:
{noformat}
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index 25f0c0c31..6ac387cb7 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -131,6 +131,25 @@
             + "WHERE \"product_id\" = 1");
   }
 
+  @Test public void testOrderByPlan() {
+    final String sql = "select deptno, job, sum(sal)\n"
+        + "from \"EMP\"\n"
+        + "group by deptno, job\n"
+            + "order by 1, 2";
+    final String explain = "PLAN=JdbcToEnumerableConverter\n"
+        + "  JdbcProject(DEPTNO=[$1], JOB=[$0], EXPR$2=[$2])\n"
+        + "    JdbcSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])\n"
+        + "      JdbcAggregate(group=[{2, 7}], EXPR$2=[SUM($5)])\n"
+        + "        JdbcTableScan(table=[[SCOTT, EMP]])";
+    final String sqlHsqldb = "SELECT ...";
+    CalciteAssert.model(JdbcTest.SCOTT_MODEL)
+        .query(sql)
+        .explainContains(explain)
+        .runs()
+        .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+        .planHasSql(sqlHsqldb);
+  }
+
   @Test public void testInPlan() {
     CalciteAssert.model(JdbcTest.FOODMART_MODEL)
         .query("select \"store_id\", \"store_name\" from \"store\"\n"
{noformat}
 Due the bug, it fails in 'runs()'.

> JDBC adapter wrongly pushes ORDER BY into sub-query
> ---------------------------------------------------
>
>                 Key: CALCITE-3751
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3751
>             Project: Calcite
>          Issue Type: Bug
>          Components: jdbc-adapter
>            Reporter: Julian Hyde
>            Priority: Major
>
> JDBC adapter wrongly pushes ORDER BY into sub-query. When I run
> {code:java}
> select deptno, job, sum(sal)
> from scott.emp
> group by deptno, job
> order by 1,2;
> {code}
> against the hsqldb database via the JDBC adapter I get the error
> {noformat}
> Error: Error while executing SQL "select deptno, job, sum(sal) from scott.emp group by deptno, job order by 1,2": While executing SQL [SELECT "DEPTNO", "JOB", SUM("SAL")
> FROM (SELECT "JOB", "DEPTNO", SUM("SAL")
> FROM "SCOTT"."EMP"
> GROUP BY "JOB", "DEPTNO"
> ORDER BY "DEPTNO" NULLS LAST, "JOB" NULLS LAST) AS "t0"] on JDBC sub-schema (state=,code=0)
> Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: SAL
>  at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
>  at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
> {noformat}
> Note that ORDER BY is inside a sub-query, which I presume is invalid SQL for hsqldb (and most other DBs).



--
This message was sent by Atlassian Jira
(v8.3.4#803005)