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

LogicalAggregate plan node looks incorrect when window functions are present

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.3.0-incubating
    • Fix Version/s: 1.4.0-incubating
    • Component/s: None
    • Labels:
      None

      Description

      For the following query:

        select min(sal), max(sal), 
            rank() over (order by job), 
            sum(sal) over (order by job) 
          from emp 
        group by job, sal
      

      I would have expected the LogicalAggregate to do the group-by and produce the MIN and MAX only. The plan below shows 2 additional fields: agg#2=RANK and agg#3=SUM($1) . I think this is incorrect because these functions should be associated with the LogicalWindow node only (which does not show up in the plan yet). Converting the LogicalAggregate to a physical plan node creates difficulties due to this. Is the physical aggregate supposed to only look at EXPR$1 and EXPR$2 and ignore agg#1, agg#2 ?

      LogicalProject(EXPR$0=[$2], EXPR$1=[$3], EXPR$2=[RANK() OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)], EXPR$3=[SUM($1) OVER (ORDER BY $0 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)])
        LogicalAggregate(group=[{0, 1}], EXPR$0=[MIN($1)], EXPR$1=[MAX($1)], agg#2=[RANK()], agg#3=[SUM($1)])
          LogicalProject(JOB=[$2], SAL=[$5])
            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      

        Attachments

          Activity

            People

            • Assignee:
              amansinha100 Aman Sinha
              Reporter:
              amansinha100 Aman Sinha
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: