[
https://issues.apache.org/jira/browse/HIVE-14971?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sahil Takiar updated HIVE-14971:
--------------------------------
Description:
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}
was:
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}
> 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)
