Bug #9695 signed/unsigned conversion depend on parameter order. a-b not the same as -b+a
Submitted: 6 Apr 2005 20:48 Modified: 6 Apr 2005 23:51
Reporter: Pavel Riha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.21 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[6 Apr 2005 20:48] Pavel Riha
Description:
http://dev.mysql.com/doc/mysql/en/arithmetic-functions.html
<cite>
If ONE of the arguments is an unsigned integer, and the other argument is also an integer, the result is an unsigned integer.
</cite>

but when A is signed and B is unsigned:

mysql> select a,b, a-b, -b+a, b-a, -a+b from c;
+------+------+----------------------+-------+----------------------+----------------------+
| a    | b    | a-b                  | -b+a  | b-a                  | -a+b                 |
+------+------+----------------------+-------+----------------------+----------------------+
|    0 | 1000 | 18446744073709550616 | -1000 |                 1000 |                 1000 |
| 1000 |    0 |                 1000 |  1000 | 18446744073709550616 | 18446744073709550616 |
+------+------+----------------------+-------+----------------------+----------------------+

see the difference between a-b and -b+a in the first line,
but the second line is ok, like I would excepted from the manual.

tested on Gentoo Linux, compiled from sources
(pavel)~$ mysql --version
mysql  Ver 12.22 Distrib 4.0.21, for pc-linux-gnu (i686)
(pavel)~$ uname -a
Linux wotrok.trilogic.cz 2.6.10-4 #4 Thu Mar 10 23:56:20 CET 2005 i686 AMD Athlon(tm) XP 2500+ AuthenticAMD GNU/Linux

tested on SuSE Linux, binary distribution
(riha@kaligula2)~$ mysql --version
mysql  Ver 12.22 Distrib 4.0.21, for suse-linux (i686)
(riha@kaligula2)~$ uname -a
Linux kaligula2 2.6.8-24-smp #1 SMP Wed Oct 6 09:16:23 UTC 2004 i686 i686 i386 GNU/Linux

How to repeat:
create table c(a int, b int unsigned);
insert into c values (0,1000);
insert into c values (1000,0);
select a,b, a-b, -b+a, b-a, -a+b from c;

Suggested fix:
'a-b' and '-b+a' should report the same.
[6 Apr 2005 23:51] Hartmut Holzgraefe
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:

the unary minus operator changes the singdness of its argument
and has a higher operator precedence than the binary plus or minus

-b  -> result is signed even if b is unsigned

-b+a is evaluated as (-b)+a 

so for the binary plus operator both operands are already signed