Bug #20924 CAST(expr as UNSIGNED) returns SIGNED value when used in various functions
Submitted: 9 Jul 2006 6:42 Modified: 21 May 2009 22:55
Reporter: Matthew Montgomery Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:All OS:Linux (Linux 2.6)
Assigned to: CPU Architecture:Any
Tags: cast functions, control flow functions
Triage: Triaged: D2 (Serious)

[9 Jul 2006 6:42] Matthew Montgomery
Description:
It appears that whenver you assign an UNSIGNED CAST integer value to a variable the wrong value is passed.

This was first noticed using an IF() clause.  It will always return a SIGNED integer if expr2 and expr3 are integers. This is true even when you directly CAST one or both of the expresions as UNSIGNED.

A partial solution is shown below.  However, This does not work within the context of a TRIGGER.  The result returned will still be a SIGNED value if the example statement is called.

How to repeat:
Typical Behavior: 

mysql> SELECT CAST(-1 as UNSIGNED);

+------------------------+
| CAST(1*-1 as UNSIGNED) |
+------------------------+
|   18446744073709551615 |
+------------------------+

Assign CAST value to variable:

mysql> SET @id = CAST(-1 as UNSIGNED);

mysql> select @id;
+------+
| @id  |
+------+
| -1   |
+------+

CAST expr2 as UNSIGNED within IF():

mysql> SELECT IF(1 != 0, CAST(-1 as UNSIGNED), 1);

+-----------------------------------------+
| IF(1 != 0, CAST(1*-1 as UNSIGNED), 1) |
+-----------------------------------------+
|                                      -1 |
+-----------------------------------------+

CAST both expr2 and expr3 as UNSIGNED within IF():

mysql> SELECT IF(1 != 0, CAST(-1 as UNSIGNED), CAST(1 as UNSIGNED));

+---------------------------------------------------------+ 
| IF(1 != 0, CAST(1*-1 as UNSIGNED), CAST(1 as UNSIGNED)) |
+---------------------------------------------------------+
|                                                      -1 |
+---------------------------------------------------------+

Use either of these within a TRIGGER:

SET NEW.field = ( SELECT CAST(IF(1 != 0, -1, 1) AS UNSIGNED) );

-- OR -- 

IF ( 1 != 0 ) THEN
 SET NEW.field = ( SELECT CAST(-1 as UNSIGNED) );
ELSE
 SET NEW.field = 1;
END IF;

Suggested fix:
Partial Solution:

If you know that both values that should be returned by the IF() clause should be (UN)SIGNED you can CAST() the entire IF() clause as such. Currently there appears to be no way to CAST one expression and not the other.

mysql> SELECT CAST(IF((1 != 0), -1, 1) AS UNSIGNED);

+-----------------------------------------+
| CAST(IF((1 != 0), 1*-1, 1) AS UNSIGNED) |
+-----------------------------------------+
|                    18446744073709551615 |
+-----------------------------------------+
[9 Jul 2006 7:22] Matthew Montgomery
--Typo--

replace...

CAST expr2 as UNSIGNED within IF():

mysql> SELECT IF(1 != 0, CAST(-1 as UNSIGNED), 1);
+-----------------------------------------+
| IF(1 != 0, CAST(1*-1 as UNSIGNED), 1) |
+-----------------------------------------+
|                                      -1 |
+-----------------------------------------+

with... 

CAST expr2 as UNSIGNED within IF():

mysql> SELECT IF(1 != 0, CAST(-1 as UNSIGNED), 1);
+-------------------------------------+
| IF(1 != 0, CAST(-1 as UNSIGNED), 1) |
+-------------------------------------+
|                                  -1 |
+-------------------------------------+
[1 Sep 2006 20:50] Alexey Kopytov
The following test case applies only to 4.1 and is not reproducible on the latest 5.0 and 5.1:

mysql> SET @id = CAST(-1 AS UNSIGNED);

mysql> select @id;
+------+
| @id  |
+------+
| -1   |
+------+
1 row in set (0.00 sec)

The test case with IF() is reproducible on the latest 4.1, 5.0 and 5.1.
Also, there are more functions besides IF() where this bug occurs in all versions:

mysql> SELECT CASE 1 WHEN 1 THEN CAST(-1 AS UNSIGNED) ELSE 1 END;
+----------------------------------------------------+
| CASE 1 WHEN 1 THEN CAST(-1 AS UNSIGNED) ELSE 1 END |
+----------------------------------------------------+
|                                                 -1 |
+----------------------------------------------------+

