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