[jira] [Created] (HIVE-24907) Wrong results with LEFT JOIN and subqueries with UNION and GROUP BY

["Stamatis Zampetakis (Jira)" <ji...@apache.org>]

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)