[
https://issues.apache.org/jira/browse/CALCITE-5138?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17536369#comment-17536369
]
xiong duan commented on CALCITE-5138:
-------------------------------------
Fixed in
[86b34f7|https://github.com/apache/calcite/commit/86b34f7ba81cc85aec4589f8fa4dcbf503ad8677].
Thanks for the review [~asolimando].
> 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
> 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)
