Bug #87794 | MySQL gives invalid value for float column type. | ||
---|---|---|---|

Submitted: | 18 Sep 2017 15:10 | Modified: | 19 Sep 2017 15:33 |

Reporter: | D L | Email Updates: | |

Status: | Not a Bug | Impact on me: | |

Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |

Version: | 5.7.17 | OS: | Any |

Assigned to: | CPU Architecture: | Any | |

Tags: | FLOAT |

[18 Sep 2017 15:10]
D L

[18 Sep 2017 15:40]
MySQL Verification Team

Thank you for the bug report. Please read: https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html Thanks.

[18 Sep 2017 16:38]
D L

From my understanding, https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html is about typical misunderstandings about floating point values like approximations and precision. This issue may look like this but it is not about that. Once the value is stored in MySQL, it is saved as 32 bits of data. When I query MySQL for that value, I expect to get these same 32 bits of data and they should be shown as the same. Example: CREATE TABLE testtable (value float); INSERT INTO testtable VALUES(1223483); SELECT value from testtable; gives 1223480 SELECT MIN(value) from testtable; gives 1223483 The min should not be higher than the value. More over, how MySQL was able to give the right min if the value would have been stored with less precision ? Based on my tests, it looks like MySQL gives a wrong value only when there is no operation/calculation applied on the queried value.

[18 Sep 2017 17:50]
MySQL Verification Team

From Manual: Floating-point numbers sometimes cause confusion because they are approximate and not stored as exact values. A floating-point value as written in an SQL statement may not be the same as the value represented internally. Attempts to treat floating-point values as exact in comparisons may lead to problems. They are also subject to platform or implementation dependencies.

[18 Sep 2017 21:32]
D L

I know float are very often misunderstood. This is not the case here. When I insert 1223484 in a float column, MySQL stores it correctly. The .MYD file contains the value 0xE0599549 (0x499559E0 big-endian), which is the exact float representation of 1223484.000000000000000000. The problem is when I ask MySQL for that value. It gives me 1223480 instead. 1223480 is represented by 0x499559C0. Clearly, it is a different value. And since I am able to get the right value by applying an operation on it, it is another proof MySQL stores it. If it was an approximation misunderstanding, the value stored in the .MYD file would be something else than the actual one and applied operations would return a similar or same value of the wrong one. I hope the provided details give a better view on the issue. If there is a flaw in my explanations, I would be very grateful to know what it is.

[19 Sep 2017 8:35]
Alexey Kopytov

MySQL uses the FLT_DIG constant (which equals to 6 with IEEE 754 encoding) to print float-type numbers. FLT_DIG is the number of decimal digits that can be converted to a float-type binary number and back without loss of precision for *any* input number. That doesn't mean there are no numbers with more significant digits that can be represented precisely in the binary format (and your case is an example of such a number), but the constant ensures that property for all inputs. An equivalent constant for double-type numbers is called DBL_DIG and equals 15 with IEEE 754 encoding. Conversion of double-type numbers from decimal representation to binary and back are handled by the dtoa library. The library allows precisely converting inputs longer than DBL_DIG digits for those numbers where such conversion is possible. Unfortunately, the library operates only with the double-type numbers, there's no float-type version. Going back to your examples, they are a combination of two things: - the input numbers have more than FLT_DIG precision, for which rounding to binary and converting back to decimal representation is possible, but is not guaranteed. - MySQL converts float-type numbers to double-type in all calculations. That's why (value + 0), MIN(value) and MAX(value) result in more precise decimal representations -- dtoa prints double-precision numbers in those cases, so it is safe to print at least 15 decimal digits. My recommendation would be to avoid the FLOAT data type entirely, or be ready to deal with anomalies like the one demonstrated here. The DOUBLE data type does not only provide higher precision, but is also more consistent in cases like this.

[19 Sep 2017 15:26]
D L

Thank you very much for this detailed explanation. I guess it also applies to the java connector too. I get the same results when using it.

[19 Sep 2017 15:33]
MySQL Verification Team

Thank you for the feedback.