Bug #90272 input overflow of ZEROS returns maximum of possible value of cell
Submitted: 1 Apr 2018 10:34 Modified: 4 Apr 2018 15:59
Reporter: Aivars Lasmanis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: overflow

[1 Apr 2018 10:34] Aivars Lasmanis
Description:
input of 81 zeros in front of int returns maximum value of cell

How to repeat:
Lets create table:
create table zero_test (amount decimal(12,2));

try- 72 zeros:
insert into zero_test set amount = '000000000000000000000000000000000000000000000000000000000000000000000001.1';
Query OK, 1 row affected (0.01 sec)

check:
mysql> select * from zero_test;
+--------+
| amount |
+--------+
|   1.10 |
+--------+
1 row in set (0.00 sec)

Lets try 73 zeros:
insert into zero_test set amount = '0000000000000000000000000000000000000000000000000000000000000000000000001.1';
ERROR 1366 (HY000): Incorrect decimal value: '0000000000000000000000000000000000000000000000000000000000000000000000001.1' for column 'amount' at row 1

Not working due STRICT_TRANS_TABLES. Next:

mysql> show variables like "%sql_mode%";
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

Switch off STRICT_TRANS_TABLES (for current session):
set session sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Try 73 zeros:
insert into zero_test set amount = '0000000000000000000000000000000000000000000000000000000000000000000000001.1';
Query OK, 1 row affected, 1 warning (0.00 sec)

ALL good now:
select * from zero_test;
+--------+
| amount |
+--------+
|   1.00 |
+--------+

Lets put 82 zeros:
insert into zero_test set amount = '0000000000000000000000000000000000000000000000000000000000000000000000000000000001.1';
Query OK, 1 row affected, 1 warning (0.00 sec)

И вот он баг:
 select * from zero_test;
+---------------+
| amount        |
+---------------+
| 9999999999.99 |
+---------------+

this happens with inputs with quotes and without quotes too
[4 Apr 2018 15:59] MySQL Verification Team
Hi!

Thank you for your bug report.

This is a small bug, but still a bug.

For strings with more then 72 numbers, the overflow error should be returned regardless of the SQL_MODE.

Also, there should not be a totally wrong result, but, again, the overflow error message.

Verified.