Bug #68439 Unsigned INTs underflow in case of math
Submitted: 20 Feb 2013 2:35 Modified: 8 Mar 2018 23:39
Reporter: Domas Mituzas Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.1 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D2 (Serious)

[20 Feb 2013 2:35] Domas Mituzas
Description:
As a certified MySQL professional and developer I'm greatly offended that decrement on 5.1 unsigned integer value is causing underflow, yet inserts of negative values are set to zero:

mysql> select * from tint;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.00 sec)

mysql> update tint set a=a-1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> select * from tint;
+------------+
| a          |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

The value should be 0, not 4294967295. Do note, that opposite (overflow) does not happen:

mysql> update tint set a=a+1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> select * from tint;
+------------+
| a          |
+------------+
| 4294967295 |
+------------+
1 row in set (0.00 sec)

How to repeat:
CREATE TABLE `tint` (
  `a` int(10) unsigned DEFAULT NULL
);
INSERT INTO tint VALUES (0);
update tint set a=a-1;
 update tint set a=a+1;

Suggested fix:
don't underflow, underflows are sad and bad
[20 Feb 2013 2:42] Domas Mituzas
do note, 5.6 actually allows these statements and sets values to 0 when NO_UNSIGNED_SUBSTRACTION SQL mode is set, so the mode that does not error would yield completely different end result on 5.1 and 5.6 servers. 

See http://bugs.mysql.com/bug.php?id=64751
[20 Feb 2013 13:36] Miguel Solorzano
Thank you for the bug report. Verified as described.
[8 Mar 2018 23:39] Roy Lyseng
Posted by developer:
 
This problem does not occur on release 5.7.22 and up.