drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Victoria Markman (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-3029) Wrong result with correlated not exists subquery
Date Mon, 11 May 2015 23:33:00 GMT
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)

Mime
View raw message