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

After pushing Project past Window, adjust references to constants properly

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.5.0
    • Component/s: core
    • Labels:
      None

      Description

      ProjectWindowTransposeRule would produce a LogicalWindow which mis-refers constant fields. A query like:

      select col1, col2 \n"
      from (
            select empno, sum(100) over (partition by  deptno order by sal) as col1,
                  sum(1000) over(partition by deptno order by sal) as col2
            from emp
      

      where the inner query generates columns which are never used in the main query. If we use the three rules in the hep planner:

      ProjectToWindowRule
      ProjectMergeRule
      ProjectWindowTransposeRule
      

      we will get a plan:

      LogicalProject(COL1=[$2], COL2=[$3])
        LogicalWindow(window#0=[window(partition {1} order by [0] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [SUM($2), SUM($2)])])
          LogicalProject(SAL=[$1], DEPTNO=[$2])
            LogicalProject(EMPNO=[$0], SAL=[$5], DEPTNO=[$7])
              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
      

      Note that the second SUM() in the LogicalWindow should have pointed to the second constant (i.e., $3).

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              seanhychu Sean Hsuan-Yi Chu
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: