xiong duan created CALCITE-5138: ----------------------------------- Summary: Join on condition generates wrong plan when the condition is sub-query Key: CALCITE-5138 URL: https://issues.apache.org/jira/browse/CALCITE-5138 Project: Calcite Issue Type: Bug Components: core Affects Versions: 1.30.0 Reporter: xiong duan
The SQL: {code:java} SELECT emp.deptno, emp.sal FROM dept LEFT JOIN emp ON (SELECT AVG(emp.sal) > 0 FROM emp) {code} Calcite generates the wrong plan: {noformat} 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]]){noformat} As above plan, the out NestedLoopJoin condition will be deptno column, not the AVG(emp.sal) > 0 condition. -- This message was sent by Atlassian Jira (v8.20.7#820007)