[
https://issues.apache.org/jira/browse/CALCITE-5138?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
xiong duan updated CALCITE-5138:
--------------------------------
Description:
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.
was:
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.
> 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
>
> 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)
