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 Priority: Blocker Fix For: 14.0, 13.0.3, 13.1.1 Original Estimate: 3 hours Time Spent: 3 hours Remaining Estimate: 0 minutes 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}