mysql> SELECT IFNULL(NULL, CAST(-1 AS UNSIGNED));
+------------------------------------+
| IFNULL(NULL, CAST(-1 AS UNSIGNED)) |
+------------------------------------+
|                                 -1 |
+------------------------------------+

mysql> SELECT COALESCE(CAST(-1 AS UNSIGNED));
+--------------------------------+
| COALESCE(CAST(-1 AS UNSIGNED)) |
+--------------------------------+
|                             -1 |
+--------------------------------+

mysql> SELECT GREATEST(1, CAST(-1 AS UNSIGNED));
+-----------------------------------+
| GREATEST(1, CAST(-1 AS UNSIGNED)) |
+-----------------------------------+
|                                 1 |
+-----------------------------------+

mysql> SELECT LEAST(1, CAST(-1 AS UNSIGNED));
+--------------------------------+
| LEAST(1, CAST(-1 AS UNSIGNED)) |
+--------------------------------+
|                             -1 |
+--------------------------------+

The reason is that unsigned_flag is not honored in the corresponding Item_func_*::val_int() methods.
[6 Sep 2006 10:10] Alexey Kopytov
The "SET @id = ..." portion of this bug is a duplicate of bug #7498 (fixed in 5.0.25, 5.1.12).
[8 Sep 2006 10:08] Bugs System
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/11610

ChangeSet@1.2540, 2006-09-08 14:08:29+04:00, kaa@polly.local +13 -0
  Bug #20924: CAST(expr as UNSIGNED) returns SIGNED value when used in various functions
  - Honor unsigned_flag in the corresponding functions
  - Use compare_int_signed_unsigned()/compare_int_unsigned_signed() instead of explicit comparison in GREATEST() and LEAST()
[12 Sep 2006 12:26] Bugs System
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/11763

ChangeSet@1.2541, 2006-09-12 16:25:40+04:00, kaa@polly.local +11 -0
  Post-review fixes for bug #20924
[12 Sep 2006 18:53] Timothy Smith
Kaamos, thanks - latest patch looks good!

Tim
[13 Sep 2006 16:29] Alexey Kopytov
The patch for this bug has been reverted. A proper solution requires significant code changes and will be implemented later.
[15 Sep 2006 22:21] Chad MILLER
Available in 5.1.12-beta.

(To be fixed later?)
[4 Oct 2006 13:57] Chad MILLER
Available in 4.1.22.
[27 Nov 2006 12:55] Alexey Kopytov
This bug is partially fixed by the fix for bug #22026. IF(), CASE and COALESCE() do work as expected with that fix:

mysql> SELECT IF(1, 18446744073709551615, 1);
+--------------------------------+
| IF(1, 18446744073709551615, 1) |
+--------------------------------+
|           18446744073709551615 |
+--------------------------------+

mysql> SELECT CASE 1 WHEN 1 THEN 18446744073709551615 ELSE 1 END;
+----------------------------------------------------+
| CASE 1 WHEN 1 THEN 18446744073709551615 ELSE 1 END |
+----------------------------------------------------+
|                               18446744073709551615 |
+----------------------------------------------------+

mysql> SELECT COALESCE(18446744073709551615);
+--------------------------------+
| COALESCE(18446744073709551615) |
+--------------------------------+
|           18446744073709551615 |
+--------------------------------+

LEAST((), GREATEST() and IFNULL() are still broken:

mysql> SELECT IFNULL(NULL, 18446744073709551615);
+------------------------------------+
| IFNULL(NULL, 18446744073709551615) |
+------------------------------------+
|                                 -1 |
+------------------------------------+

mysql> SELECT GREATEST(1, 18446744073709551615);
+-----------------------------------+
| GREATEST(1, 18446744073709551615) |
+-----------------------------------+
|                                 1 |
+-----------------------------------+

mysql> SELECT LEAST(1, 18446744073709551615);
+--------------------------------+
| LEAST(1, 18446744073709551615) |
+--------------------------------+
|                             -1 |
+--------------------------------+
[9 Jun 2014 6:33] Shane Bester
mysql> select @@sql_mode,version(),cast(pow(2,64)-1 as unsigned) a,pow(2,64)-1 = 18446744073709551615 as b;
+------------+-----------------+---------------------+------+
| @@sql_mode | version()       | a                   | b    |
+------------+-----------------+---------------------+------+
|            | 5.7.5-m15-debug | 9223372036854775808 |    1 |
+------------+-----------------+---------------------+------+
1 row in set (0.00 sec)