Dayakar M created HIVE-29175:
--------------------------------
Summary: 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
Wrong Anti join conversion leading to missing results.
Reproduction steps:
{noformat}
create table tab1 (col1 int, col2 int, col3 int, col4 int);
create table tab2 (col1 int, col2 int, col3 int, col4 int);
insert into tab1 values
(123, 1000, 5000, 9),
(456, 1000, 7000, 7),
(789, 1000, 5000, 8);
insert into tab2 values
(123, 1000, 5000, 2),
(456, 1000, 7000, 7),
(123, 5000, 4000, 2);
select t1.col1, t1.col2, t1.col3 from tab2 t1
left join tab1 t2
on t2.col3=t1.col2 AND t2.col1=t1.col1
left join tab2 t3
on t3.col1=t1.col1 AND t2.col3=t1.col3
where t1.col4=2 AND t3.col1 is null;{noformat}
When
*set hive.auto.convert.anti.join=false;*
then above select query returning below rows
{noformat}
123 1000 5000
123 5000 4000{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)
