[
https://issues.apache.org/jira/browse/CALCITE-7379?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
weihua zhang updated CALCITE-7379:
----------------------------------
Description:
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}
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
);
return wrong result:
4, 4, 4
right result is:
2, 2, 2
3, 3, 3
4, 4, 4
{code:sql}
> 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
>
> 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)
