| 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 |
[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: http://lists.mysql.com/internals/32106
[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 : http://bugs.mysql.com/bug.php?id=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. http://bugs.mysql.com/bug.php?id=16285
Description: 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 | +------------------------+-----------+