[
https://issues.apache.org/jira/browse/HIVE-14971?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15589255#comment-15589255
]
Sahil Takiar edited comment on HIVE-14971 at 10/19/16 4:58 PM:
---------------------------------------------------------------
These queries doesn't work either:
* {{select not(2 <=> NULL);}}
* {{select not(NULL <=> NULL);}}
In Hive they both return {{NULL}}, in MySQL they return false and true,
respectively.
was (Author: stakiar):
These queries doesn't work either:
* {{select not(2 <=> NULL);}}
* {{select not(NULL <=> NULL);}}
They both return {{NULL}}, in MySQL they return false and true, respectively.
> 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:
> {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)
