MySQL Bugs: #7498: User variable SET saves SIGNED BIGINT as UNSIGNED BIGINT

Bug #7498	User variable SET saves SIGNED BIGINT as UNSIGNED BIGINT
Submitted:	23 Dec 2004 0:12	Modified:	15 Aug 2006 5:18
Reporter:	Arnout Veenman
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.0.17	OS:	Any (all)
Assigned to:	Magnus Blaudd	Target Version:

Description:
When SETting a user variable from an UNSIGNED BIGINT(20) field, it's being
converted/stored in the user variable as a SIGNED BIGINT(20) value.

How to repeat:
CREATE TABLE `bigfailure` (
`afield` BIGINT UNSIGNED NOT NULL 
);
INSERT INTO `bigfailure` VALUES (18446744071710965857);

SET @a := (SELECT afield FROM bigfailure);
SELECT @a

mysql> SELECT @a := afield FROM bigfailure;
+--------------+
| @a := afield |
+--------------+
|  -1998585759 |
+--------------+
1 row in set (0.10 sec)

Hi!

Changing the category to 'MySQL Server' because the problem hardly is InnoDB-related.

--Heikki

Please retest to determine platform-specificness.

I tested on Linux SUSE 9.3 and Windows 2003. I have same result for both cases:

mysql> SELECT @a
    -> ;
+-------------+
| @a          |
+-------------+
| -1998585759 |
+-------------+

Occurs both in 4.1 and 5.0

This looks like two problems:

1. The "subselect_single_select_engine" returns no unsigned flag. This can be seen with
the below query which dowsn't return any rows.
mysql> select * from bigfailure where afield = (SELECT afield FROM bigfailure); 
Empty set (0.02 sec)

2. The Item_func_set/get_user_var does not care about the unsigned flag.
mysql> set @a=18446744071710965857;
Query OK, 0 rows affected (0.01 sec)

mysql> select @a;
+-------------+
| @a          |
+-------------+
| -1998585759 |
+-------------+

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/7383

Need to check how this will work in 5.0 before proceeding with reviewing the patch. Maybe
I make a new one for 4.1 that better works with 5.0

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/7393

Will fix this from 5.0 and up. Found a problem in combination with ps_protocol. The
compare functions to use for a subselect is determined at prepare phase, when the value
has not yet been converted into a Item_cache_int and thus it's not known that the Item is
unsigned. This makes the compare function 'compare_int_signed_unsigned' to be selected, it
should be 'compare_int_unsigned'

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/7455

Magnus, the patch looks ok to me.
If Serg is busy, feel free to set me as a reviewer.

Pushed to 5.0.25

Documented bugfix in 5.0.25 changelog.

Need to know if/when this is pushed to 5.1. Thanks!

Merged into 5.1.12

Noted in 5.1.12 changelog.