[
https://issues.apache.org/jira/browse/CALCITE-5138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan resolved CALCITE-5138.
---------------------------------
Fix Version/s: 1.31.0
Resolution: Fixed
> 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
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.31.0
>
> Time Spent: 0.5h
> Remaining Estimate: 0h
>
> 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)
