[
https://issues.apache.org/jira/browse/HIVE-25721?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
范宜臻 updated HIVE-25721:
-----------------------
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:
# `select` clause only contains right table fields
# `on` clause contains left table condition, and this condition can filter
records
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
----------------------------+
> Outer join result is wrong
> --------------------------
>
> Key: HIVE-25721
> URL: https://issues.apache.org/jira/browse/HIVE-25721
> Project: Hive
> Issue Type: Bug
> Components: HiveServer2
> Affects Versions: All Versions
> Reporter: 范宜臻
> Assignee: 范宜臻
> Priority: Major
> Fix For: 4.0.0
>
>
> 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:
> # `select` clause only contains right table fields
> # `on` clause contains left table condition, and this condition can filter
> records
> 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
> ----------------------------+
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
