[CALCITE-5138] Join on condition generates wrong plan when the condition is sub-query

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.30.0
• Fix Version/s: 1.31.0
• Component/s: core
• Labels: pull-request-available
• Assignee: xiong duan
• Reporter: xiong duan
• Created: 08/May/22 02:39
• Updated: 03/Aug/22 16:21
• Resolved: 12/May/22 23:11

Description

The SQL:

SELECT emp.deptno, emp.sal
FROM dept
         LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) 

Calcite generates the wrong plan:

 EnumerableCalc(expr#0..4=[{inputs}], DEPTNO=[$t3], SAL=[$t2])
   EnumerableNestedLoopJoin(condition=[$0], joinType=[left])
     EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
       EnumerableTableScan(table=[[scott, DEPT]])
     EnumerableNestedLoopJoin(condition=[true], joinType=[left])
       EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7])
         EnumerableTableScan(table=[[scott, EMP]])
       EnumerableAggregate(group=[{}], DUMMY=[COUNT()])
         EnumerableAggregate(group=[{}], agg#0=[$SUM0($5)], agg#1=[COUNT($5)])
           EnumerableTableScan(table=[[scott, EMP]])

As above plan, the out NestedLoopJoin condition will be deptno column, not the AVG(emp.sal) > 0 condition.

Issue Links

• is related to: CALCITE-5154 Boolean clause is not recognized as boolean in Join-On's subquery.
• links to: GitHub Pull Request #2801