CODEWITHSUNDEEP
mysqlsql
Capfka
Capfka

Reputation: 453

MySQL Floating Point Value Query Issue

I have a rather strange problem with a floating point value in a table. In my PRODUCT table I have a MIN_QUANTITY field which contains 0, 0.01, 0.25 and 1 as values. I wanted to know how many of the rows had 0.01 as the MIN_QUANTITY field, so I used this query:

SELECT COUNT(*) FROM PRODUCT WHERE MIN_QUANTITY = 0.01;

The query returned 0. This is plain wrong, because there are roughly 1,200 instances of 0.01 in this field. So I tried:

SELECT COUNT(*) FROM PRODUCT WHERE MIN_QUANTITY = 0.25;

This query duly returned 2,105 which is the right count.

I've been reading that MySQL sometimes does funny things with floating point values, but surely a straight count of an uncalculated value in a field should return the right number - or am I doing something wrong?

Thanks in advance for any advice!

Upvotes: 0

Views: 133

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You've discovered why floating point numbers are not a good idea for storing exact amounts. Use decimal instead. This is a fixed-point representation.

If you cannot change data types in tables, then add a tolerance and use between:

SELECT COUNT(*)
FROM PRODUCT
WHERE MIN_QUANTITY between 0.1 - 0.0001 and 0.25 + 0.0001;

With this formulation, you can still use an index on MIN_QUANTITY. If you were to do something like this instead:

SELECT COUNT(*)
FROM PRODUCT
WHERE cast(MIN_QUANTITY as decimal(10, 2)) = 0.1;

then you would lost the ability to take advantage of an index.

Upvotes: 1

Related Questions