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

Rel-to-sql conversion fails to insert a subquery for some sort-by-ordinal queries

    XMLWordPrintableJSON

Details

    Description

      Currently, when given a rel expression like this:

      LogicalProject(JOB=[$0])
        LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
          LogicalAggregate(group=[{2}], agg#0=[COUNT($1)])
            LogicalTableScan(table=[[scott, EMP]])
      

      Calcite will convert to this invalid (ORDER BY 2 despite only one SELECT expression) SQL:

      SELECT "JOB"
      FROM "scott"."EMP"
      GROUP BY "JOB"
      ORDER BY "JOB", 2
      

      When it should convert to this SQL with a sub-query:

      SELECT "JOB"
      FROM (SELECT "JOB", COUNT("ENAME") AS "$f1"
      FROM "scott"."EMP"
      GROUP BY "JOB"
      ORDER BY "JOB", 2) AS "t0"
      

      The problem is that the hasSortByOrdinal method sees a non-ordinal "JOB" in the ORDER BY clause and incorrectly says that there are no ordinals, due to an improper early return. It should only return early if the result is true. Otherwise, it should continue searching for ordinals.

      When the method is fixed, a subquery is created.

      Attachments

        Issue Links

          Activity

            People

              wnoble Will Noble
              wnoble Will Noble
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: