[
https://issues.apache.org/jira/browse/CALCITE-7278?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7278:
----------------------------------
Issue Type: Improvement (was: Bug)
> 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}
> WITH
> T1(C1, C2, C3) AS (
> VALUES (1, 2, 3)
> ),
> T2(C4, C5, C6) AS (
> VALUES (4, 5, 6)
> ),
> T3(C7, C8, C9, C10) AS (
> VALUES (7, 8, 9, 10)
> )
> SELECT *
> FROM T1
> LEFT JOIN T2
> ON T1.C1 = T2.C4
> OR T2.C5 IN (
> SELECT T3.C8
> FROM T3
> WHERE T3.C9 = T1.C3 AND T3.C10 = T2.C6
> );
> {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(C1=[$0], C2=[$1], C3=[$2], C4=[$3], C5=[$4], C6=[$5]), id = 23
> LogicalJoin(condition=[OR(=($0, $3), IN($4, {
> LogicalProject(C8=[$1])
> LogicalFilter(condition=[AND(=($2, $cor0.C3), =($3, $cor0.C6))])
> LogicalValues(tuples=[[{ 7, 8, 9, 10 }]])
> }))], joinType=[left]), id = 21
> LogicalValues(tuples=[[{ 1, 2, 3 }]]), id = 12
> LogicalValues(tuples=[[{ 4, 5, 6 }]]), id = 13
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
