You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hive.apache.org by "Soumyakanti Das (Jira)" <ji...@apache.org> on 2021/06/07 18:13:00 UTC
[jira] [Assigned] (HIVE-25212) Precision of the result set varying
depending on the predicate
[ https://issues.apache.org/jira/browse/HIVE-25212?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Soumyakanti Das reassigned HIVE-25212:
--------------------------------------
> 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
> Priority: Major
>
> 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)