[
https://issues.apache.org/jira/browse/HIVE-29176?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18018038#comment-18018038
]
Stamatis Zampetakis commented on HIVE-29176:
--------------------------------------------
Since this is a correctness issue, I would keep the "wrong results" in the
summary. Also it would be helpful to highlight which results are the correct
(with or without anti-join).
> AntiJoin may not be introduced when the IS NULL filter is applied to a
> nullable column
> --------------------------------------------------------------------------------------
>
> Key: HIVE-29176
> URL: https://issues.apache.org/jira/browse/HIVE-29176
> Project: Hive
> Issue Type: Bug
> Reporter: Thomas Rebele
> Assignee: Thomas Rebele
> Priority: Major
>
> Given the following script:
> {code:java}
> --! qt:dataset:src
> --! qt:dataset:part
> -- SORT_QUERY_RESULTS
> create table t1_n55 as select cast(key as int) key, value from src where key
> <= 10;
> select * from t1_n55 sort by key;
> create table t2_n33 as select cast(2*key as int) key, value from t1_n55 union
> select 10 key, null value;
> select * from t2_n33 sort by key;
> create table t3_n12 as select * from (select * from t1_n55 union all select *
> from t2_n33) b;
> select * from t3_n12 sort by key, value;
> SET hive.auto.convert.anti.join=true;
> select "\nantijoin=true\n";
> explain cbo select a.key, a.value from t1_n55 a left join t2_n33 b on
> a.key=b.key where b.value is null;
> select a.key, a.value from t1_n55 a left join t2_n33 b on a.key=b.key where
> b.value is null;
> SET hive.auto.convert.anti.join=false;
> select "\nantijoin=false\n";
> explain cbo select a.key, a.value from t1_n55 a left join t2_n33 b on
> a.key=b.key where b.value is null;
> select a.key, a.value from t1_n55 a left join t2_n33 b on a.key=b.key where
> b.value is null;
> {code}
> There's a difference when executing the query with/without anti-join:
> Result with antijoin:
> {code:java}
> 2 val_2
> 5 val_5
> 5 val_5
> 5 val_5
> 9 val_9
> {code}
> Result without antijoin:
> {code:java}
> 10 val_10
> 2 val_2
> 5 val_5
> 5 val_5
> 5 val_5
> 9 val_9
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
