[
https://issues.apache.org/jira/browse/HIVE-29175?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18018094#comment-18018094
]
Thomas Rebele edited comment on HIVE-29175 at 9/4/25 11:35 AM:
---------------------------------------------------------------
Just a thought: maybe we can keep the anti-join by pushing the corresponding
filter ({{{}t2.col2=t1.col2{}}}) plus NULL checks to the LHS? To be
investigated.
The ticket is somehow related to HIVE-29176, so maybe it makes sense to do the
PR for HIVE-29176 first.
was (Author: thomas.rebele):
Just a thought: maybe we can keep the anti-join by pushing the corresponding
filter ({{{}t2.col2=t1.col2{}}}) plus NULL checks to the LHS? To be
investigated.
> 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}
> +----------+----------+
> | t1.col1 | t1.col2 |
> +----------+----------+
> | 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)
