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