Furcy Pin created HIVE-11192:
--------------------------------
Summary: 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)
