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)

Reply via email to