[
https://issues.apache.org/jira/browse/HIVE-29290?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18033772#comment-18033772
]
Raghav Aggarwal edited comment on HIVE-29290 at 10/29/25 9:03 AM:
------------------------------------------------------------------
Haven't investigated enough but disabling anti-join is yielding expected result
with the following set.
{code:java}
set hive.merge.nway.joins=true;
set hive.auto.convert.anti.join=false;{code}
was (Author: JIRAUSER295901):
Haven't investigated enough but disabling anti-join is yielding expected result
with the following set.
{code:java}
set hive.merge.nway.joins=true;
set hive.auto.convert.anti.join=false;{code}
> 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
> Affects Versions: 4.1.0, 4.0.1
> Reporter: Chinna Rao Lalam
> Priority: Major
>
> 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)
