[teiid-issues] [JBoss JIRA] (TEIID-5933) Nested left joins return wrong results

[Dmitrii Pogorelov (Jira)]

Dmitrii Pogorelov created TEIID-5933:
----------------------------------------

             Summary: Nested left joins return wrong results
                 Key: TEIID-5933
                 URL: https://issues.redhat.com/browse/TEIID-5933
             Project: Teiid
          Issue Type: Bug
          Components: Query Engine
    Affects Versions: 13.1
            Reporter: Dmitrii Pogorelov
            Assignee: Steven Hawkins


Nested left joins in combination with a where x is null (on a left joined column) return wrong results:
{code:sql}
-- wrong result
SELECT *
FROM test_dwh_pg.test_1 a
    LEFT JOIN ( SELECT x.c1 
             	FROM test_dwh_pg.test_2 x
             		LEFT JOIN (SELECT * FROM test_dwh_pg.test_3 WHERE c1 <> '123')  y  ON x.c1 = y.c1 WHERE y.c1 IS NULL
     ) b ON a.c1 = b.c1
WHERE b.c1 IS NULL ;;
 
-- correct result
SELECT *
FROM test_dwh_pg.test_1 a
    LEFT JOIN ( SELECT x.c1 
             	FROM test_dwh_pg.test_2 x
             		LEFT JOIN (SELECT * FROM test_dwh_pg.test_3 WHERE c1 <> '123')  y  ON x.c1 = y.c1 WHERE y.c1 IS NULL
             	LIMIT 1000000000	
     ) b ON a.c1 = b.c1
WHERE b.c1 IS NULL ;;
{code}



--
This message was sent by Atlassian Jira
(v7.13.8#713008)