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)
