[HIVE-23435] Full outer join result is missing rows

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.1.0
• Fix Version/s: 4.0.0-alpha-1
• Component/s: HiveServer2
• Labels: pull-request-available
• Assignee: Mustafa İman
• Reporter: Naveen Gangam
• Created: 11/May/20 12:45
• Updated: 17/Nov/22 08:47
• Resolved: 01/Jun/20 17:27

Description

Full Outer join result has missing rows. Appears to be a bug with the full outer join logic. Expected output is receiving when we do a left and right outer join.

Reproducible steps are mentioned below.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SUPPORT ANALYSIS

Steps to Reproduce:

1. Create a table and insert data:

create table x (z char(5), x int, y int);

insert into x values ('one', 1, 50),
('two', 2, 30),
('three', 3, 30),
('four', 4, 60),
('five', 5, 70),
('six', 6, 80);

2. Try full outer with the below command. The result is incomplete, it is missing the row:

NULL NULL NULL three 3 30.0
Full Outer Join:

select x1.`z`, x1.`x`, x1.`y`, x2.`z`,
x2.`x`, x2.`y`
from `x` x1 full outer join
`x` x2 on (x1.`x` > 3) and (x2.`x` < 4) and (x1.`x` =
x2.`x`);

Result:

----------------------------------+

x1.z x1.x x1.y x2.z x2.x x2.y
----------------------------------+

one 1 50 NULL NULL NULL
NULL NULL NULL one 1 50
two 2 30 NULL NULL NULL
NULL NULL NULL two 2 30
three 3 30 NULL NULL NULL
four 4 60 NULL NULL NULL
NULL NULL NULL four 4 60
five 5 70 NULL NULL NULL
NULL NULL NULL five 5 70
six 6 80 NULL NULL NULL
NULL NULL NULL six 6 80
----------------------------------+

3. Expected output is coming when we use left/right join + union:

select x1.`z`, x1.`x`, x1.`y`, x2.`z`,
x2.`x`, x2.`y`
from `x` x1 left outer join
`x` x2 on (x1.`x` > 3) and (x2.`x` < 4) and (x1.`x` =
x2.`x`)
union
select x1.`z`, x1.`x`, x1.`y`, x2.`z`,
x2.`x`, x2.`y`
from `x` x1 right outer join
`x` x2 on (x1.`x` > 3) and (x2.`x` < 4) and (x1.`x` =
x2.`x`);

Result:

------------------------------------+

z x y _col3 _col4 _col5
------------------------------------+

NULL NULL NULL five 5 70
NULL NULL NULL four 4 60
NULL NULL NULL one 1 50
four 4 60 NULL NULL NULL
one 1 50 NULL NULL NULL
six 6 80 NULL NULL NULL
three 3 30 NULL NULL NULL
two 2 30 NULL NULL NULL
NULL NULL NULL six 6 80
NULL NULL NULL three 3 30
NULL NULL NULL two 2 30
five 5 70 NULL NULL NULL
------------------------------------+

 

Issue Links

• links to: GitHub Pull Request #1039