[
https://issues.apache.org/jira/browse/HIVE-10931?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14616478#comment-14616478
]
Furcy Pin commented on HIVE-10931:
----------------------------------
I tried to reproduce on a local environment on the release-1.2.1 branch, and
the bug has disappeared.
I guess the issue can be closed.
> 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 :
> {code:sql}
> 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
> ;
> {code}
> will return this :
> {noformat}
> +----------+--+
> | t2.val |
> +----------+--+
> | A |
> +----------+--+
> {noformat}
> 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 :
> {code:sql}
> 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
> ;
> {code}
> (same query as before, but `col5` was removed from the select)
> will return :
> {noformat}
> +----------+--+
> | t2.val |
> +----------+--+
> | X |
> +----------+--+
> {noformat}
> Removing the `COALESCE` also removes the bug...
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
