drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Zelaine Fong (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DRILL-5367) Join query returns wrong results
Date Mon, 20 Mar 2017 15:47:41 GMT

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

Zelaine Fong commented on DRILL-5367:
-------------------------------------

[~khfaraaz] - can you attach the data files you used.

> Join query returns wrong results
> --------------------------------
>
>                 Key: DRILL-5367
>                 URL: https://issues.apache.org/jira/browse/DRILL-5367
>             Project: Apache Drill
>          Issue Type: Bug
>          Components: Execution - Flow
>    Affects Versions: 1.10.0
>         Environment: 3 node cluster
>            Reporter: Khurram Faraaz
>
> Join query returns wrong results
> Drill 1.10.0 does not return any results.
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> SELECT * FROM using_f1 JOIN (SELECT * FROM using_f2)
foo USING(col_prime);
> +---------+------------+------------+-------------+---------+-----------+----------+-------------+-------------+--------------+----------+------------+
> | col_dt  | col_state  | col_prime  | col_varstr  | col_id  | col_name  | col_dt0  |
col_state0  | col_prime0  | col_varstr0  | col_id0  | col_name0  |
> +---------+------------+------------+-------------+---------+-----------+----------+-------------+-------------+--------------+----------+------------+
> +---------+------------+------------+-------------+---------+-----------+----------+-------------+-------------+--------------+----------+------------+
> No rows selected (0.314 seconds)
> {noformat}
> {noformat}
> Explain plan for above failing query
> 0: jdbc:drill:schema=dfs.tmp> explain plan for SELECT * FROM using_f1 JOIN (SELECT
* FROM using_f2) foo USING(col_prime);
> +------+------+
> | text | json |
> +------+------+
> | 00-00    Screen
> 00-01      ProjectAllowDup(*=[$0], *0=[$1])
> 00-02        Project(T49¦¦*=[$0], T48¦¦*=[$2])
> 00-03          Project(T49¦¦*=[$1], col_prime=[$2], T48¦¦*=[$0])
> 00-04            HashJoin(condition=[=($2, $0)], joinType=[inner])
> 00-06              Project(T48¦¦*=[$0])
> 00-08                Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///tmp/using_f2]],
selectionRoot=maprfs:/tmp/using_f2, numFiles=1, usedMetadataFile=false, columns=[`*`]]])
> 00-05              Project(T49¦¦*=[$0], col_prime=[$1])
> 00-07                Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///tmp/using_f1]],
selectionRoot=maprfs:/tmp/using_f1, numFiles=1, usedMetadataFile=false, columns=[`*`]]])
> {noformat}
> Whereas Postgres 9.3 returns expected results for the same data.
> {noformat}
> postgres=# SELECT * FROM using_f1 JOIN (SELECT * FROM using_f2) foo USING(col_prime);
>  col_prime |   col_dt   | col_state |                          col_varstr
>                 | col_id |     col_name      |   col_dt   | col_state |
>               col_varstr                            | col_id |       col_name
> -----------+------------+-----------+-----------------------------------------------
> ----------------+--------+-------------------+------------+-----------+-------------
> ----------------------------------------------------+--------+----------------------
>        103 | 2014-12-24 | TX        | LUW2QzWGdJfnxHrqm3vwyndzRBFwH8l5xVDaM3hTiZAanp
> j               |  19462 | Julie Lennox      | 1990-01-11 | WV        | KKzEOgle6E5h
> NANduNAAIp9DQnGLGxO                                 |  54217 | Derek Wilson
>        103 | 1985-07-18 | CA        | aYQ2uLpPxebGGRvcX0fahrAOO4yhkDRvMPES6PuYsIfwkU
> Mrcq6NSdt0j     |  48987 | Lillian Lupo      | 1990-01-11 | WV        | KKzEOgle6E5h
> NANduNAAIp9DQnGLGxO                                 |  54217 | Derek Wilson
>        103 | 1988-02-27 | SC        | OcVKheHMyeKLgcvamrJHUxKyCGGJGci3Y9ht2LI9T5Ek1n
> wckB            |  52840 | Martha Rose       | 1990-01-11 | WV        | KKzEOgle6E5h
> NANduNAAIp9DQnGLGxO                                 |  54217 | Derek Wilson
>        211 | 1989-12-06 | SD        | HHlmvV4
>                 |   1131 | Kenneth Hayes     | 1989-05-31 | MT        | yhHfCGaCqnAr
> XUCD4jRoZQ4fj6IQIKZHUGLlIsSr1L7voCE3lEmj3DOSFqJ0Kq  |  49191 | Joan Stein
>         43 | 2006-01-24 | NV        | EJAN2JjRqoQWgp7rHLT1yPMBR50g1Kil3klu1vPritFKB2
> 5EjmL1tLXleagAP |  30179 | William Strassel  | 2006-03-02 | MI        | W9G0nWo8QNtH
> r9YxOscigPbtXEtNPZ                                  |  44849 | Catherine Turner
>        193 | 1990-01-14 | NV        | 9nd3po1bnyasqINVA
>                 |  47775 | James Walters
> ...
> 1990-01-14 | NV        | 9nd3po1bnyasqINVA
>                 |  47775 | James Walters     | 1980-04-22 | ID        | jR8jr1lqDprU
> FPhAX4xZnulndYNd3                                   |   5876 | Rosie Johnson
>          5 | 2004-01-27 | KS        | 0A8Gwqm66k6wQ1KzcUdSQKZU3AZtPImxb8
>                 |  57787 | Dean Salazar      | 1997-09-13 | SC        | uq35Sqf1GfPt
> IV1mE2CzwxKaX                                       |  17041 | Dorothy Paulsen
>          5 | 1999-07-12 | UT        | hQk9DBx1egLNIpi9btvN7GPewgvPROWaNArsxAbRILW3dN
> fwi526          |  38130 | Beverly Flores    | 1997-09-13 | SC        | uq35Sqf1GfPt
> IV1mE2CzwxKaX                                       |  17041 | Dorothy Paulsen
> (239 rows)                
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Mime
View raw message