[
https://issues.apache.org/jira/browse/DRILL-5049?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15797461#comment-15797461
]
Khurram Faraaz commented on DRILL-5049:
---------------------------------------
Drill 1.10.0 => 21 rows returned
{noformat}
0: jdbc:drill:schema=dfs.tmp> SELECT t1.c4
. . . . . . . . . . . . . . > FROM `t_alltype.parquet` t1
. . . . . . . . . . . . . . > WHERE NOT EXISTS
. . . . . . . . . . . . . . > (
. . . . . . . . . . . . . . > SELECT t2.c4
. . . . . . . . . . . . . . > FROM `t_alltype.parquet` t2
. . . . . . . . . . . . . . > WHERE t1.c4 = t2.c4 OR (t1.c4 IS
NULL AND t2.c4 IS NULL)
. . . . . . . . . . . . . . > );
+-------+
| c4 |
+-------+
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
| null |
+-------+
21 rows selected (0.662 seconds)
{noformat}
results from Postgres 9.3 => zero rows returned.
{noformat}
postgres=# SELECT t1.c4
postgres-# FROM t_alltype t1
postgres-# WHERE NOT EXISTS
postgres-# (
postgres(# SELECT t2.c4
postgres(# FROM t_alltype t2
postgres(# WHERE t1.c4 = t2.c4 OR (t1.c4 IS NULL AND t2.c4 IS
NULL)
postgres(# );
c4
----
(0 rows)
{noformat}
> wrong results - correlated subquery interacting with null equality join
> -----------------------------------------------------------------------
>
> Key: DRILL-5049
> URL: https://issues.apache.org/jira/browse/DRILL-5049
> Project: Apache Drill
> Issue Type: Bug
> Components: Execution - Flow
> Affects Versions: 1.9.0
> Reporter: Khurram Faraaz
> Priority: Critical
> Attachments: nullEqJoin_17.drill_res, nullEqJoin_17.postgres,
> t_alltype.parquet
>
>
> Here is a query that uses null equality join. Drill 1.9.0 returns 124
> records, whereas Postgres 9.3 returns 145 records. I am on Drill 1.9.0 git
> commit id: db308549
> I have attached the results from Drill 1.9.0 and Postgres, please review.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> explain plan for
> . . . . . . . . . . . . . . > SELECT *
> . . . . . . . . . . . . . . > FROM `t_alltype.parquet` t1
> . . . . . . . . . . . . . . > WHERE EXISTS
> . . . . . . . . . . . . . . > (
> . . . . . . . . . . . . . . > SELECT *
> . . . . . . . . . . . . . . > FROM `t_alltype.parquet` t2
> . . . . . . . . . . . . . . > WHERE t1.c4 = t2.c4 OR (t1.c4
> IS NULL AND t2.c4 IS NULL)
> . . . . . . . . . . . . . . > );
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 Project(*=[$0])
> 00-02 Project(T30¦¦*=[$0])
> 00-03 HashJoin(condition=[AND(=($1, $2), =($1, $3))],
> joinType=[inner])
> 00-05 Project(T30¦¦*=[$0], c4=[$1])
> 00-07 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath [path=maprfs:///tmp/t_alltype.parquet]],
> selectionRoot=maprfs:/tmp/t_alltype.parquet, numFiles=1,
> usedMetadataFile=false, columns=[`*`]]])
> 00-04 HashAgg(group=[{0, 1}], agg#0=[MIN($2)])
> 00-06 Project(c40=[$1], c400=[$1], $f0=[true])
> 00-08 HashJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
> joinType=[inner])
> 00-10 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath [path=maprfs:///tmp/t_alltype.parquet]],
> selectionRoot=maprfs:/tmp/t_alltype.parquet, numFiles=1,
> usedMetadataFile=false, columns=[`c4`]]])
> 00-09 Project(c40=[$0])
> 00-11 HashAgg(group=[{0}])
> 00-12 Scan(groupscan=[ParquetGroupScan
> [entries=[ReadEntryWithPath [path=maprfs:///tmp/t_alltype.parquet]],
> selectionRoot=maprfs:/tmp/t_alltype.parquet, numFiles=1,
> usedMetadataFile=false, columns=[`c4`]]])
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
