[
https://issues.apache.org/jira/browse/HIVE-29290?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Denys Kuzmenko updated HIVE-29290:
----------------------------------
Fix Version/s: 4.2.0
(was: 4.3.0)
> Wrong results when n-way join contains both anti and outer join
> ---------------------------------------------------------------
>
> Key: HIVE-29290
> URL: https://issues.apache.org/jira/browse/HIVE-29290
> Project: Hive
> Issue Type: Bug
> Components: Query Processor
> Affects Versions: 4.1.0, 4.0.1
> Reporter: Chinna Rao Lalam
> Assignee: Krisztian Kasa
> Priority: Major
> Labels: correctness, pull-request-available
> Fix For: 4.2.0
>
>
> Enabling CBO and "nway.joins" returns wrong results.
>
> set hive.merge.nway.joins=true;
> set hive.cbo.enable=true;
> create table taba(id string);
> INSERT INTO TABLE taba VALUES ('1'),('2');
> create table tabb(id string);
> INSERT INTO TABLE tabb VALUES ('1');
> create table tabc(id string);
> INSERT INTO TABLE tabc VALUES ('1'),('2'),('2');
> //Full data
> select * from taba A left outer join tabb B on (A.id = B.id) left outer join
> tabc C on (C.id = A.id);
> {code}
> +-----+-------+-------+
> | id | id_1 | id_2 |
> +-----+-------+-------+
> | 1 | 1 | 1 |
> | 2 | NULL | 2 |
> | 2 | NULL | 2 |
> +-----+-------+-------+
> {code}
>
> //ID is not null
> select * from taba A left outer join chinna.tabb B on (A.id = B.id) left
> outer join chinna.tabc C on (C.id = A.id) where B.id is not null;
> {code}
> +-----+-------+-------+
> | id | id_1 | id_2 |
> +-----+-------+-------+
> | 1 | 1 | 1 |
> +-----+-------+-------+
> {code}
>
> //ID is null
> select * from taba A left outer join tabb B on (A.id = B.id) left outer join
> tabc C on (C.id = A.id) where B.id is null;
> {code}
> +-----+-------+-------+
> | id | id_1 | id_2 |
> +-----+-------+-------+
> | 1 | NULL | 1 |
> | 2 | NULL | 2 |
> | 2 | NULL | 2 |
> +-----+-------+-------+
> {code}
> In this case B.id is not null for id=1, Correct output is
> {code}
> +-----+-------+-------+
> | id | id_1 | id_2 |
> +-----+-------+-------+
> | 2 | NULL | 2 |
> | 2 | NULL | 2 |
> +-----+-------+-------+
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
