Description:
When running in strict SQL mode, inserting an out-of range value produces the following error:
ERROR 1264 (22003): Out of range value adjusted for column 'f1' at row 1
This error message is misleading because no value adjustment occurs in strict SQL mode. Instead, the query is aborted.
How to repeat:
mysql> create table t1 (f1 tinyint);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values (300);
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'f1' at row 1 |
+---------+------+------------------------------------------------------+
1 row in set (0.02 sec)
mysql> set SQL_MODE='STRICT_ALL_TABLEs';
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values (300);
ERROR 1264 (22003): Out of range value adjusted for column 'f1' at row 1
Suggested fix:
It appears that the warnigs that are produced in non-strict SQL mode simply get converted into SQL errors in strict SQL mode. Unfortunately, warnings messages may specify what actions the database took to remedy the situation, and such actions will not be executed in strict SQL mode -- instead, the query will be aborted without any side-effects.
Therefore, it would be nice to either have a separate set of error messages for strict SQL codes instead or using the wording from the warnings. Alternatively, the message can be made universal between the two modes, for example:
Range adjustment (was) required for column f1 at row 1
which works as both a warning and as an error message without misleading the user.
