[
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}
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}
was:
{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
> 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: Bug
> 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)
