Bug #3553 select col_1 - col_2 gives wrong value for unsigned ints and negative result
Submitted: 23 Apr 2004 15:06 Modified: 25 Apr 2004 22:54
Reporter: Are you mortal Then prepare to die. Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:Ver 11.18 OS:Compaq Tru64 UNIX V5.1
Assigned to: Sergei Golubchik CPU Architecture:Any

[23 Apr 2004 15:06] Are you mortal Then prepare to die. 
Description:
crazy results for simple query...

How to repeat:
create temporary table x (
  a int(10) unsigned,
  b int(10) unsigned
);

insert into x values (10,10);
insert into x values (10,1 );
insert into x values ( 1,10);

select *, a - b  from x;
+------+--------+----------------------+
| a    | uni_to | a - b                |
+------+--------+----------------------+
|   10 |     10 |                    0 |
|   10 |      1 |                    9 |
|    1 |     10 | 18446744073709551607 |
+------+--------+----------------------+
3 rows in set (0.00 sec)

Suggested fix:
Somthing to do with geting a negative value result from a calculation involving unsigned integer columns?

You can't go to the moon on fractions... man!

What are you going to land on? 1/8 th 1/5th man!
[25 Apr 2004 22:54] Sergei Golubchik 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

read 

http://dev.mysql.com/doc/mysql/en/Server_SQL_mode.html

about

NO_UNSIGNED_SUBTRACTION
[26 Apr 2004 1:44] Are you mortal Then prepare to die. 
Who wants this (unsigned) behaviour?

I see the problem when subtracting two unsigned columns when the result needs to be unsigned to fit the size of the integer type, but this is an artificial restriction which should not be necessary for the user to take care of.

I think the behaviour is strange, and could cause problems for a lot of people. 

If mysql worked out what its result should be and did an automatic cast that would be more appropriate.

For example if I multiply two tinyint columns, I don't want a tinyint result if the result is bigger than a tinyint!

But thats just like ... my opinion ... man.