[ https://issues.apache.org/jira/browse/DRILL-5049?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15811028#comment-15811028 ]
Khurram Faraaz commented on DRILL-5049: --------------------------------------- [~coder_kk] The two queries (one in the original JIRA and the other in the comment) are different. Both are correlated queries. When I added the comment I forgot to mention that this another similar correlated query also gives wrong results as compared to Postgres results. > 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)