[jira] [Created] (HIVE-10931) Wrong columns selected on multiple joins

["Furcy Pin (JIRA)" <ji...@apache.org>]

Furcy Pin created HIVE-10931:
--------------------------------

             Summary: Wrong columns selected on multiple joins
                 Key: HIVE-10931
                 URL: https://issues.apache.org/jira/browse/HIVE-10931
             Project: Hive
          Issue Type: Bug
    Affects Versions: 1.1.0
         Environment: Cloudera cdh5.4.2
            Reporter: Furcy Pin


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






--
This message was sent by Atlassian JIRA
(v6.3.4#6332)