MySQL floating point rounding error

Question

Internally, what is going on with the floating point representation on the right to get false?

mysql> SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0;
+-----------------------+-----------------------------+
| 3.1415 + 0.9585 = 4.1 | 3.1415E0 + 0.9585E0 = 4.1E0 |
+-----------------------+-----------------------------+
|                     1 |                           0 |
+-----------------------+-----------------------------+

edit Conclusion: Don't use Float or Double for precision math. Use Decimal.

Answer 1

floating points like FLOAT or DOUBLE are always inprecise because they're an approximity, not an exact value, you should not use them for calculations. Use the DECIMAL data type instead.

Answer 2

First of all, you should never use = with floating points, as you simply cannot predict if no rounding errors occur. Always use < or >, dependent on what you want to achieve.

Second: Nothing is wrong with either formula. Internally, both variants get encoded in their variables to the nearest possible binary representation, which slightly differs from the value you set.

One digit before the decimal point is standard, so all following digits are used. On the right side, you specifically define where the comma occurs, thus "wasting" a certain part at the end of the binary representation of your variable - which leads to rounding errors (in this specific case).

Answer 3

MySQL, just as almost any computer implementation that uses floating point arithmetic, employs the IEEE-754 standard. This is binary floating point.

Just as it is impossible to write 1/3 in decimal, it's impossible to write infinitely many digits in binary floating point (given that computers are restricted in time and space). A float uses 32 bits, a double uses 64 bits, mathematically, that's very little space for infinite many fractions.

The Wikipedia article above is a good read about floating point. In general, when dealing with computer match, it is imperative to know a bit how computers use limited storage for unlimited fractions.

Exact decimals also exist, but are limited to a fixed range (i.e. no high exponent). In MySQL you can use DECIMAL for fixed point numbers.

Answer 4

In this, it was lucky that the first one compared exactly:

SELECT 3.1415 + 0.9585 = 4.1, 3.1415E0 + 0.9585E0 = 4.1E0;

Test floating points for near equality in this way:

mysql> SELECT abs(3.1415 + 0.9585 - 4.1) < 0.0001, abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001;
+-------------------------------------+-------------------------------------------+
| abs(3.1415 + 0.9585 - 4.1) < 0.0001 | abs(3.1415E0 + 0.9585E0 - 4.1E0) < 0.0001 |
+-------------------------------------+-------------------------------------------+
|                                   1 |                                         1 |
+-------------------------------------+-------------------------------------------+
1 row in set (0.00 sec)

Choose the difference (where I've used 0.0001) appropriately to the values involved.