Bug #68439 Unsigned INTs underflow in case of math

• Submitted: 20 Feb 2013 2:35
• Modified: 8 Mar 2018 23:39
• Reporter: Domas Mituzas
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 5.1
• OS: Any
• CPU Architecture: Any

[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] MySQL Verification Team

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.