19
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!

matteo
  • 2,934
  • 7
  • 44
  • 59
  • 5
    Don't confuse what is output with the actual value that is stored. You can use `format()` or `cast()` to decimal (for instance) to see the full value. – Gordon Linoff Apr 16 '14 at 21:44
  • @PhilPerry I see 32bit provides less precision than I thought, but still enough to distinguish 1919.987 from 1919.99 pretty well (i.e. they have different binary representation), that's not the issue in this case. – matteo Apr 17 '14 at 09:31
  • I wonder why a comment of mine was deleted. – matteo Apr 17 '14 at 16:52

2 Answers2

18

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.

Kaii
  • 20,122
  • 3
  • 38
  • 60
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Still, interestingly MySQL stores `1919.987` as `1919.98999..` in a single-precision floating point column (`FLOAT`), as you can see in [this SQL fiddle](http://www.sqlfiddle.com/#!2/33e37/1). Now i'm asking the same question as the OP: *Why??* – Kaii Apr 17 '14 at 12:13
  • 1
    @Kaii . . . MySQL documentation doesn't cover this well. But check out Wikipedia (http://en.wikipedia.org/wiki/Single-precision_floating-point_format). The basic single precision float has 6-9 digits of precision. The guarantee of decimal-->float-->decimal is 6 digits, and this example has 7. – Gordon Linoff Apr 17 '14 at 12:22
  • I tested on my server with Gordon's solution and I get 1919.987060547 after formatting, as expected, I don't know why the fiddle gives a different result – matteo Apr 17 '14 at 16:51
14

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.

Kaii
  • 20,122
  • 3
  • 38
  • 60
  • 5
    I think my question was pretty clear in that I know what a floating point number is and that it has a precision and an error. My question was about why the precision is lower than expected. http://www.h-schmidt.net/FloatConverter/IEEE754.html if I convert 1919.987 to ieee 754 and back I don't get any error – matteo Apr 17 '14 at 09:17
  • If you understand IEEE 754, then you understand that not all values are POSSIBLE to represent exactly, even if they're within the "precision" of an IEEE 754 float. Even 0.1 cannot be accurately represented with a float, and that's only one digit of precision. 1919.987 is one of those values. – dodexahedron Apr 17 '14 at 14:46
  • 2
    Yeah, the problem is that the precision is enough to distinguish 1919.987 from 1919.99 so that does not explain why the two values would end up being seen as the same. What does explain it is Gordon's answer – matteo Apr 17 '14 at 16:47
  • 1
    Nevertheless, this answer might provide helpful information for other visitors. I will not remove it. – Kaii Apr 17 '14 at 17:35