[
https://issues.apache.org/jira/browse/HIVE-29175?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18017853#comment-18017853
]
Thomas Rebele commented on HIVE-29175:
--------------------------------------
I had simplified the query and forgot to copy-paste the INSERT statements.
Fixed.
> 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)
