MySQL floating point comparison issues
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)
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
I did face the similar issue once. Convert the 'float' field to 'decimal'. It'll definitely solve the problem.