You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Parth Chandra (JIRA)" <ji...@apache.org> on 2015/06/30 01:31:04 UTC
[jira] [Updated] (DRILL-3029) Wrong result with correlated not
exists subquery
[ https://issues.apache.org/jira/browse/DRILL-3029?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Parth Chandra updated DRILL-3029:
---------------------------------
Fix Version/s: 1.2.0
> 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
> 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)