[jira] [Created] (DRILL-5049) difference in results - correlated subquery interacting with null equality join

["Khurram Faraaz (JIRA)" <ji...@apache.org>]

Khurram Faraaz created DRILL-5049:
-------------------------------------

             Summary: difference in 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


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)