Union All query returns incorrect results.

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