You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Victoria Markman (JIRA)" <ji...@apache.org> on 2015/05/12 01:33:00 UTC
[jira] [Created] (DRILL-3029) Wrong result with correlated not
exists subquery
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)