[
https://issues.apache.org/jira/browse/CALCITE-7379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18055228#comment-18055228
]
weihua zhang commented on CALCITE-7379:
---------------------------------------
This paragraph is cited from the paper to demonstrate that my implementation
embodies the ideas presented herein, enabling future readers of this PR to
refer to the concepts from the paper for reference.
> 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
> Fix For: 1.42.0
>
> Attachments: screenshot-1.png
>
>
> case1:
> {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}
> case2:
> {code:sql}
> SELECT * FROM dept
> WHERE EXISTS (
> SELECT * FROM emp
> LEFT JOIN (
> SELECT * FROM emp e_sub
> WHERE e_sub.deptno = dept.deptno
> ) foo
> ON emp.deptno = foo.deptno
> );
> --return wrong result:
> -- +--------+------------+----------+
> -- | DEPTNO | DNAME | LOC |
> -- +--------+------------+----------+
> -- | 10 | ACCOUNTING | NEW YORK |
> -- | 20 | RESEARCH | DALLAS |
> -- | 30 | SALES | CHICAGO |
> -- +--------+------------+----------+
> -- (3 rows)
> --right result is:
> -- dname | deptno | loc
> -- ------------+--------+----------
> -- ACCOUNTING | 10 | NEW YORK
> -- RESEARCH | 20 | DALLAS
> -- SALES | 30 | CHICAGO
> -- OPERATIONS | 40 | BOSTON
> --(4 rows)
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
