Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-3751

JDBC adapter wrongly pushes ORDER BY into sub-query

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.22.0
    • Component/s: jdbc-adapter
    • Labels:
      None

      Description

      JDBC adapter wrongly pushes ORDER BY into sub-query. When I run

      select deptno, job, sum(sal)
      from scott.emp
      group by deptno, job
      order by 1,2;
      

      against the hsqldb database via the JDBC adapter I get the error

      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)
      

      Note that ORDER BY is inside a sub-query, which I presume is invalid SQL for hsqldb (and most other DBs).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: