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
