[HIVE-21660] Wrong result when union all and later view with explode is used

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.1
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Physical Optimizer
• Assignee: Ganesha Shreedhara
• Reporter: Ganesha Shreedhara
• Created: 29/Apr/19 06:37
• Updated: 27/Feb/24 22:23
• Resolved: 10/Mar/20 19:24

Description

There is a data loss when the data is inserted to a partitioned table using union all and lateral view with explode. 

 

Steps to reproduce:

 

create table t1 (id int, dt string);
insert into t1 values (2, '2019-04-01');
create table t2( id int, dates array<string>);
insert into t2 select 1 as id, array('2019-01-01','2019-01-02','2019-01-03') as dates;

create table dst (id int) partitioned by (dt string);

set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
insert overwrite table dst partition (dt)
select t.id, t.dt from (
select id, dt from t1
union all
select id, dts as dt from t2 tt2 lateral view explode(tt2.dates) dd as dts ) t;
select * from dst;

 

 

Actual Result:

+--------------+--------------+
| 2            | 2019-04-01   |
+--------------+--------------+

 

Expected Result (Run only the select part from the above insert query): 

+-------+------------+
| 2     | 2019-04-01 |
| 1     | 2019-01-01 |
| 1     | 2019-01-02 |
| 1     | 2019-01-03 |
+-------+------------+

 

Data retrieved using union all and lateral view with explode from second table is missing. 

Issue Links

• is duplicated by: HIVE-26872 INSERT UNION with LATERAL VIEW does not produce data
• links to: RB request