MySQL Bugs: #108423: When the maximum integers of double and float are inserted, the data is distorte

Bug #108423	When the maximum integers of double and float are inserted, the data is distorte
Submitted:	8 Sep 2022 10:03	Modified:	12 Sep 2022 7:51
Reporter:	diandian wei	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	mysql Ver 8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When the maximum integers of double and float are inserted, the data is distorte

How to repeat:

drop database if exists SingleTable;
create database SingleTable;
use SingleTable;
create table ompsitem (id bigint primary key auto_increment,tinyint1 tinyint default null,smallint1 smallint default null,mediumint1 mediumint default null,ageint int default null,ageinterger1 integer default null,bigint1 bigint default null,float1 float default null,double1 double default null,decimal1 decimal default null,datel date default null,timel time default null,yearl year default null,datetimel datetime default null,timestampl timestamp default current_timestamp,char1 char default null,varchar1 varchar(255) default null,tinytext1 tinytext default null,text1 text default null,longtext1 longtext default null,tinyblob1 tinyblob default null,blob1 blob default null,longblob1 longblob default null,mediumblob1 mediumblob default null,mediumtext1 mediumtext default null);
insert into ompsitem(tinyint1,smallint1,mediumint1,ageint,ageinterger1,bigint1,float1,double1,decimal1,datel,timel,yearl,datetimel,char1,varchar1,tinytext1,text1,longtext1,tinyblob1,blob1,longblob1,mediumblob1,mediumtext1)values(127,32767,8388607,2147483647,2147483647,9223372036854775807,99999999999999999999,99999999999999999999,12,'9999-12-31','838:59:59','2155','9999-12-31 23:59:59','a','bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','cccccccccccccccccccccccccccccccccccc','ddddddddd','eeeeeee','fffffffff','ggggggggg','hhhhhhhh','iiiiiiiiiiiiiiiiiiiiiiiii','ggggggggggggggggggggggggggg');
select float1,double1 from ompsitem;

Suggested fix:
The inserted data is stored correctly or alarm information is generated.

The execution result is incorrect.
It's supposed to be 99999999999999999999.

mysql> select float1,double1 from ompsitem;
+--------+---------+
| float1 | double1 |
+--------+---------+
|   1e20 |    1e20 |
+--------+---------+
1 row in set (0.00 sec)

Hello diandian wei,

Thank you for the report and feedback.
Discussed internally with concern developer and concluded that it is indeed not a bug. there are 20 digits of precision, which exceeds the precision for both data types. Thus, 1e20 is the best possible approximation for 99999999999999999999.

Official manual confirms this on how strict and non-strict mode handles Out-of-Range and Overflow Handling values - https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html

regards,
Umesh