Naveen Gangam created HIVE-23435: ------------------------------------ Summary: Full outer join result is missing rows Key: HIVE-23435 URL: https://issues.apache.org/jira/browse/HIVE-23435 Project: Hive Issue Type: Bug Components: HiveServer2 Affects Versions: 3.1.0 Reporter: Naveen Gangam Assignee: Jesus Camacho Rodriguez
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 ------------------------------------+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ EXPECTED ENGINEERING ACTION Confirm this is a bug. If so, any work around or just use left+right outer join. -- This message was sent by Atlassian Jira (v8.3.4#803005)