Major 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).