[
https://issues.apache.org/jira/browse/HIVE-14185?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15366959#comment-15366959
]
Gopal V commented on HIVE-14185:
--------------------------------
[~fmantlik]: because IN doesn't work the way you want.
col2 IN (null, 'A') looks like (col2 == null or col2 == 'A')
nothing is equal to null, even null itself, so it folds into (col2 == 'A'),
which implicitly does 'IS NOT NULL' internally.
If your goal is to look for nulls, equality is the wrong expression - for
example, you can filter nulls out by doing col2 == col2, which works like "IS
NOT NULL".
> Join query fails if the left table is empty and where condition searches in a
> list containing null
> --------------------------------------------------------------------------------------------------
>
> Key: HIVE-14185
> URL: https://issues.apache.org/jira/browse/HIVE-14185
> Project: Hive
> Issue Type: Bug
> Affects Versions: 1.2.1
> Reporter: Frantisek Mantlik
>
> If the table on the left hand side of the join is empty and the right hand
> side table contains data, the following query fails:
> {{SELECT * FROM left JOIN right ON left.col1=right.col1 WHERE left.col2 IN
> (null, 'A')}}
> while the following query runs without problems:
> {{SELECT * FROM left JOIN right ON left.col1=right.col1 WHERE (left.col2 IS
> NULL or left.col2 IN ('A'))}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
