[HIVE-14529] Union All query returns incorrect results.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.1.0
• Fix Version/s: 2.1.1
• Component/s: Query Planning

Environment

Hadoop 2.6
Hive 2.1

• Assignee: Unassigned
• Reporter: Wenhe Li
• Created: 12/Aug/16 08:17
• Updated: 26/Jul/17 15:15
• Resolved: 03/May/17 00:36

Description

create table dw_tmp.l_test1 (id bigint,val string,trans_date string) row format delimited fields terminated by ' ' ;

create table dw_tmp.l_test2 (id bigint,val string,trans_date string) row format delimited fields terminated by ' ' ;

select * from dw_tmp.l_test1;

1 table_1 2016-08-11

select * from dw_tmp.l_test2;

2 table_2 2016-08-11

– right like this

select
id,
'table_1' ,
trans_date
from dw_tmp.l_test1
union all
select
id,
val,
trans_date
from dw_tmp.l_test2 ;

1 table_1 2016-08-11
2 table_2 2016-08-11

– incorrect

select
id,
999,
'table_1' ,
trans_date
from dw_tmp.l_test1
union all
select
id,
999,
val,
trans_date
from dw_tmp.l_test2 ;

1 999 table_1 2016-08-11
2 999 table_1 2016-08-11 <-- here is wrong

– incorrect

select
id,
999,
666,
'table_1' ,
trans_date
from dw_tmp.l_test1
union all
select
id,
999,
666,
val,
trans_date
from dw_tmp.l_test2 ;

1 999 666 table_1 2016-08-11
2 999 666 table_1 2016-08-11 <-- here is wrong

– right

select
id,
999,
'table_1' ,
trans_date,
'2016-11-11'
from dw_tmp.l_test1
union all
select
id,
999,
val,
trans_date,
trans_date
from dw_tmp.l_test2 ;

1 999 table_1 2016-08-11 2016-11-11
2 999 table_2 2016-08-11 2016-08-11

Issue Links

• duplicates: HIVE-14530 Union All query returns incorrect results