[HIVE-10931] Wrong columns selected on multiple joins

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

Environment

Cloudera cdh5.4.2

• Assignee: Unassigned
• Reporter: Furcy Pin
• Created: 04/Jun/15 11:08
• Updated: 12/Aug/15 00:57
• Resolved: 12/Aug/15 00:57

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...