[jira] [Commented] (HIVE-14185) Join query fails if the left table is empty and where condition searches in a list containing null

["Gopal V (JIRA)" <ji...@apache.org>]

    [ 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)