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

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

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

Linleicheng commented on HIVE-24907:
------------------------------------

We can reproduce the issue if we generate a operator tree as below:

 
{noformat}
       ....
        Reducer 2 
            Reduce Operator Tree:
              Group By Operator
                keys: KEY._col0 (type: int)
                mode: mergepartial
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column stats: COMPLETE
            Execution mode: llap
            Reduce Operator Tree:
              Group By Operator
                keys: KEY._col0 (type: int)
                mode: mergepartial
                outputColumnNames: _col0
                Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column stats: COMPLETE
                Merge Join Operator
                  condition map:
                       Left Outer Join 0 to 1
                  keys:
                    0 _col0 (type: int)
                    1 _col0 (type: int)
                ...
        ...

{noformat}
We should avoid generating map-join operator, so explicitly setting hive.auto.convert.join to false would help.

 

> 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.20.1#820001)