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 8:42	Modified:	22 May 0:55
Reporter:	Matthew Montgomery
Status:	Verified
Category:	Server: Types	Severity:	S2 (Serious)
Version:	All	OS:	Linux (Linux 2.6)
Assigned to:	Alexey Kopytov	Target Version:
Tags:	cast functions, control flow functions
Triage:	Triaged: D2 (Serious)

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