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");