[
https://issues.apache.org/jira/browse/DRILL-3029?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15923368#comment-15923368
]
Gautam Kumar Parai commented on DRILL-3029:
-------------------------------------------
[~agirish] Instead of empty results I see the following error {quote} ERROR:
correlated subquery with skip-level correlations is not supported
(subselect.c:394) {quote}. Can you please take a look?
> 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: Future
>
> 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.15#6346)
