[
https://issues.apache.org/jira/browse/HIVE-29175?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Thomas Rebele updated HIVE-29175:
---------------------------------
Description:
Wrong Anti join conversion leading to missing results.
Reproduction steps:
{noformat}
create table tab1 (col1 int, col2 int);
create table tab2 (col1 int, col2 int);
insert into tab1 values
(123, 1000),
(456, 1000),
(123, 5000);
insert into tab2 values
(123, 1000),
(456, 1000),
(788, 1000);
select t1.col1, t1.col2 from tab1 t1
left join tab2 t2
on t2.col1=t1.col1 AND t2.col2=t1.col2
left join tab1 t3
on t3.col1=t1.col1 AND t2.col2=t1.col2
where t3.col1 is null;{noformat}
When
*set hive.auto.convert.anti.join=false;*
then above select query returning below rows
{noformat}
123 5000{noformat}
but when
*set hive.auto.convert.anti.join=true;*
then its returning empty rows.
was:
Wrong Anti join conversion leading to missing results.
Reproduction steps:
{noformat}
create table tab1 (col1 int, col2 int);
create table tab2 (col1 int, col2 int);
select t1.col1, t1.col2 from tab1 t1
left join tab2 t2
on t2.col1=t1.col1 AND t2.col2=t1.col2
left join tab1 t3
on t3.col1=t1.col1 AND t2.col2=t1.col2
where t3.col1 is null;{noformat}
When
*set hive.auto.convert.anti.join=false;*
then above select query returning below rows
{noformat}
123 5000{noformat}
but when
*set hive.auto.convert.anti.join=true;*
then its returning empty rows.
> Wrong Anti join conversion leading to missing results
> -----------------------------------------------------
>
> Key: HIVE-29175
> URL: https://issues.apache.org/jira/browse/HIVE-29175
> Project: Hive
> Issue Type: Bug
> Components: Hive
> Reporter: Dayakar M
> Assignee: Dayakar M
> Priority: Major
> Labels: pull-request-available
>
> Wrong Anti join conversion leading to missing results.
> Reproduction steps:
> {noformat}
> create table tab1 (col1 int, col2 int);
> create table tab2 (col1 int, col2 int);
> insert into tab1 values
> (123, 1000),
> (456, 1000),
> (123, 5000);
> insert into tab2 values
> (123, 1000),
> (456, 1000),
> (788, 1000);
> select t1.col1, t1.col2 from tab1 t1
> left join tab2 t2
> on t2.col1=t1.col1 AND t2.col2=t1.col2
> left join tab1 t3
> on t3.col1=t1.col1 AND t2.col2=t1.col2
> where t3.col1 is null;{noformat}
> When
> *set hive.auto.convert.anti.join=false;*
> then above select query returning below rows
> {noformat}
> 123 5000{noformat}
> but when
> *set hive.auto.convert.anti.join=true;*
> then its returning empty rows.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
