Bug #91026 Inconsistent behavior insert overflow decimal error-code and warn-code
Submitted: 25 May 2018 12:04 Modified: 18 Jun 2018 12:06
Reporter: Li Su Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[25 May 2018 12:04] Li Su
Description:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t (a decimal(10,2));
Query OK, 0 rows affected (0.02 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values ("1e10000");
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t values ("1111111111");
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'a' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.01 sec)

mysql> set sql_mode = 'STRICT_TRANS_TABLES';                                                                                                                  Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> insert into t values ("1e10000");
ERROR 1366 (HY000): Incorrect decimal value: '1e10000' for column 'a' at row 1
mysql> insert into t values ("1111111111");
ERROR 1264 (22003): Out of range value for column 'a' at row 1

In above scenario, when sql_mode='', both return 1264(Error: 1264 SQLSTATE: 22003 (ER_WARN_DATA_OUT_OF_RANGE)).

But why in sql_mode = 'STRICT_TRANS_TABLES', `insert into t values ("1111111111")` not return `Error: 1366 SQLSTATE: HY000 (ER_TRUNCATED_WRONG_VALUE_FOR_FIELD)` and make `Error: 1264 SQLSTATE: 22003 (ER_WARN_DATA_OUT_OF_RANGE)`.

When `sql_mode = 'STRICT_TRANS_TABLES'` and `insert into t values ("1111111111")`, it return a ERROR not a warn, it should not return any ER_WARN_* code at I think.

How to repeat:
drop table if exists t;
create table t (a decimal(10,2));
set sql_mode = 'STRICT_TRANS_TABLES';
insert into t values ("1111111111");
[18 Jun 2018 12:06] MySQL Verification Team
Hi,

Thank you for your report. However, it is not a bug.

Decimal column, as you have defined it, can only have 8 (eight) digits left of the digital point and 2 (two) to the right of it. Both constants that you tried to include were larger then that.

Regarding that particular SQL_MODE changing errors into warnings, and the other way around, it is expected behaviour and described in our Reference Manual.