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)