Sahil Takiar created HIVE-14971:
-----------------------------------
Summary: Hive returns incorrect result when NOT used with <=>
(null safe equals) operator
Key: HIVE-14971
URL: https://issues.apache.org/jira/browse/HIVE-14971
Project: Hive
Issue Type: Bug
Components: Hive
Reporter: Sahil Takiar
Assignee: Sahil Takiar
Hive returns incorrect results when using a <=> inside a NOT operator:
*Setup:*
{code}
create table table1 (int_col_1 int, int_col_2 int);
insert into table1 values (1, 2), (3, 3), (null, 4), (5, null), (null, null);
select * from table1;
+-------------------+-------------------+
| table1.int_col_1 | table1.int_col_2 |
+-------------------+-------------------+
| 1 | 2 |
| 3 | 3 |
| NULL | 4 |
| 5 | NULL |
| NULL | NULL |
+-------------------+-------------------+
{code}
The following query returns incorrect results: {{select int_col_1 from table1
where not(int_col_1 <=> int_col_2)}} returns
{code}
+------------+
| int_col_1 |
+------------+
| 1 |
+------------+
{code}
Where it should return {{1, NULL, 5}}
Here is another query that returns incorrect results: {{select *, not(int_col_1
<=> int_col_2) from table1}}
{code}
+-------------------+-------------------+--------+
| table1.int_col_1 | table1.int_col_2 | _c1 |
+-------------------+-------------------+--------+
| 1 | 2 | true |
| 3 | 3 | false |
| NULL | 4 | NULL |
| 5 | NULL | NULL |
| NULL | NULL | NULL |
+-------------------+-------------------+--------+
{code}
The column {{_c1}} should not be returning {{NULL}} for the last three rows.
This could be a bug in the NOT operator because the query {{select *, int_col_1
<=> int_col_2 from table1}} returns:
{code}
+-------------------+-------------------+--------+
| table1.int_col_1 | table1.int_col_2 | _c1 |
+-------------------+-------------------+--------+
| 1 | 2 | false |
| 3 | 3 | true |
| NULL | 4 | false |
| 5 | NULL | false |
| NULL | NULL | true |
+-------------------+-------------------+--------+
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
