MySQL Bugs: #104632: Floating point overflow

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	FLOAT

Description:
create table t3 (id int);
 insert into t3 values(1);
select avg(id * 1111111111111111111111111111111111111111111111111111111111111111111111.1) from t3;
result:
| avg(id * 1111111111111111111111111111111111111111111111111111111111111111111111.1) |
+------------------------------------------------------------------------------------+
|             9999999999999999999999999999999999999999999999999999999999999999.90000 |
+------------------------------------------------------------------------------------+

How to repeat:
create table t3 (id int);
 insert into t3 values(1);
select avg(id * 1111111111111111111111111111111111111111111111111111111111111111111111.1) from t3;
result:
| avg(id * 1111111111111111111111111111111111111111111111111111111111111111111111.1) |
+------------------------------------------------------------------------------------+
|             9999999999999999999999999999999999999999999999999999999999999999.90000 |
+------------------------------------------------------------------------------------+

Suggested fix:
Error message: the floating point number is too long

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.

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