Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-24907

Wrong results with LEFT JOIN and subqueries with UNION and GROUP BY

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 2.4.0, 3.2.0, 4.0.0
    • None
    • Query Processor
    • None

    Description

      The following SQL query returns wrong results when run in TEZ/LLAP:

      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;
      

      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.

      Attachments

        Issue Links

          Activity

            People

              zabetak Stamatis Zampetakis
              zabetak Stamatis Zampetakis
              Votes:
              0 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated: