[
https://issues.apache.org/jira/browse/DRILL-3029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14612484#comment-14612484
]
Victoria Markman commented on DRILL-3029:
-----------------------------------------
Drill output with 1.1
#Tue Jun 30 14:50:20 EDT 2015
git.commit.id.abbrev=e3fc7e9
{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 (3.633 seconds)
{code}
> 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
>
> Attachments: t1_t2_t3.tar
>
>
> 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)
