[
https://issues.apache.org/jira/browse/CALCITE-7379?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18055164#comment-18055164
]
weihua zhang commented on CALCITE-7379:
---------------------------------------
in *Improving Unnesting of Complex Queries* 3.3
!screenshot-1.png|width=992,height=899!
> 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)
