[jira] [Created] (HIVE-23435) Full outer join result is missing rows

["Naveen Gangam (Jira)" <ji...@apache.org>]

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)