[CALCITE-3004] RexOver is incorrectly pushed down in ProjectSetOpTransposeRule and ProjectCorrelateTransposeRule

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.19.0
• Fix Version/s: 1.20.0
• Labels: pull-request-available
• Assignee: Chunwei Lei
• Reporter: Hongze Zhang
• Created: 16/Apr/19 03:44
• Updated: 24/Jun/19 15:01
• Resolved: 23/Apr/19 22:33

Description

1. Test case for ProjectSetOpTransposeRule:

select count(sal) over () from (select * from emp e1 union all select * from emp e2)

Plan:

LogicalUnion(all=[true])
  LogicalProject(EXPR$0=[COUNT($5) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
  LogicalProject(EXPR$0=[COUNT($5) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

Should be:

LogicalProject(EXPR$0=[COUNT($5) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
  LogicalUnion(all=[true])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
      LogicalTableScan(table=[[CATALOG, SALES, EMP]])

 

2. Test case for ProjectCorrelateTransposeRule:

select t2.empno, sum(t1.deptno) over () from DEPT_NESTED as t1, unnest(t1.employees) as t2

Plan: Will cause infinitely loop due to RexOver is pushed down

Should be:

 

LogicalProject(EMPNO=[$2], EXPR$1=[SUM($0) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)])
  LogicalCorrelate(correlation=[$cor1], joinType=[inner], requiredColumns=[{1}])
    LogicalProject(DEPTNO=[$0], EMPLOYEES=[$3])
      LogicalTableScan(table=[[CATALOG, SALES, DEPT_NESTED]])
  LogicalProject(EMPNO=[$0])
    Uncollect
      LogicalProject(EMPLOYEES=[$cor1.EMPLOYEES])
        LogicalValues(tuples=[[{ 0 }]])

 

Issue Links

• links to: GitHub Pull Request #1166