MySQL Bugs: #20924: CAST(expr as UNSIGNED) returns SIGNED value when used in various functions

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

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 |
+-----------------------------------------+

--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 |
+-------------------------------------+

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.

The "SET @id = ..." portion of this bug is a duplicate of bug #7498 (fixed in 5.0.25, 5.1.12).

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()

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

Kaamos, thanks - latest patch looks good!

Tim

The patch for this bug has been reverted. A proper solution requires significant code changes and will be implemented later.

Available in 5.1.12-beta.

(To be fixed later?)

Available in 4.1.22.

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 |
+--------------------------------+

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)