MySQL floating point comparison issues

Question

I ran into an issue by introducing floating point columns in the MySQL database schema that the comparisons on floating point values don't return the correct results always.

1 - 50.12
2 - 34.57
3 - 12.75
4 - ...(rest all less than 12.00)

SELECT COUNT(*) FROM `users` WHERE `points` > "12.75"

This returns me "3".

I have read that the comparisons of floating point values in MySQL is a bad idea and decimal type is the better option.

Do I have any hope of moving ahead with the float type and get the comparisons to work correctly?

What kinds of literals are enclosed in double quotation marks in SQL? — Joey, Apr 02 '10 at 15:25
Unfortunately, MySQL allows double quotes to act like single-quotes by default. This feature can be turned off with the `ANSI_QUOTES` option, which will make them refer to identifiers as per the ANSI SQL standard (like the non-standard backticks in the above query). — bobince, Apr 02 '10 at 15:56
12.75 is exactly representable in binary (1100.11), so I don't see how it passes the test "> 12.75". Are you sure there's no other point > 12.75 in your list? — Rick Regan, Apr 03 '10 at 22:43

Daniel Vassallo · Accepted Answer · 2010-04-08T21:03:16.247

31

Do you notice the problem below?

CREATE TABLE a (num float);

INSERT INTO a VALUES (50.12);
INSERT INTO a VALUES (34.57);
INSERT INTO a VALUES (12.75);
INSERT INTO a VALUES (11.22);
INSERT INTO a VALUES (10.46);
INSERT INTO a VALUES (9.35);
INSERT INTO a VALUES (8.55);
INSERT INTO a VALUES (7.23);
INSERT INTO a VALUES (6.53);
INSERT INTO a VALUES (5.15);
INSERT INTO a VALUES (4.01);

SELECT SUM(num) FROM a;
+-----------------+
| SUM(num)        |
+-----------------+
| 159.94000005722 | 
+-----------------+

There's an extra 0.00000005722 spread between some of those rows. Therefore some of those values will return false when compared with the value they were initialized with.

To avoid problems with floating-point arithmetic and comparisons, you should use the DECIMAL data type:

ALTER TABLE a MODIFY num DECIMAL(6,2);

SELECT SUM(num) FROM a;
+----------+
| SUM(num) |
+----------+
|   159.94 | 
+----------+
1 row in set (0.00 sec)

edited Apr 08 '10 at 21:03

answered Apr 02 '10 at 15:32

Daniel Vassallo

337,827
72
505
443

Hey Daniel! thanks. I am considering converting my column type to DECIMAL. – Sharief Shaik Apr 02 '10 at 16:39
3

@Sharief: If converting to `DECIAML` is impossible, the only option I see is to allow some tolerance for floating point comparisons, such that you could write your query as follows: `SELECT COUNT(*) FROM users WHERE points > (12.75 + 0.001);`... However if accuracy is paramount, fixed point `DECIMAL` is the way to go. Another alternative to `DECIMAL` could be using an integer value scaled up to represent your values in terms of hundredths: `5012` instead of `50.12`. There may be some situations where this might be appropriate. – Daniel Vassallo Apr 02 '10 at 16:48
I did try adding the tolerance already, exactly the way you mentioned, even then the results were never consistent. – Sharief Shaik Apr 02 '10 at 19:51
`DECIMAL` is not the right choice in every context... Read this: http://stackoverflow.com/a/5150314/655224. In my case I compared two float values with `...WHERE CAST(float1 AS CHAR) <= CAST(float2 AS CHAR)?` – algorhythm May 29 '15 at 11:43

score 5 · Answer 2 · answered Apr 07 '17 at 07:02

I do this

WHERE abs(value - 12.75)<0.001

but I agree, any language can compare float equality and if stored values equals exact numbers values you you inserted, there should not be any issue

with only a couple of decimals and exact matching values, precision errors does not sounds like an obvious reason for such mismatches in MySQL

score 2 · Answer 3 · answered Apr 05 '10 at 07:16

2

I did face the similar issue once. Convert the 'float' field to 'decimal'. It'll definitely solve the problem.

answered Apr 05 '10 at 07:16

intellidiot

11,108
4
34
41

I thought by using precise fixed length of float already solved the problem, so it doesn't?? for example; i define float(4,2). And then I stored value of 12.50, AND THEN I try to compare it with "> 12.50" statement. Wouldn't it still fail? – gumuruh Feb 07 '12 at 04:08

score 1 · Answer 4 · answered Apr 02 '10 at 15:27

1

There is a problems with comparison of floats for equality. This may give unpredicted results. This is due to internal implementation of floating point arithmetics.

answered Apr 02 '10 at 15:27

Andrey

59,039
12
119
163

score 0 · Answer 5 · answered Apr 02 '10 at 15:26

0

It's a floating point, so what's the problem? 3 could be the correct result, depends on what the database thinks about 12.75. Is it 12.75 or just a little more?

Use DECIMAL if you want exact numbers.

answered Apr 02 '10 at 15:26

Frank Heikens

117,544
24
142
135

Hi Frank, can you elaborate on what you mean by "what the database thinks about 12.75". Shall I be in trouble if I tried to compare a two digit precision value with a three digit precision. Like... SELECT COUNT(*) FROM `users` WHERE `points` > "12.751" – Sharief Shaik Apr 02 '10 at 16:35
@ShariefShaik I think Decimal should solved that case, from what other user experiences. – gumuruh Feb 07 '12 at 04:10

score 0 · Answer 6 · answered Apr 02 '10 at 15:28

0

Comparing a number with a string?

answered Apr 02 '10 at 15:28

dkretz

37,399
13
80
138

score 0 · Answer 7 · answered Jun 26 '19 at 12:01

0

Use REAL instead of FLOAT or DECIMAL.

answered Jun 26 '19 at 12:01

Anil Dhandar

11
1

score 0 · Answer 8 · answered Nov 06 '20 at 17:32

0

Decimal comparison won't work in FLOAT data type value. you just need to change the column data type to DECIMAL.

ALTER TABLE a MODIFY num DECIMAL(6,2);

answered Nov 06 '20 at 17:32

Mohit Rathod

1,057
1
19
33

MySQL floating point comparison issues

8 Answers8

Linked