Why MYSQL SUM() on FLOAT columns generates fractions

Question

Could someone tell me the reason why MySQL SUM() function performed on FLOAT columns gives strange result ?

Example :

CREATE TABLE payments (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    amount FLOAT DEFAULT NULL,
    PRIMARY KEY(id)
);

INSERT INTO payments (amount) VALUES (1.3),(1.43),(1.65),(1.71);

When performing SUM(), expecting 6.09, MySQL returns that floating number :

mysql> SELECT SUM(amount) FROM payments WHERE 1;
+--------------------+
| SUM(amount)        |
+--------------------+
| 6.0899999141693115 |
+--------------------+
1 row in set (0.00 sec)

This is pretty scary for a guy that may develop let say... an accounting software ! :/

Version : Mysql 5.5.60

[Is floating point math broken?](https://stackoverflow.com/questions/588004/is-floating-point-math-broken) — Álvaro González, Jun 05 '18 at 17:16
https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html — bishop, Jun 05 '18 at 17:18
Also read https://dev.mysql.com/doc/refman/8.0/en/problems-with-float.html — Bill Karwin, Jun 05 '18 at 18:47

score 2 · Accepted Answer · answered Jun 05 '18 at 17:15

2

That's why you don't use floating point values in anything where rounding is important. Even the manual says:

The DECIMAL and NUMERIC types store exact numeric data values. These types are used when it is important to preserve exact precision, for example with monetary data.

answered Jun 05 '18 at 17:15

Veijo

264
2
9

Ok, I didn't check mysql documentation before using FLOAT column. It is very useful to know ! When is it relevant to use FLOAT columns then ? It maximizes storage or calculation speed on values that precision isn't important ? – Simmoniz Jun 05 '18 at 17:28
@Simmoniz it is not just a mysql thing, it is a general computing thing. yes, "float" math tends to be much much faster than "decimal" math, but decimal math stores precise values so can manage basic addition, subtraction, and multiplication with such side effects... though as far as I know still can have issues with calculations such as 1/3. – Uueerdo Jun 05 '18 at 18:14

Why MYSQL SUM() on FLOAT columns generates fractions

1 Answers1