Bug #28079 Misleading error message(s) when in strict SQL mode
Submitted: 24 Apr 2007 18:32 Modified: 24 Apr 2007 21:44
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.27 OS:Any
Assigned to: CPU Architecture:Any

[24 Apr 2007 18:32] Philip Stoev
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.
[24 Apr 2007 21:44] Giuseppe Maxia
An existing bug report (Bug#18248) already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.