Bug #104632 | Floating point overflow | ||
---|---|---|---|
Submitted: | 16 Aug 2021 6:04 | Modified: | 16 Aug 2021 11:54 |
Reporter: | linfeng chen | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 8.0.21 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | FLOAT |
[16 Aug 2021 6:04]
linfeng chen
[16 Aug 2021 11:54]
MySQL Verification Team
Hi Mr. chen, Thank you for your bug report. However, this is not a bug. Simply, the largest IEEE floating point standard supports up to 20 (twenty) significant digits. Your example has more then 50 (fifty) significant digits. Hence, it is not supported by any standards. Not a bug.
[16 Aug 2021 19:44]
Roy Lyseng
It is correct that this is not a bug. However, the problematic value is a decimal value and not a floating point value. The value 1111111111111111111111111111111111111111111111111111111111111111111111.1 is a decimal number, but its precision (71) is greater than the maximum precision supported by MySQL (65). Therefore it is truncated in the output. The truncation is removed in later versions of MySQL 8.0. If the query result is used to create a table, as in the statement: create table z select avg(id * 111111111111111111111111111111111111111111 1111111111111111111111111111.1) as x from t3; you will get an error message "ERROR 1264 (22003): Out of range value for column 'z' at row 1" because MySQL will attempt to create a table with a decimal number column with greater precision than is supported. Workaround 1: Use smaller numbers and ensure that max. precision is less or equal to 65. Workaround 2: Use floating point numbers that will accommodate numbers up to 10^308. You will however loose some precision, since double precision numbers are limited to 16-17 digits. Example: select avg(id * 1111111111111111111111111111111111111111111111111111111111111111111111.1e0) as z from t3; +-----------------------+ | z | +-----------------------+ | 1.1111111111111112e69 | +-----------------------+