MySQL Bugs: #87794: MySQL gives invalid value for float column type.

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.7.17	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FLOAT

Description:
MySQL gives a different value than what it has when I query it. It looks like it knows the right value but does not give it back. An example will make it easy to understand. (Tested using mysql client and java mysql connector)

Let say we have a very simple table with one float column:
CREATE TABLE simpletable (value FLOAT);

And we insert two values in it:
INSERT INTO simpletable VALUES (1223484);
INSERT INTO simpletable VALUES (1223482);

And get it back:
SELECT value FROM simpletable;
+---------+
| value   |
+---------+
| 1223480 |
| 1223480 |
+---------+

Already strange, looks like a loss of precision... Only for 7 digits... 
But then it gets weird:

SELECT * FROM simpletable WHERE value = 1223480;
nothing...

SELECT * FROM simpletable WHERE value = 1223482;
+---------+
| value   |
+---------+
| 1223480 |
+---------+

SELECT * FROM simpletable WHERE value = 1223484;
+---------+
| value   |
+---------+
| 1223480 |
+---------+

Even if they are displayed as the same they are not.

And then:
SELECT value+0 FROM simpletable;
+------------+
| (value +0) |
+------------+
|    1223484 |
|    1223482 |
+------------+

Wow! The original values are back! Let's try something else:

SELECT MIN(value), MAX(value) FROM simpletable;
+------------+------------+
| min(value) | max(value) |
+------------+------------+
|    1223482 |    1223484 |
+------------+------------+

Again, original values are really there. 

At first it looks like a display/format issue in the MySQL client. But I also get the issue using the JAVA connector using setFloat on the prepared statement and getFloat on the result set.

The issue is also easy to reproduce with many other numbers.

How to repeat:

CREATE TABLE simpletable (value FLOAT);
INSERT INTO simpletable VALUES (1223484);
INSERT INTO simpletable VALUES (1223482);

SELECT * FROM simpletable WHERE value = 1223480;
SELECT * FROM simpletable WHERE value = 1223482;
SELECT * FROM simpletable WHERE value = 1223484;

SELECT value, value+0 FROM simpletable;
SELECT MIN(value), MAX(value) FROM simpletable;

Suggested fix:
Consistent result.

Thank you for the bug report. Please read:

https://dev.mysql.com/doc/refman/5.7/en/problems-with-float.html

Thanks.

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.

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.

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.

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.

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.

Thank you for the feedback.