Search code examples
mysqlprecisionaccounting

Why MYSQL SUM() on FLOAT columns generates fractions

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

Solution

  • 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.