Hive left join指定where条件的问题

发表于 | 分类于 | 阅读次数：

先来看下两组sql

sql1:

1
2
3
4
5
6
7
8
9
10
select
if(t2.foo is null, true, false)
, if(t2.foo is not null, true, false)
, t1.*, t2.*
from (select * from dual) t1
left join (select 'a' as foo from dual) t2
on t1.foo = t2.foo ;

result:
true    false   x       NULL

sql2:

1
2
3
4
5
6
7
8
9
10
select
if(t2.foo is null, true, false)
, if(t2.foo is not null, true, false)
, t1.*, t2.*
from (select * from dual where foo = 'x') t1
left join (select 'a' as foo from dual) t2
on t1.foo = t2.foo ;

result:
false   true    x       NULL

问题描述：两组几乎同样的sql，一个指定了where条件，一个没指定where条件，两组结果出现明显的逻辑错误
解决办法：set hive.auto.convert.join=false