[IMPALA-2203] CTAS and IAS produce different results vs SELECT when using FULL JOIN

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: Impala 2.2
• Fix Version/s: Impala 2.2.7, Impala 2.3.0
• Labels: correctness
• Target Version: Impala 2.2.7, Impala 2.3.0
• Assignee: Alexander Behm
• Reporter: Juan Yu
• Created: 12/Aug/15 22:03
• Updated: 20/Dec/15 00:05
• Resolved: 18/Aug/15 05:19

Description

where CTAS and IAS produce different results as a plain SELECT when using FULL JOINs and static value columns.

It's a regression. Seen in Impala 2.1.3+ (starting in CDH 5.3.5) but not earlier version.

Performing the same steps in Hive produce expected results. Also verified expected results in postgresql.

Example:

create table raw (id STRING, val STRING);
insert into raw values ('user1','one'), ('user2','two');
select * from raw;
+-------+-----+
| id | val |
+-------+-----+
| user1 | one |
| user2 | two |
+-------+-----+

select a.id as a_id, b.id as b_id, one, two from ( select id, 'Y' as one from raw where val = 'one' group by id ) a full join ( select id, 'Y' as two from raw where val = 'two' group by id ) b on a.id = b.id;
+-------+-------+------+------+
| a_id | b_id | one | two |
+-------+-------+------+------+
| user1 | NULL | Y | NULL |
| NULL | user2 | NULL | Y |
+-------+-------+------+------+

create table test1 as select a.id as a_id, b.id as b_id, one, two from ( select id, 'Y' as one from raw where val = 'one' group by id ) a full join ( select id, 'Y' as two from raw where val = 'two' group by id ) b on a.id = b.id;

select * from test1;
+-------+-------+-----+-----+
| a_id | b_id | one | two |
+-------+-------+-----+-----+
| user1 | NULL | Y | Y |
| NULL | user2 | Y | Y |
+-------+-------+-----+-----+

Testing using INSERT also produces the same incorrect result.

Workaround
To produce correct results, you can rewrite the with a UNION ALL to force the inline-view results to be materialized.
Before CTAS with incorrect results:

create table test1 as select a.id as a_id, b.id as b_id, one, two from
  (select id, 'Y' as one from raw where val = 'one' group by id) a
full join
  (select id, 'Y' as two from raw where val = 'two' group by id) b
on a.id = b.id;

After CTAS with correct results:

create table test1 as select a.id as a_id, b.id as b_id, one, two from
  (select id, 'Y' as one from raw where val = 'one' group by id
   union all <-- This is the workaround
   select NULL, NULL from raw where false) a <-- This is the workaround
full join
  (select id, 'Y' as two from raw where val = 'two' group by id
   union all <-- This is the workaround 
   select NULL, NULL from raw where false) b <-- This is the workaround
on a.id = b.id;