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

Generate wrong SQL when plan contains Project(Sort(Aggregate)) and aggregate field has no alias

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: In Progress
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.21.0
    • Fix Version/s: next
    • Component/s: core

      Description

      Input SQL:

      select sum(sal) from scott.emp group by ename limit 10
      

      Optimized Plan:

      JdbcToEnumerableConverter
        JdbcProject(EXPR$0=[$1])
          JdbcSort(fetch=[10])
            JdbcAggregate(group=[{1}], EXPR$0=[SUM($5)])
              JdbcTableScan(table=[[SCOTT, EMP]])
      

      Output wrong SQL: outter select list is wrong.

      SELECT SUM("SAL")
      FROM (SELECT "ENAME", SUM("SAL")
      FROM "SCOTT"."EMP"
      GROUP BY "ENAME"
      LIMIT 10) AS "t0" 

      It should be:

      SELECT SUM("SAL")
      FROM "SCOTT"."EMP"
      GROUP BY "ENAME"
      LIMIT 10
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                Lei Jiang Lei Jiang
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 6h
                  6h