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

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.

Iʼd like to return to the question with some notes which, I honestly consider, were missing at the previous discussion. This is, mainly, an objection to what was said by Alexey Kopytov.

The introduction note is about what we request from text representation of data types in general: obviously, this is 1) a convenient representation, but 2) an exact representation of a type value. The latter requires some demonstration. Assume a data type is `varchar`. We print it in single quotes
by default. If a single quote is present in the value, it is escaped with `\`. Additionally, `\` itself is escaped. There is no exception like "if you have \ in a string, import of the exported one will differ".

This logic shall be exactly transferred to other types, as `float` or `double`. The task of text representations is not to satisfy some external views on what in a data item can be represented; it is to represent all values of a data type, exactly (so allowing to import the same value back). `Float` is a binary floating-point (in SQL standardʼs, slightly derogative, terms: "approximate") type, so, we shall consider its precision in binary digits of mantissa with the base-2 exponent, not in decimal digits.

For this, mentioned precisions FLT_DIG and DBL_DIG are principally unsuitable: they define a class of a "decimal value" with a precision that guaranteedly shall be carried by `float` or `double`, respectively, but not how to fully represent a value of the FP type. "Approximate" (binary) types are not "exact" (decimal) ones and shall not be treated according to the lattersʼ rules. To specify what amount of decimal digits is needed to represent any binary floating point value, two other constants are defined in C headers: FLT_DECIMAL_DIG and DBL_DECIMAL_DIG, which respectively equal 9 and 17. And they are to be used in the export code by default.

Hence, this note:

> 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.

is principally misattributing, and, the issue IS a bug.

For `double`, I see that it is already printed with the full required precision. But `float` is discriminated. In the comment before `my_gcvt` body in strings/dtoa.cc, it is explicitly mentioned that this is a policy decision. And this policy is grounded on a wrong basis.

It is highly indicative how this issue is addressed in another code in MySQL repo: FloatToBuffer() in extra/protobuf/protobuf-24.4/src/google/protobuf/io/strtod.cc contains, really, a hack: it prints in FLT_DIG digits, then parse the value back into `float` and checks whether it equals; if not, it adds 3 more digits, effectively applying FLT_DECIMAL_DIG. The same for double: DBL_DIG first, then, if not fully represented, add 2 (which results in DBL_DECIMAL_DIG). I wonder here why FLT_DECIMAL_DIG and DBL_DECIMAL_DIG are not named explicitly in the code: the constants 3 and 2 look there mystical. The same mystery happens in extra/protobuf/protobuf-24.4/php/ext/google/protobuf/php-upb.c. Although the method is, sheerly, a hack, it works how assumed in the most of cases.

It is seen in code and mentioned in comments that the problem is, to some extent, entailed by use of `dtoa` library. Since its early forms, first, the `dtoa` itself has got huge development from different sources; second, many approaches of exact printing were invented, and libraries written, to produce a minimal required length of digits to represent a floating-point value that is exactly imported back. I am mentioning only some most renowned: https://github.com/ulfjack/ryu/; https://github.com/jk-jeon/dragonbox; more references are present, for example, in ECMA-262; as soon as, at least, JavaScript (with ECMA standard), Java, Python specifications name the shortest-exact-form style as the main, this means that it has become cheap and widespread enough to be utilized for MySQL as well.

So, the proposition: apply one of the following:

1) The minimal one: change FLT_DIG to FLT_DECIMAL_DIG in `my_gcvt()` in dtoa code. I guess a large part of cases will result in no change of text representation; for rest, having some more digits seem of a minor issue.

2) The wider one, but hackish: utilize the trick applied in protobuf code (one shorter export, checking, using the longer one in the need) into the main MySQL code. (Do we really need a separate dtoa instead of printf/scanf support in libc?)

3) The maximum one: import a modern shortest-exact-form printing library, like some listed above. Except the work needed to import it, I donʼt expect an obstacle. (This may include newer dtoa development as well.)

Also, adjust cases like `FLT_DIG+3` where an own constant is available. But this seems may requiring fixing uplink, so, this is not for MySQL on its own.

PS: C++: `std::numeric_limits<T>::max_digits10` fulfil the same task as {FLT,DBL}_DECIMAL_DIG. In C++ code it is better than a C header constant.