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

Wrong columns selected on multiple joins

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Cannot Reproduce
    • Affects Version/s: 1.1.0
    • Fix Version/s: 1.2.1
    • Component/s: None
    • Labels:
      None
    • Environment:

      Cloudera cdh5.4.2

      Description

      The following set of queries :

      DROP TABLE IF EXISTS test1 ;
      DROP TABLE IF EXISTS test2 ;
      DROP TABLE IF EXISTS test3 ;
      
      CREATE TABLE test1 (col1 INT, col2 STRING, col3 STRING, coL4 STRING, coL5 STRING, col6 STRING) ;
      INSERT INTO TABLE test1 VALUES (1,NULL,NULL,NULL,NULL,"A") ;
      
      CREATE TABLE test2 (col1 INT, col2 STRING, col3 STRING, coL4 STRING, coL5 STRING, col6 STRING) ;
      INSERT INTO TABLE test2 VALUES (1,NULL,NULL,NULL,NULL,"X") ;
      
      CREATE TABLE test3 (coL1 STRING) ;
      INSERT INTO TABLE test3 VALUES ("A") ;
      
      SELECT
        T2.val
      FROM test1 T1
      LEFT JOIN (SELECT col1, col2, col3, col4, col5,  COALESCE(col6,"") as val FROM test2) T2
      ON T2.col1 = T1.col1
      LEFT JOIN test3 T3  
      ON T3.col1 = T1.col6 
      ;
      

      will return this :

      +----------+--+
      | t2.val   |
      +----------+--+
      | A        |
      +----------+--+
      

      Obviously, this result is wrong as table `test2` contains a "X" and no "A".

      This is the most minimal example we found of this issue, in particular
      having less than 6 columns in the tables will work, for instance :

      SELECT
        T2.val
      FROM test1 T1
      LEFT JOIN (SELECT col1, col2, col3, col4, COALESCE(col6,"") as val FROM test2) T2
      ON T2.col1 = T1.col1
      LEFT JOIN test3 T3  
      ON T3.col1 = T1.col6 
      ;
      

      (same query as before, but `col5` was removed from the select)
      will return :

      +----------+--+
      | t2.val   |
      +----------+--+
      | X        |
      +----------+--+
      

      Removing the `COALESCE` also removes the bug...

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              fpin Furcy Pin
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: