Bug #46161 in strict mode, updates on unsigned integers still rollover
Submitted: 14 Jul 2009 8:06 Modified: 14 Aug 2009 9:14
Reporter: Jan Klopper Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.51a-24+lenny1 OS:Linux (debian lenny)
Assigned to: CPU Architecture:Any
Tags: integer update rollover strict

[14 Jul 2009 8:06] Jan Klopper
Description:
In strict mode an unsigned field cannot be set to a negative value, this raises an error and sets the value to 0.

However, if the same field is updated through the following sql :

update table set column = column - value;

Then the value is rolled and no error/warning is raised.

How to repeat:
Create a table with an unsigned int field, set the value to 100.

update table set column = column - 200;

Notice the column value to rollover instead of a warning with a value of 0 in the table.

Suggested fix:
Raise the same warning and set the value to 0
[14 Jul 2009 8:41] Sveta Smirnova
Thank you for the report.

With current development version I get correct error when try to insert: " 1264: Out of range value adjusted for column 'f1' at row 1"

Additionally suggested fix can not be done: you have to use not strict SQL mode to get such behavior.

So closed as "Can't repeat".
[14 Jul 2009 8:48] Jan Klopper
Yes, I also get this warning: " 1264: Out of range value adjusted for column 'f1' at row 1"

When i try to insert a value or update the column to a negative value.

However if i update it by using the current value minus a too large integer, it still rolls over, without the warning. Isn't this was strict mode is supposed to do?
[14 Jul 2009 9:14] Sveta Smirnova
Thank you for the feedback.

Sorry, missed "no warning" part. But I still can not repeat such behavior:

mysql> drop table t1;
Query OK, 0 rows affected (0.08 sec)

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

mysql> create table t1(f1 int unsigned);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 values(-100);
ERROR 1264 (22003): Out of range value adjusted for column 'f1' at row 1
mysql> insert into t1 values(100);
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set f1=f1-200;
ERROR 1264 (22003): Out of range value adjusted for column 'f1' at row 1

mysql> select * from t1;
+------+
| f1   |
+------+
|  100 | 
+------+
1 row in set (0.00 sec)

Please upgrade to current version 5.0.83, try with it and if problem still exists provide repeatable test case like I did.
[14 Aug 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".