Bug #14543 Relative UPDATE query changing unsigned int to -1 overflows (value=4294967295)
Submitted: 1 Nov 2005 13:41 Modified: 16 Jan 2006 17:15
Reporter: Mike Sullivan Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.15/5.0.16 BK OS:Linux (Linux (Fedora Core 3))
Assigned to: Sergei Glukhov CPU Architecture:Any

[1 Nov 2005 13:41] Mike Sullivan
Executing an a relative update (ie, field = field - 1) that would update an unsigned integer field to an invalid value (less than 0) seems to cause an integer overflow, making the value 4294967295 instead of 0.

This only occurs with a relative update. Directly inserting a row with an invalid value or using a "fixed" update with an invalid value leaves the row at 0.

As far as I know, this only occurs in MySQL 5.

How to repeat:
The following queries show the problem. The first 2 insert/updates behave properly but the 3rd does not:

create table overflowtest (
	testid varchar(50) not null default '' primary key,
	testfield int unsigned not null default 0

insert into overflowtest (testid, testfield) values ('insert_invalid', -1);

insert into overflowtest (testid, testfield) values ('insert_update_fixed', 1);
update overflowtest set testfield = -1 where testid = 'insert_update_fixed';

insert into overflowtest (testid, testfield) values ('insert_update_relative', 0);
update overflowtest set testfield = testfield - 1 where testid = 'insert_update_relative';

This generates the following data:

mysql> select * from overflowtest;
| testid                 | testfield  |
| insert_invalid         |          0 |
| insert_update_fixed    |          0 |
| insert_update_relative | 4294967295 |

Suggested fix:
The output from the select query should look like this:

| testid                 | testfield |
| insert_invalid         |         0 |
| insert_update_fixed    |         0 |
| insert_update_relative |         0 |
[9 Nov 2005 11:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[16 Jan 2006 17:15] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

No bug here. If you, instead of the update, tried the select to see what the new value of the column should be, you'd get

mysql> select testfield-1 from overflowtest where testid = 'insert_update_relative';
| testfield-1          |
| 18446744073709551615 |
1 row in set (0.03 sec)

This is larger than the upper boundary of unsigned int, so on update the column gets the upper boundary value and a "truncated" warning in generated.

See the manual for NO_UNSIGNED_SUBTRACTION sql_mode for more details.
[14 Mar 2006 15:17] Stephane Deverly
Seems very similar to bug 15472 :
It's a bit confusing here : is it a bug which was fixed with the issued patch, or is it the expected behavior ?
Check bug 16285 for a workaround using NO_UNSIGNED_SUBTRACTION mode, and a related to bug.