drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gautam Kumar Parai (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-5049) wrong results - correlated subquery interacting with null equality join
Date Wed, 15 Mar 2017 00:03:41 GMT

    [ https://issues.apache.org/jira/browse/DRILL-5049?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15925299#comment-15925299
] 

Gautam Kumar Parai commented on DRILL-5049:
-------------------------------------------

Please refer CALCITE-714, CALCITE-1200 for a more complete reference.

> 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
>            Assignee: Gautam Kumar Parai
>            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.15#6346)

Mime
View raw message