[
https://issues.apache.org/jira/browse/CALCITE-7278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7278:
----------------------------------
Description:
{code:sql}
SELECT *
FROM dept d
LEFT JOIN emp e
ON d.deptno = e.deptno
OR e.job IN (
SELECT b.job
FROM bonus b
WHERE b.sal = d.deptno
);
{code}
will hit
https://github.com/apache/calcite/blob/253ed4639fdc07dd58de3f310503df2847500973/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L951
{code:java}
LogicalProject(deptno=[$0], dname=[$1], loc=[$2], empno=[$3], ename=[$4],
job=[$5], mgr=[$6], hiredate=[$7], sal=[$8], comm=[$9], deptno0=[$10])
LogicalJoin(condition=[OR(=($0, $10), IN($5, {
LogicalProject(JOB=[$1])
LogicalFilter(condition=[=($2, CAST($cor0.deptno):DOUBLE)])
LogicalTableScan(table=[[testdb, bonus]])
}))], joinType=[left])
LogicalTableScan(table=[[testdb, dept]])
LogicalTableScan(table=[[testdb, emp]])
{code}
was:
{code:sql}
SELECT *
FROM dept d
LEFT JOIN emp e
ON d.deptno = e.deptno
OR e.job IN (
SELECT b.job
FROM bonus b
WHERE b.sal = d.deptno
AND b.ename = e.ename
);
{code}
will hit
https://github.com/apache/calcite/blob/253ed4639fdc07dd58de3f310503df2847500973/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L951
{code:java}
LogicalProject(deptno=[$0], dname=[$1], loc=[$2], empno=[$3], ename=[$4],
job=[$5], mgr=[$6], hiredate=[$7], sal=[$8], comm=[$9], deptno0=[$10])
LogicalJoin(condition=[OR(=($0, $10), IN($5, {
LogicalProject(JOB=[$1])
LogicalFilter(condition=[AND(=($2, CAST($cor0.deptno):DOUBLE), =($0,
$cor0.ename))])
LogicalTableScan(table=[[testdb, bonus]])
}))], joinType=[left])
LogicalTableScan(table=[[testdb, dept]])
LogicalTableScan(table=[[testdb, emp]])
{code}
> Correlated subqueries in the join condition cannot reference both join inputs
> -----------------------------------------------------------------------------
>
> Key: CALCITE-7278
> URL: https://issues.apache.org/jira/browse/CALCITE-7278
> Project: Calcite
> Issue Type: Improvement
> Reporter: weihua zhang
> Priority: Major
>
> {code:sql}
> SELECT *
> FROM dept d
> LEFT JOIN emp e
> ON d.deptno = e.deptno
> OR e.job IN (
> SELECT b.job
> FROM bonus b
> WHERE b.sal = d.deptno
> );
> {code}
> will hit
> https://github.com/apache/calcite/blob/253ed4639fdc07dd58de3f310503df2847500973/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java#L951
> {code:java}
> LogicalProject(deptno=[$0], dname=[$1], loc=[$2], empno=[$3], ename=[$4],
> job=[$5], mgr=[$6], hiredate=[$7], sal=[$8], comm=[$9], deptno0=[$10])
> LogicalJoin(condition=[OR(=($0, $10), IN($5, {
> LogicalProject(JOB=[$1])
> LogicalFilter(condition=[=($2, CAST($cor0.deptno):DOUBLE)])
> LogicalTableScan(table=[[testdb, bonus]])
> }))], joinType=[left])
> LogicalTableScan(table=[[testdb, dept]])
> LogicalTableScan(table=[[testdb, emp]])
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
