[
https://issues.apache.org/jira/browse/CALCITE-7278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-7278:
------------------------------------
Labels: pull-request-available (was: )
> 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
> Labels: pull-request-available
> Attachments: image-2025-12-02-09-45-26-967.png
>
>
> {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)
