MySQL Bugs: #15472: Decrementing UNSIGNED column produces wrong result

Bug #15472	Decrementing UNSIGNED column produces wrong result
Submitted:	5 Dec 2005 1:18	Modified:	8 May 2006 18:54
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Linux (Linux, Windows)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
This is a change in behavior going from 4.1 to 5.0 and up.
Up through 4.1, decrementing an integer UNSIGNED column
that has a value of 0 produces a result of 0.

In 5.0 and up, it produces a result equal to the maximum
value of the column data type.

How to repeat:
Test script:

drop table if exists t;
create table t (i int unsigned);
insert into t set i = 0;
select * from t;
update t set i = i - 1;
select * from t;

Basically, it attempts to decrement an UNSIGNED value that is already 0.

Result in 4.0.27, 4.1.16:

+------+
| i    |
+------+
|    0 |
+------+
+------+
| i    |
+------+
|    0 |
+------+

Result in 5.0.17, 5.1.4:

+------+
| i    |
+------+
|    0 |
+------+
+------------+
| i          |
+------------+
| 4294967295 |
+------------+

Now, granted that in 5.0 and up, you set strict mode (in which case an
error occurs for the update), this still is contrary to the documented
behavior that if you attempt to set a column to a value outside the
column range, the value gets clipped to the endpoint of the range.
Thus, decrementing 0 produces -1, which should be clipped to 0.

Thank you for a bug report. Verified just as described on 5.0.17-BK (ChangeSet@1.1973, 2005-12-03 20:52:34+01:00):

mysql> drop table if exists t;
Query OK, 0 rows affected (0,01 sec)

mysql> create table t (i int unsigned);
Query OK, 0 rows affected (0,00 sec)

mysql> insert into t set i = 0;
Query OK, 1 row affected (0,00 sec)

mysql> select * from t;
+------+
| i    |
+------+
|    0 |
+------+
1 row in set (0,00 sec)

mysql> update t set i = i - 1;
Query OK, 1 row affected, 1 warning (0,00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'i' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0,00 sec)

mysql> select * from t;
+------------+
| i          |
+------------+
| 4294967295 |
+------------+
1 row in set (0,00 sec)

In strict mode (default on Windows) the same 1264 is produced, but as an error.

This behaviour is because of an addition of a flag in the function Field_long::store().
This flag is controled by the NO_UNSIGNED_SUBTRACTION sql_mode flag.
When off (by default) the statement behaves as described in that bug for 5.0 (sets the field to ~0).
When you turn it on the 4.1 behaviour is back.

Do we want that flag on by default or do we want this particular place to disregard the flag ?

This has been determined to be correct behaviour; the MySQL
Reference Manual states that the result of an arithmetic
operation where one operand is unsigned is also unsigned.
In earlier releases, MySQL was not able to consistently 
track whether a value was unsigned, everywhere in the server. 
This was corrected, causing the discrepancy shown.

The behavior demonstrated in the report should be described more
clearly in the manual. I'm changing this to a documentation bug and
assigning it to myself.

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
product(s).

Additional info:

The change in unsigned integer subtraction now is described in
the "SQL changes" section of this page:

http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html

See also Bug#18712.

Just for anyone that might be hitting similar problem, in our case we werent' able to turn on the NO_UNSIGNED_SUBTRACTION flag easily . Instead we fond that casting the unsigned field in the update to a signed field did the trick.

As an example:

DROP TABLE IF EXISTS `new_table`;
CREATE TABLE `new_table` (
  `prova` int(10) unsigned DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
insert into new_table (prova) VALUES(0);
update new_table set prova=IF((CAST(prova AS SIGNED)-1)>0,(CAST(prova AS SIGNED)-1),0); /* Works*/
select * from new_table;
update new_table set prova=IF((prova-1)>0,(prova-1),0); /* doesnt' work*/
select * from new_table;