[
https://issues.apache.org/jira/browse/CALCITE-7379?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7379:
----------------------------------
Description:
{code:sql}
WITH
t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
SELECT * FROM t1 WHERE EXISTS (
SELECT * FROM t2
LEFT JOIN
(SELECT * FROM t3 WHERE t3.a = t1.a) foo
ON t2.a = foo.a
);
return wrong result:
4, 4, 4
right result is:
2, 2, 2
3, 3, 3
4, 4, 4
{code:sql}
was:
{code:sql}
WITH
t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
SELECT * FROM t1 WHERE EXISTS (
SELECT * FROM t2
LEFT JOIN
(SELECT * FROM t3 WHERE t3.a = t1.a) foo
ON t2.a = foo.a
);
{code}
return wrong result:
{noformat}
4, 4, 4
{noformat}
right result is:
{noformat}
2, 2, 2
3, 3, 3
4, 4, 4
{noformat}
> LHS correlated variables are shadowed by nullable RHS outputs in LEFT JOIN
> --------------------------------------------------------------------------
>
> Key: CALCITE-7379
> URL: https://issues.apache.org/jira/browse/CALCITE-7379
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: weihua zhang
> Priority: Major
> Labels: pull-request-available
>
> {code:sql}
> WITH
> t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
> t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
> t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
> SELECT * FROM t1 WHERE EXISTS (
> SELECT * FROM t2
> LEFT JOIN
> (SELECT * FROM t3 WHERE t3.a = t1.a) foo
> ON t2.a = foo.a
> );
> return wrong result:
> 4, 4, 4
> right result is:
> 2, 2, 2
> 3, 3, 3
> 4, 4, 4
> {code:sql}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
