MySQL Bugs: #38512: Unsigned Integer Underflow

Bug #38512	Unsigned Integer Underflow
Submitted:	1 Aug 2008 4:23	Modified:	3 Oct 2008 18:16
Reporter:	opcode nop	Email Updates:
Status:	Closed
Category:	Server: Docs	Severity:	S2 (Serious)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Paul DuBois	Target Version:
Triage:	Needs Triage: D4 (Minor)

Description:
When arithmetic operation performed on UNSIGNED INTEGER that results in negative values,
an integer underflow occurs.

How to repeat:
This test is performed on MySql 5.0.60-r1 (gentoo portage)

mysql> system uname -a
Linux meepo 2.6.24-hardened-r3 #11 Mon Jul 28 07:31:20 MYT 2008 i686 AMD Sempron(tm)
2200+ AuthenticAMD GNU/Linux
mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.0.60-log |
+------------+
1 row in set (0.00 sec)

mysql> system uname -a
Linux meepo 2.6.24-hardened-r3 #11 Mon Jul 28 07:31:20 MYT 2008 i686 AMD Sempron(tm)
2200+ AuthenticAMD GNU/Linux
mysql> SELECT CAST( -1 AS UNSIGNED );
+------------------------+
| CAST( -1 AS UNSIGNED ) |
+------------------------+
|   18446744073709551615 |
+------------------------+
1 row in set (0.00 sec)

mysql> SELECT CAST( 0 AS UNSIGNED ) - 1;
+---------------------------+
| CAST( 0 AS UNSIGNED ) - 1 |
+---------------------------+
|      18446744073709551615 |
+---------------------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
It should be "rounded" back to 0.

Many thanks for writing a bug report.

Verified as described with 5.0 and 5.1 bzr tree.

Thank you for your bug report. This issue has been addressed in the documentation. The
updated documentation will appear on our website shortly, and will be included in the
next release of the relevant products.

I've added the following text to
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html:

Subtraction between integer values, where one is of type UNSIGNED,
produces an unsigned result by default. If the result would otherwise
have been negative, it becomes the maximum integer value. If the
NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the result is negative. 

mysql> SET SQL_MODE = ''; 
mysql> SELECT CAST(0 AS UNSIGNED) - 1; 
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|    18446744073709551615 |
+-------------------------+

mysql> SET SQL_MODE = 'NO_UNSIGNED_SUBTRACTION'; 
mysql> SELECT CAST(0 AS UNSIGNED) - 1; 
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
|                      -1 |
+-------------------------+

If the result of such an operation is used to update an UNSIGNED
integer column, the result is clipped to the maximum value for the
column type, or clipped to 0 if NO_UNSIGNED_SUBTRACTION is enabled.
If strict SQL mode is enabled, an error occurs and the column remains
unchanged.