Chinna Rao Lalam created HIVE-29290:
---------------------------------------
Summary: Enabling "hive.merge.nway.joins" returns wrong resutls
Key: HIVE-29290
URL: https://issues.apache.org/jira/browse/HIVE-29290
Project: Hive
Issue Type: Bug
Components: Query Processor
Reporter: Chinna Rao Lalam
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);
+-----+-------+-------+
| id | id_1 | id_2 |
+-----+-------+-------+
| 1 | 1 | 1 |
| 2 | NULL | 2 |
| 2 | NULL | 2 |
+-----+-------+-------+
//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;
+-----+-------+-------+
| id | id_1 | id_2 |
+-----+-------+-------+
| 1 | 1 | 1 |
+-----+-------+-------+
//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;
+-----+-------+-------+
| id | id_1 | id_2 |
+-----+-------+-------+
| 1 | NULL | 1 |
| 2 | NULL | 2 |
| 2 | NULL | 2 |
+-----+-------+-------+
In this case B.id is not null for id=1, Correct output is
+-----+-------+-------+
| id | id_1 | id_2 |
+-----+-------+-------+
| 2 | NULL | 2 |
| 2 | NULL | 2 |
+-----+-------+-------+
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
