presto: != operator is not working correctly in presto for decimal values

I connected hive with presto and query a table containing decimal values. In case of != operator it’s showing me all the rows of the table.

Below are the create table and load queries for hive:

create table decimalOperatorCheck(name String, ids decimal(10,2))ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

Load data: load data local inpath '/home/hduser/Files/decimaldata.csv' into table decimalOperatorCheck;

Query in Presto CLI: select * from decimaloperatorcheck where ids !=12345.56;

Output:

presto:default> select * from decimaloperatorcheck where ids !=12345.56; name | ids
--------±--------- Justin | 11.90
Josh | 233.34
Alex | 123.45
Ryan | 12345.56 (4 rows)

Here is the csv used for loading data. data.txt

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 18 (16 by maintainers)

Most upvoted comments

Actually this is not strictly decimal related. It is implication of three facts:

numeric literals with dot (e.g 12345.56) are interpreted in Presto as literals of type DOUBLE
if there is comparison of values of DECIMAL and DOUBLE type the comparison is happening in DOUBLE domain (the decimal value is first cast to DOUBLE).
you should not expect accurate equality semantics on approximate types like DOUBLE

So the query

select * from decimaloperatorcheck where ids !=12345.56;

is effectively executing as:

select * from decimaloperatorcheck where cast(ids as DOUBLE) != 12345.56;

If you explicitly rewrite query so it is happening in DECIMAL domain using appropriate literal constructor the query will hopefully pass:

select * from decimaloperatorcheck where ids != DECIMAL '12345.56';

We have plans straightening that up but it is somewhat larger effort. So far pushed out of the priority queue.

losipiuk on Jun 26, 2017

I think so. Followup from #8409 is still valid, yet it is not related to this issue.

losipiuk on Jun 12, 2018