MySQL Bugs: #46799: Subtraction from unsigned integers wraps around

Bug #46799	Subtraction from unsigned integers wraps around
Submitted:	19 Aug 2009 0:53	Modified:	19 Aug 2009 5:54
Reporter:	Peter Jeremy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.1.32-ndb-7.0.5-cluster-gpl-log	OS:	Solaris (5.10 Generic_139555-08)
Assigned to:		CPU Architecture:	Any

Description:
When subtracting a value from an unsigned column, the result may wrap around zero to a large value.  It appears that the subtraction is done using 64-bit 2's complement and only the result is range-checked.

How to repeat:
bash-3.00# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.32-ndb-7.0.5-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

10:39 test> create table x (a tinyint unsigned, b smallint unsigned, c mediumint unsigned, d int unsigned, e bigint unsigned);
Query OK, 0 rows affected (0.00 sec)

10:41 test> insert into x values (1,1,1,1,1);
Query OK, 1 row affected (0.00 sec)

10:41 test> select * from x;
+------+------+------+------+------+
| a    | b    | c    | d    | e    |
+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+
1 row in set (0.00 sec)

10:41 test> update x set a=a-5, b=b-5, c=c-5, d=d-5, e=e-5;
Query OK, 1 row affected, 4 warnings (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 4

Warning (Code 1264): Out of range value for column 'a' at row 1
Warning (Code 1264): Out of range value for column 'b' at row 1
Warning (Code 1264): Out of range value for column 'c' at row 1
Warning (Code 1264): Out of range value for column 'd' at row 1
10:42 test> select * from x;
+------+-------+----------+------------+----------------------+
| a    | b     | c        | d          | e                    |
+------+-------+----------+------------+----------------------+
|  255 | 65535 | 16777215 | 4294967295 | 18446744073709551612 |
+------+-------+----------+------------+----------------------+
1 row in set (0.00 sec)

10:42 test> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

10:48 test>

Suggested fix:
For unsigned subtraction, A - B, verify B <= A before performing the subtraction, rather than verifying that the result is in range.

Hi Peter and thanks for your report.

Verified just as described on OpenSolaris x64 host with MySQL server 5.1.31 using both MyISAM and InnoDB engines.

Now, I do not see this as a bug at all. I think what happens is that MySQL server get's NAN from underlying HW (cause of integer overflow) and converts it to something appropriate throwing warning or error, depending on SQL_MODE, in the process.

So, please use "STRICT_TRANS_TABLES" and your table will not get updated because of error, which is what I would have expected and how other RDBM's behave:

mysql> set @@SQL_MODE="STRICT_TRANS_TABLES";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+---------------------+
| @@SQL_MODE          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)
...
mysql> update bug46799 set a=a-5, b=b-5, c=c-5, d=d-5, e=e-5;
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1264 | Out of range value for column 'a' at row 1 |
| Error | 1264 | Out of range value for column 'b' at row 1 |
| Error | 1264 | Out of range value for column 'c' at row 1 |
| Error | 1264 | Out of range value for column 'd' at row 1 |
+-------+------+--------------------------------------------+
4 rows in set (0.00 sec)

mysql> select * from bug46799;
+------+------+------+------+------+
| a    | b    | c    | d    | e    |
+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+
1 row in set (0.00 sec)

What exactly happens here is described at the bottom of:

http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

<paste>
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. 

...

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. 
</paste>