[ https://issues.apache.org/jira/browse/DRILL-3029?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Aman Sinha updated DRILL-3029: ------------------------------ Assignee: Victoria Markman (was: Jinfeng Ni) > Wrong result with correlated not exists subquery > ------------------------------------------------ > > Key: DRILL-3029 > URL: https://issues.apache.org/jira/browse/DRILL-3029 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.0.0 > Reporter: Victoria Markman > Assignee: Victoria Markman > Priority: Critical > Fix For: 1.2.0 > > > Subquery has correlation to two outer tables in the previous blocks. > Postgres returns empty result set in this case: > {code} > 0: jdbc:drill:schema=dfs> select > . . . . . . . . . . . . > distinct a1 > . . . . . . . . . . . . > from > . . . . . . . . . . . . > t1 > . . . . . . . . . . . . > where not exists > . . . . . . . . . . . . > ( > . . . . . . . . . . . . > select > . . . . . . . . . . . . > * > . . . . . . . . . . . . > from > . . . . . . . . . . . . > t2 > . . . . . . . . . . . . > where not exists > . . . . . . . . . . . . > ( > . . . . . . . . . . . . > select > . . . . . . . . . . . . > * > . . . . . . . . . . . . > from > . . . . . . . . . . . . > t3 > . . . . . . . . . . . . > where > . . . . . . . . . . . . > t3.b3 = t2.b2 and > . . . . . . . . . . . . > t3.a3 = t1.a1 > . . . . . . . . . . . . > ) > . . . . . . . . . . . . > ) > . . . . . . . . . . . . > ; > +------------+ > | a1 | > +------------+ > | 1 | > | 2 | > | 3 | > | 4 | > | 5 | > | 6 | > | 7 | > | 9 | > | 10 | > | null | > +------------+ > 10 rows selected (0.991 seconds) > {code} > Copy/paste reproduction: > {code} > select > distinct a1 > from > t1 > where not exists > ( > select > * > from > t2 > where not exists > ( > select > * > from > t3 > where > t3.b3 = t2.b2 and > t3.a3 = t1.a1 > ) > ) > ; > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)