Why does MySQL round floats way more than expected?

Question

UPDATE some_table SET some_float_field=1919.987 WHERE id=123

SELECT * FROM some_table WHERE id=123

where some_float_field is a field defined as "float" (without any specific size values).

The expected resulting value would be 1919.987; instead, it is rounded to 1919.99

Why? A 32bit (single precision) float has enough precision for storing that correctly!

Answer 1

When you run the query:

SELECT * FROM some_table WHERE id = 123

You are relying on the user interface to format the floating point numbers. The interface you are using is using two characters rather than more. After all, there is no information on the "right" number to show.

You can convince the interface to show the right number by formatting the number as a string or as a decimal. For instance:

select format(some_float_field, 3)

will convert this to a string with three decimal places. One caution: it will also add commas which you might not want. This should also work:

select cast(some_float_field as decimal(8, 3))

Note that you can readily validate that the data is correct by doing something like:

select *
from some_table
where some_float_field between 1919.987 - 0.0001 and 1919.987 + 0.0001;

Note that you don't want to use = on floating point values, but you already understand that.

Answer 2

from the manual of FLOAT:

The FLOAT and DOUBLE types represent approximate numeric data values. MySQL uses four bytes for single-precision values ..

The emphasis here lies on approximate. If you need to store exact values, you should really use the DECIMAL data type.

For FLOAT, MySQL uses the IEEE standard 754 for binary floating point arithmetic to "compress" any fraction of a number into 4 bytes (or 8, for DOUBLE).

Note that this is applied to any value, regardless if the value (decimal part and fraction) could be precisely represented in 4 bytes! For example, the representation of 0.01 in floating point is exactly 0.009999999776482582092285156250 - even though 0.01 would perfectly fit in 32-bits of memory using another storage format.

Wikipedia explains the concept of floating point pretty well.

Note that the algorithm is affected by the precision specified in the column definition.
See this SQL fiddle example.