[
https://issues.apache.org/jira/browse/HIVE-11192?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14616963#comment-14616963
]
Furcy Pin commented on HIVE-11192:
----------------------------------
My apologies, this was not a bug but the expected (confusing IMHO) behavior for
SQL.
SELECT 1 IN (1,2,3,NULL) ;
> true
SELECT 1 IN (2,3) ;
> false
SELECT 1 IN (2,3,NULL) ;
> NULL
SELECT 1 NOT IN (1,2,3,NULL) ;
> false
SELECT 1 NOT IN (2,3,NULL) ;
> NULL
SELECT 1 NOT IN (2,3) ;
> true
> Wrong results for query with WHERE ... NOT IN when table has null values
> ------------------------------------------------------------------------
>
> Key: HIVE-11192
> URL: https://issues.apache.org/jira/browse/HIVE-11192
> Project: Hive
> Issue Type: Bug
> Affects Versions: 1.1.0, 1.2.1
> Environment: Hive on MR
> Reporter: Furcy Pin
>
> I tested this on cdh5.4.2 cluster and locally on the release-1.2.1 branch
> ```sql
> DROP TABLE IF EXISTS test1 ;
> DROP TABLE IF EXISTS test2 ;
> CREATE TABLE test1 (col1 STRING) ;
> INSERT INTO TABLE test1 VALUES ("1"), ("2"), ("3"), ("4") ;
> CREATE TABLE test2 (col1 STRING) ;
> INSERT INTO TABLE test2 VALUES ("1"), ("4"), (NULL) ;
> SELECT
> COUNT(1)
> FROM test1 T1
> WHERE T1.col1 NOT IN (SELECT col1 FROM test2)
> ;
> SELECT
> COUNT(1)
> FROM test1 T1
> WHERE T1.col1 NOT IN (SELECT col1 FROM test2 WHERE col1 IS NOT NULL)
> ;
> ```
> The first query returns 0 and the second returns 2.
> Obviously, the expected answer is always 2.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
