[HIVE-25721] Outer join result is wrong

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: HiveServer2
• Labels: pull-request-available
• Assignee: Yizhen Fan
• Reporter: Yizhen Fan
• Created: 18/Nov/21 05:54
• Updated: 10/Jul/24 14:26
• Resolved: 24/Nov/21 15:38

Description

Outer Join results is wrong, here is a left join case.

select b.fields from a left join b on a.key=b.key and a.filter=xxx

there are some necessary condition to produce this problem:

1. `select` clause only contains right table fields
2. `on` clause contains left table condition, and this condition can filter records 

cause:

candidateStorage[tag].addRow(value); // CommonMergeJoinOperator.process

row of left table cannot be add into row container because tblDesc of left table is null, while left table data can not be ignored in this case.

Reproducible steps are mentioned below.

--------------------------------------------

set hive.auto.convert.join=false;

create table t_smj_left (key string, value int);

insert into t_smj_left values
('key1', 1),
('key1', 2);

create table t_smj_right (key string, value int);

insert into t_smj_right values
('key1', 1);

select
t2.value
from t_smj_left t1
left join t_smj_right t2 on t1.key=t2.key and t1.value=2;

 

Result:

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

NULL
NULL

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

Expected Output:

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

1

NULL

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

Issue Links

• links to: GitHub Pull Request #2798