CODEWITHSUNDEEP
mysqlcomparisonfloating-pointselect-query
Sharief Shaik
Sharief Shaik

Reputation: 1084

MySQL floating point comparison issues

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?

Upvotes: 27

Views: 39311

Answers (8)

Mohit Rathod
Mohit Rathod

Reputation: 1193

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);

Upvotes: 1

Anil Dhandar
Anil Dhandar

Reputation: 21

Use REAL instead of FLOAT or DECIMAL.

Upvotes: 1

ninja
ninja

Reputation: 473

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

Upvotes: 6

Daniel Vassallo
Daniel Vassallo

Reputation: 344291

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)

Upvotes: 33

intellidiot
intellidiot

Reputation: 11228

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

Upvotes: 2

dkretz
dkretz

Reputation: 37645

Comparing a number with a string?

Upvotes: 0

Andrey
Andrey

Reputation: 60065

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.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 126991

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.

Upvotes: 1

Related Questions