MySQL Bugs: #17439: ~0 works different inside functions than the bare expression

Bug #17439	~0 works different inside functions than the bare expression
Submitted:	16 Feb 2006 0:44	Modified:	16 Oct 2007 11:08
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19-BK, 5.1.7-BK	OS:	Linux (Linux, winxp pro xp)
Assigned to:		CPU Architecture:	Any

Description:
A bitwise negation of 0 is interpreted as either a singed or an unsigned number depending upon the context. When used as a bare expression, it returns the maximum unsigned value. When used inside a function, it returns -1

How to repeat:
mysql> select ~0, abs(~0), if(true,~0,0);
+----------------------+---------+---------------+
| ~0                   | abs(~0) | if(true,~0,0) |
+----------------------+---------+---------------+
| 18446744073709551615 |       1 |            -1 |
+----------------------+---------+---------------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
please use a single level of signed precision for all contexts

Thank you for a problem report. Verified just as described on current 5.0-BK and 5.1-BK.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12113

ChangeSet@1.2272, 2006-09-18 03:00:16-07:00, igreenhoe@anubis.greendragongames.com +4 -0
  Fix for bug #17439 (~0 works different inside functions than the bare
  expression)
  
  Problem: abs() and if() were not checking or using the value of
  unsigned_flag.
  
  Solution: abs() and if() changed appropriately.

Are you sure it's only abs() and if()? I mean, I just noticed once, tried another function and concluded it had to be wrong in all cases. I just can't believe it's coincidence I would pick out the only two functions that are wrong. 

I can actually prove that at least on 5.1.11 bk, it is not limited to just ABS and IF:

mysql> select ifnull(null,~0);
+-----------------+
| ifnull(null,~0) |
+-----------------+
|              -1 |
+-----------------+
1 row in set (0.00 sec)

So, at least IFNULL is affected too. COALESCE is not by the way:

mysql> select coalesce(null,~0);
+----------------------+
| coalesce(null,~0)    |
+----------------------+
| 18446744073709551615 |
+----------------------+
1 row in set (0.00 sec)

More proof that this issue is not limited to ABS, IF, NULLIF.

COS, SIN, ATAN, TAN

mysql> select cos(18446744073709551615);
+---------------------------+
| cos(18446744073709551615) |
+---------------------------+
|         -0.94981199465599 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select cos(~0);
+------------------+
| cos(~0)          |
+------------------+
| 0.54030230586814 |
+------------------+
1 row in set (0.00 sec)

mysql> select sin(~0);
+------------------+
| sin(~0)          |
+------------------+
| -0.8414709848079 |
+------------------+
1 row in set (0.02 sec)

mysql> select sin(18446744073709551615);
+---------------------------+
| sin(18446744073709551615) |
+---------------------------+
|          0.31282131450335 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select sin(-1);
+------------------+
| sin(-1)          |
+------------------+
| -0.8414709848079 |
+------------------+
1 row in set (0.00 sec)

mysql> select atan(-1);
+-------------------+
| atan(-1)          |
+-------------------+
| -0.78539816339745 |
+-------------------+
1 row in set (0.00 sec)

mysql> select atan(~0);
+-------------------+
| atan(~0)          |
+-------------------+
| -0.78539816339745 |
+-------------------+
1 row in set (0.00 sec)

mysql> select atan(18446744073709551615); +----------------------------+
| atan(18446744073709551615) |
+----------------------------+
|            1.5707963267949 |
+----------------------------+
1 row in set (0.00 sec)

mysql> select tan(18446744073709551615);
+---------------------------+
| tan(18446744073709551615) |
+---------------------------+
|         -0.32935077285126 |
+---------------------------+
1 row in set (0.00 sec)

mysql> select tan(-1);
+------------------+
| tan(-1)          |
+------------------+
| -1.5574077246549 |
+------------------+
1 row in set (0.00 sec)

mysql> select tan(~0);
+------------------+
| tan(~0)          |
+------------------+
| -1.5574077246549 |
+------------------+
1 row in set (0.00 sec)

I'm not really sure if this patch actually solves the problem generically. If it doen't, my suggestion would be to reset the status of this bug to "Analyzing", and investigate which other functions are affected.

Seems to be fixed at least in 5.1.22, prolly way before that.