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)