[HIVE-26135] Invalid Anti join conversion may cause missing results

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-2
• Labels: pull-request-available
• Assignee: Zoltan Haindrich
• Reporter: Zoltan Haindrich
• Created: 12/Apr/22 12:50
• Updated: 16/Nov/22 13:50
• Resolved: 26/Apr/22 15:23

Description

right now I think the following is needed to trigger the issue:

• left outer join
• only select left hand side columns
• conditional which is using some udf
• the nullness of the udf is checked

repro sql; in case the conversion happens the row with 'a' will be missing

drop table if exists t;
drop table if exists n;

create table t(a string) stored as orc;
create table n(a string) stored as orc;

insert into t values ('a'),('1'),('2'),(null);
insert into n values ('a'),('b'),('1'),('3'),(null);


explain select n.* from n left outer join t on (n.a=t.a) where assert_true(t.a is null) is null;
explain select n.* from n left outer join t on (n.a=t.a) where cast(t.a as float) is null;


select n.* from n left outer join t on (n.a=t.a) where cast(t.a as float) is null;
set hive.auto.convert.anti.join=false;
select n.* from n left outer join t on (n.a=t.a) where cast(t.a as float) is null;

resultset with hive.auto.convert.anti.join enabled:

+------+
| n.a  |
+------+
| b    |
| 3    |
+------+

correct resultset with hive.auto.convert.anti.join disabled:

+-------+
|  n.a  |
+-------+
| a     |
| b     |
| 3     |
| NULL  |
+-------+

workaround could be to disable the feature:

set hive.auto.convert.anti.join=false;

Issue Links

• is caused by: HIVE-23716 Support Anti Join in Hive
• links to: GitHub Pull Request #3205