MySQL Bugs: #46161: in strict mode, updates on unsigned integers still rollover

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

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

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".

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?

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.

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".