Discrepancy in MySQL Floating Point Precision: Data Rounded When Retrieved With SELECT Query

Question

I've encountered a perplexing issue while working with floating point values in MySQL. Specifically, I'm attempting to store and retrieve a floating point value with seven significant digits. However, when I fetch the data using a plain SELECT statement, the returned value appears to be rounded off. Conversely, when I utilize the ROUND or FORMAT functions, I receive the exact, unrounded value.

I've read some related discussion but most seem to be about suggesting the use of decimal type, but don't mention the reason for the discrepancy.

Here's a demonstration of my problem using a basic SQL command sequence:

1. Table Creation:

mysql> CREATE TABLE IF NOT EXISTS `demo`
  (
     `id`    INT(10) NOT NULL auto_increment,
     `value` FLOAT,
     PRIMARY KEY (`id`)
  );  

2. Inserting Value:

mysql> INSERT INTO demo (value) VALUES (2998877.0);

3. Retrieving Value (Returns Rounded Off Value):

mysql> SELECT value FROM demo;
+---------+
| value   |
+---------+
| 2998880 |
+---------+
1 row in set (0.00 sec)

4. Retrieving Value with ROUND Function (Returns Exact Value):

mysql> SELECT round(value) FROM demo;
+--------------+
| round(value) |
+--------------+
|      2998877 |
+--------------+
1 row in set (0.00 sec)

Given that the ROUND function returns the accurate value, it seems to indicate that the value is stored precisely. Therefore, my main question is: why does the SELECT statement, without any accompanying functions, yield a rounded value instead of the precise one? Any insights into this issue would be greatly appreciated.

Answer 1

MySQL is essentially hardcoded to round FLOAT values that are longer than 6 digits. See the comment from Alexey Kopytov on this bug report: https://bugs.mysql.com/bug.php?id=87794

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.

Answer 2

It is a problem about the round rule.

Why does this happen? This is because float type can only use 23 bits of binary to represent the decimal part when storing data. If the decimal part exceeds 23 bits, it will be truncated or rounded. And different decimal numbers, when converted to binary, may produce different lengths of decimal parts.

For example, the binary decimal part of 2998877/10 has infinitely many digits, while the binary decimal part of 2998877/100 has only 28 digits.

So, when converting to float type, the former only needs to round the last digit, while the latter needs to round the last 6 digits. This leads to different rounding errors.

PS: as you mentioned, the value stored is accurate, you can use SELECT CAST(value AS SIGNED) from demo to check。

to @Augustine ， it is just a "show problem", when float to string, it should consider the show format. in this case, alter table demo change value value float(100,2); then the select value can be as 2998877.00