Victoria Markman created DRILL-3029: ---------------------------------------
Summary: 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: Jinfeng Ni Priority: Critical 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)