[CALCITE-3847] Decorrelation for join with lateral table outputs wrong plan if the join condition contains correlation variables

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.22.0
• Fix Version/s: 1.23.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Danny Chen
• Reporter: Danny Chen
• Created: 09/Mar/20 11:57
• Updated: 24/May/20 12:00
• Resolved: 10/Mar/20 05:55

Description

After CALCITE-2004, we can decorrelate plan for query:

select deptno, r.num from dept join
lateral table(ramp(dept.deptno)) as r(num)
on deptno=num

to plan like:

LogicalProject(DEPTNO=[$0], NUM=[$2])
  LogicalJoin(condition=[=($0, $2)], joinType=[inner])
    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
    LogicalTableFunctionScan(invocation=[RAMP($cor0.DEPTNO)], rowType=[RecordType(INTEGER I)])

See there was a correlate variable left.

To fix this, i would suggest to stop decorrelating when we encounter the correlate variables in LogicalTableFunctionScan.

Issue Links

• links to: GitHub Pull Request #1850