Bug #21070 subtraction of two unsigned field returns unsigned
Submitted: 14 Jul 2006 19:36 Modified: 15 Jul 2006 14:20
Reporter: Sedat Onur ORAKOGLU Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:Windows (win 2000)
Assigned to: CPU Architecture:Any

[14 Jul 2006 19:36] Sedat Onur ORAKOGLU
Description:
create table test
(
id int auto_increment,
f1 int unsigned,
f2 int unsigned,
primary key (id));

INSERT INTO TEST (F1,F2)
VALUES (1,6),(8,10),(20,5),(25,10);

SELECT F1,F2,F2-F1 FROM TEST;

returns the result as

f1,f2,f2-f1
1,6,5
8,10,2
20,5,18446744073709551601
....

is this a bug? 

How to repeat:
always
[15 Jul 2006 14:20] Valeriy Kravchuk
Thank you for a problem report. Sorry, but this is not a bug. Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html):

"Warning: When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.8, “Cast Functions and Operators”."

Use the following in such a cases:

mysql> select f1, f2, cast(f2 - f1 as signed) from test;
+------+------+-------------------------+
| f1   | f2   | cast(f2 - f1 as signed) |
+------+------+-------------------------+
|    1 |    6 |                       5 |
|    8 |   10 |                       2 |
|   20 |    5 |                     -15 |
|   25 |   10 |                     -15 |
+------+------+-------------------------+
4 rows in set (0.01 sec)

or don't use unsigned at all.