Stamatis Zampetakis created HIVE-24907:
------------------------------------------
Summary: 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
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)
