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