[
https://issues.apache.org/jira/browse/HIVE-24907?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17305289#comment-17305289
]
Sungwoo commented on HIVE-24907:
--------------------------------
Hi [~zabetak],
I reproduced the wrong result. The result depends on the configuration key
hive.auto.convert.join, so explicitly setting it to false would help.
{noformat}
0: jdbc:hive2://10.1.1.39:9852/> set hive.auto.convert.join=true;
No rows affected (0.004 seconds)
0: jdbc:hive2://10.1.1.39:9852/> SELECT sub1.key, sub2.key
. . . . . . . . . . . . . . . .> FROM
. . . . . . . . . . . . . . . .> (SELECT a.key FROM tbl a GROUP BY a.key) sub1
. . . . . . . . . . . . . . . .> LEFT OUTER JOIN (
. . . . . . . . . . . . . . . .> SELECT b.key FROM tbl b WHERE b.value = 2001
GROUP BY b.key
. . . . . . . . . . . . . . . .> UNION
. . . . . . . . . . . . . . . .> SELECT c.key FROM tbl c WHERE c.value = 2005
GROUP BY c.key) sub2
. . . . . . . . . . . . . . . .> ON sub1.key = sub2.key;
+-----------+-----------+
| sub1.key | sub2.key |
+-----------+-----------+
| 1 | NULL |
| 2 | 2 |
| 3 | 3 |
+-----------+-----------+
3 rows selected (11.487 seconds)
0: jdbc:hive2://10.1.1.39:9852/> set hive.auto.convert.join=false;
No rows affected (0.004 seconds)
0: jdbc:hive2://10.1.1.39:9852/> SELECT sub1.key, sub2.key
. . . . . . . . . . . . . . . .> FROM
. . . . . . . . . . . . . . . .> (SELECT a.key FROM tbl a GROUP BY a.key) sub1
. . . . . . . . . . . . . . . .> LEFT OUTER JOIN (
. . . . . . . . . . . . . . . .> SELECT b.key FROM tbl b WHERE b.value = 2001
GROUP BY b.key
. . . . . . . . . . . . . . . .> UNION
. . . . . . . . . . . . . . . .> SELECT c.key FROM tbl c WHERE c.value = 2005
GROUP BY c.key) sub2
. . . . . . . . . . . . . . . .> ON sub1.key = sub2.key;
+-----------+-----------+
| sub1.key | sub2.key |
+-----------+-----------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
+-----------+-----------+
3 rows selected (7.925 seconds)
{noformat}
> Wrong results with LEFT JOIN and subqueries with UNION and GROUP BY
> -------------------------------------------------------------------
>
> Key: HIVE-24907
> URL: https://issues.apache.org/jira/browse/HIVE-24907
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 2.4.0, 3.2.0, 4.0.0
> Reporter: Stamatis Zampetakis
> Assignee: Stamatis Zampetakis
> Priority: Major
>
> The following SQL query returns wrong results when run in TEZ/LLAP:
> {code:sql}
> SET hive.auto.convert.sortmerge.join=true;
> CREATE TABLE tbl (key int,value int);
> INSERT INTO tbl VALUES (1, 2000);
> INSERT INTO tbl VALUES (2, 2001);
> INSERT INTO tbl VALUES (3, 2005);
> SELECT sub1.key, sub2.key
> FROM
> (SELECT a.key FROM tbl a GROUP BY a.key) sub1
> LEFT OUTER JOIN (
> SELECT b.key FROM tbl b WHERE b.value = 2001 GROUP BY b.key
> UNION
> SELECT c.key FROM tbl c WHERE c.value = 2005 GROUP BY c.key) sub2
> ON sub1.key = sub2.key;
> {code}
> Actual results:
> ||SUB1.KEY||SUB2.KEY||
> |1|NULL|
> |2|NULL|
> |3|NULL|
> Expected results:
> ||SUB1.KEY||SUB2.KEY||
> |1|NULL|
> |2|2|
> |3|3|
> Tested can be reproduced with {{TestMiniLlapLocalCliDriver}} or
> {{TestMiniTezCliDriver}} in older versions of Hive.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
