[jira] [Created] (HIVE-25212) Precision of the result set varying depending on the predicate

["Soumyakanti Das (Jira)" <ji...@apache.org>]

Soumyakanti Das created HIVE-25212:
--------------------------------------

             Summary:  Precision of the result set varying depending on the predicate
                 Key: HIVE-25212
                 URL: https://issues.apache.org/jira/browse/HIVE-25212
             Project: Hive
          Issue Type: Bug
            Reporter: Soumyakanti Das
            Assignee: Soumyakanti Das


Hive: Precision of the result set varying depending on the predicate

Problem Statement:
{noformat}
SELECT t1.c1 FROM t1 WHERE ((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) IS NULL;
-- result: [ 0.1593230600000000000000000000000000000, 0.8522372800000000000000000000000000000 ]
SELECT t1.c1 FROM t1 WHERE ((0.265)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.265)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.265)!=(t1.c1))) IS NULL;
-- result: [ 0.15932306, 0.85223728] {noformat}
Steps to reproduce:
{noformat}
DROP DATABASE IF EXISTS database0 CASCADE;
CREATE DATABASE database0;
use database0;
CREATE TABLE t1(c0 FLOAT NOT NULL, c1 DECIMAL(9,8) NOT NULL);

-- Number of Inserts for this run: 2;

INSERT INTO t1(c0, c1) VALUES(0.037977062, 0.15932306);
INSERT INTO t1(c0, c1) VALUES(0.65065473, 0.85223728);

SELECT t1.c1 FROM t1 WHERE ((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) IS NULL;
-- result: [ 0.1593230600000000000000000000000000000, 0.8522372800000000000000000000000000000 ]
SELECT t1.c1 FROM t1 WHERE ((0.265)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.265)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.265)!=(t1.c1))) IS NULL;
-- result: [ 0.15932306, 0.85223728] {noformat}
Observations:
 If the NOT NULL constraint is removed then the result sets match (ideal case is, it should not depend on the constraint)

Similarity with Impala:
 Result is as expected
{noformat}
impala database0> SELECT t1.c1 FROM t1 WHERE ((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) IS NULL;
+------------+
| c1         |
+------------+
| 0.15932306 |
| 0.85223728 |
+------------+
rows_count: 2 Time taken: 936ms

impala database0> SELECT t1.c1 FROM t1 WHERE ((0.265)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.265)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.265)!=(t1.c1))) IS NULL;
+------------+
| c1         |
+------------+
| 0.85223728 |
| 0.15932306 |
+------------+
rows_count: 2 Time taken: 887ms{noformat}
Similarity with Postgres:
{noformat}
temp=# SELECT t1.c1 FROM t1 WHERE ((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.26568513289718753700441311593749560415744781494140625)!=(t1.c1))) IS NULL;
     c1
------------
 0.15932306
 0.85223728
(2 rows)

temp=# SELECT t1.c1 FROM t1 WHERE ((0.265)!=(t1.c1)) UNION ALL SELECT t1.c1 FROM t1 WHERE NOT (((0.265)!=(t1.c1))) UNION ALL SELECT ALL t1.c1 FROM t1 WHERE (((0.265)!=(t1.c1))) IS NULL;
     c1
------------
 0.15932306
 0.85223728
(2 rows) {noformat}
Is this expected in case of Hive?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)