Bug #7498 User variable SET saves SIGNED BIGINT as UNSIGNED BIGINT
Submitted: 22 Dec 2004 23:12 Modified: 15 Aug 2006 3:18
Reporter: Arnout Veenman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 OS:Any (all)
Assigned to: Magnus Blåudd CPU Architecture:Any

[22 Dec 2004 23:12] Arnout Veenman
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
[23 Dec 2004 6:53] Hartmut Holzgraefe
mysql> SELECT @a := afield FROM bigfailure;
+--------------+
| @a := afield |
+--------------+
|  -1998585759 |
+--------------+
1 row in set (0.10 sec)
[23 Dec 2004 10:08] Heikki Tuuri
Hi!

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

--Heikki
[9 Aug 2005 14:34] Mark Matthews
Please retest to determine platform-specificness.
[13 Aug 2005 7:20] Vasily Kishkin
I tested on Linux SUSE 9.3 and Windows 2003. I have same result for both cases:

mysql> SELECT @a
    -> ;
+-------------+
| @a          |
+-------------+
| -1998585759 |
+-------------+
[16 May 2006 10:41] Magnus Blåudd
Occurs both in 4.1 and 5.0
[30 May 2006 8:35] Magnus Blåudd
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 |
+-------------+
[8 Jun 2006 11:03] 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/7383
[8 Jun 2006 12:15] Magnus Blåudd
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
[8 Jun 2006 13:41] 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/7393
[9 Jun 2006 15:05] Magnus Blåudd
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'
[9 Jun 2006 17:36] 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/7455
[14 Jun 2006 10:32] Alexander Barkov
Magnus, the patch looks ok to me.
If Serg is busy, feel free to set me as a reviewer.
[20 Jul 2006 11:43] Magnus Blåudd
Pushed to 5.0.25
[9 Aug 2006 12:54] Jon Stephens
Documented bugfix in 5.0.25 changelog.

Need to know if/when this is pushed to 5.1. Thanks!
[14 Aug 2006 21:28] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 3:17] Paul Dubois
Noted in 5.1.12 changelog.