Bug #19342 IN works incorrectly for BIGINT UNSIGNED values
Submitted: 25 Apr 2006 17:08 Modified: 15 Mar 2007 13:19
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0-bk (before 5.0.21)/5.1BK OS:
Assigned to: Georgi Kodinov CPU Architecture:Any

[25 Apr 2006 17:08] Sergey Petrunya
Description:
IN works incorrectly for BIGINT UNSIGNED values.

How to repeat:
create table bigint_u(a bigint unsingned);
insert into bigint_u values (0xFFFFFFFFFFFFFFFF);

mysql> select * from bigint_u where a=-1 or a=-2 ;
Empty set (0.00 sec)
mysql> select * from bigint_u where a in (-1, -2);
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 |
+----------------------+

Suggested fix:
I suspect the cause of this bug is somewhere in Item_func_in::fix_length_and_dec() .
[25 Apr 2006 17:24] Miguel Solorzano
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bigint_u(a bigint unsigned);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bigint_u values (0xFFFFFFFFFFFFFFFF);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bigint_u where a=-1 or a=-2 ;
Empty set (0.01 sec)

mysql> select * from bigint_u where a in (-1, -2);
+----------------------+
| a                    |
+----------------------+
| 18446744073709551615 | 
+----------------------+
1 row in set (0.01 sec)
[13 May 2006 18:41] Sergey Petrunya
The problem also occurs when field is not a BIGINT UNSIGNED but the values in IN-list are:

CREATE TABLE t1 (
  `some_id` smallint(5) default NULL,
  KEY `some_id` (`some_id`)
);

mysql> explain select * from t1 where some_id in (18446744073709551615,18446744073709551612);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.01 sec)

mysql> explain select * from t1 where some_id not in (18446744073709551615,18446744073709551612);
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.01 sec)
[13 Sep 2006 6:18] 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/11816

ChangeSet@1.2272, 2006-09-12 23:17:58-07:00, igreenhoe@anubis.greendragongames.com +4 -0
  Fix for bug #19342 (IN works incorrectly for BIGINT UNSIGNED values)
  
  Problem: the in function extracts and stores the integer portion of
  an integer-valued Item prior to comparison for speed.  However, this
  extraction did not keep track if the value was unsigned in certian
  circumstances, and assumed that the stored value was signed.
  
  Solution: add the extra information so that we can make the
  determination at comparison time if the origional values were
  signed or not.  Note that this may appear to be an inefficent packing
  of the data; however, it ensures that the long longs are
  word-aligned, and also keeps the signedness bit near the value so
  that we can efficently sort.
[29 Oct 2006 20:45] pif
I just applied the patch, but it didn't solve my similar problem.

Here is how i reproduce it :
CREATE TABLE pif (ub bigint unsigned);
insert into pif values(13491727406643098568);
select * from pif where ub=0xBB3C3E98175D33C8;
=> 1 row => ok
select * from pif where ub in (0xBB3C3E98175D33C8);
=> 1 row => ok
select * from pif where ub in (0xBB3C3E98175D33C8, 42);
=> 0 row instead of 1 !

some variants which works :
select * from pif where hex(ub) in (hex(0xBB3C3E98175D33C8), 42);
=> 1 row
select * from pif where ~ub in (~0xBB3C3E98175D33C8, 42);
=> 1 row (so it's a signed/unsigned problem)

hope this helps.
[1 Feb 2007 0:44] Ian Greenhoe
Additional test cases for which this fails:

CREATE TABLE t1 (c1 bigint unsigned);
insert into t1 values(13491727406643098568);
insert into t1 values(0x7ffffffffffffffe);
insert into t1 values(0x7fffffffffffffff);
insert into t1 values(0x8000000000000000);
insert into t1 values(0x8000000000000001);
insert into t1 values(0x8000000000000002);
insert into t1 values(0x8000000000000300);
insert into t1 values(0x8000000000000400);
insert into t1 values(0x8000000000000401);
select * from t1 where c1 in (0xBB3C3E98175D33C8, 42);
select * from t1 where c1 in
	(0xBB3C3E98175D33C8,
	 0x7fffffffffffffff,
	 0x8000000000000000,
	 0x8000000000000400,
	 0x8000000000000401,
	 42);
select * from t1 where c1 in
	(0x7fffffffffffffff,
	 0x8000000000000001);
select * from t1 where c1 in
	(0x7ffffffffffffffe,
	 0x7fffffffffffffff);

drop table t1;

The first select returns no rows, and the last three return

 9223372036854775806
 9223372036854775807
 9223372036854775808
 9223372036854775809
 9223372036854775810
 9223372036854776576
 9223372036854776832

... which translates to the following rows:
0x7ffffffffffffffe, 0x7fffffffffffffff, 0x8000000000000000, 0x8000000000000001, 0x8000000000000002, 0x8000000000000300, 0x8000000000000400

The first and last rows seem to not be returned because everything above 0x8000000000000400 is not returned.

Further, it seems to require that two things are needed: Two (or more) items in the "in(...)" part of the statement, and that one of the items in the "in" must fall in the magic range.  I'm not sure what the lower bound of the magic range is.

This was discovered on a version patched with essentially the patch above, and verified on a clean build of the MySQL 5.0 server.
[1 Feb 2007 17:29] Chad MILLER
+ CREATE TABLE t1 (c1 bigint unsigned);
+ insert into t1 values(13491727406643098568);
+ insert into t1 values(0x7fffffefffffffff);
+ insert into t1 values(0x7ffffffeffffffff);
+ insert into t1 values(0x7fffffffefffffff);
+ insert into t1 values(0x7ffffffffeffffff);
+ insert into t1 values(0x7fffffffffefffff);
+ insert into t1 values(0x7ffffffffffeffff);
+ insert into t1 values(0x7fffffffffffefff);
+ insert into t1 values(0x7ffffffffffffeff);
+ insert into t1 values(0x7fffffffffffffef);
+ insert into t1 values(0x7ffffffffffffffe);
+ insert into t1 values(0x7fffffffffffffff);
+ insert into t1 values(0x8000000000000000);
+ insert into t1 values(0x8000000000000001);
+ insert into t1 values(0x8000000000000002);
+ insert into t1 values(0x8000000000000300);
+ insert into t1 values(0x8000000000000400);
+ insert into t1 values(0x8000000000000401);
+ insert into t1 values(0x8000000000004001);
+ insert into t1 values(0x8000000000040001);
+ insert into t1 values(0x8000000000400001);
+ insert into t1 values(0x8000000004000001);
+ insert into t1 values(0x8000000040000001);
+ insert into t1 values(0x8000000400000001);
+ insert into t1 values(0x8000004000000001);
+ insert into t1 values(0x8000040000000001);
+ select * from t1 where c1 in (0xBB3C3E98175D33C8, 42);
+ c1
+ select hex(c1) from t1 where c1 in
+ (0xBB3C3E98175D33C8,
+ 0x7fffffffffffffff,
+ 0x8000000000000000,
+ 0x8000000000000400,
+ 0x8000000000000401,
+ 42);
+ hex(c1)
+ 7FFFFFFFFFFFFEFF
+ 7FFFFFFFFFFFFFEF
+ 7FFFFFFFFFFFFFFE
+ 7FFFFFFFFFFFFFFF
+ 8000000000000000
+ 8000000000000001
+ 8000000000000002
+ 8000000000000300
+ 8000000000000400
+ select hex(c1) from t1 where c1 in
+ (0x7fffffffffffffff,
+ 0x8000000000000001);
+ hex(c1)
+ 7FFFFFFFFFFFFEFF
+ 7FFFFFFFFFFFFFEF
+ 7FFFFFFFFFFFFFFE
+ 7FFFFFFFFFFFFFFF
+ 8000000000000000
+ 8000000000000001
+ 8000000000000002
+ 8000000000000300
+ 8000000000000400
+ select hex(c1) from t1 where c1 in
+ (0x7ffffffffffffffe,
+ 0x7fffffffffffffff);
+ hex(c1)
+ 7FFFFFFFFFFFFEFF
+ 7FFFFFFFFFFFFFEF
+ 7FFFFFFFFFFFFFFE
+ 7FFFFFFFFFFFFFFF
+ 8000000000000000
+ 8000000000000001
+ 8000000000000002
+ 8000000000000300
+ 8000000000000400
[13 Feb 2007 8:30] 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/19754

ChangeSet@1.2408, 2007-02-13 10:29:57+02:00, gkodinov@macbook.gmz +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[19 Feb 2007 17:24] 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/20107

ChangeSet@1.2408, 2007-02-19 16:34:09+02:00, gkodinov@macbook.gmz +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[19 Feb 2007 17:31] 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/20091

ChangeSet@1.2408, 2007-02-19 15:26:37+02:00, gkodinov@macbook.gmz +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[22 Feb 2007 12:50] 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/20354

ChangeSet@1.2408, 2007-02-22 14:50:13+02:00, gkodinov@macbook.gmz +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[22 Feb 2007 13: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/20356

ChangeSet@1.2408, 2007-02-22 15:25:57+02:00, gkodinov@macbook.gmz +6 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[2 Mar 2007 10:49] 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/20997

ChangeSet@1.2408, 2007-03-02 12:48:35+02:00, gkodinov@macbook.gmz +5 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[2 Mar 2007 14: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/21027

ChangeSet@1.2408, 2007-03-02 16:25:56+02:00, gkodinov@macbook.gmz +5 -0
  Bug #19342:
  Several problems here :
   1. The conversion to double of an hex string const item
   was not taking into account the unsigned flag.
   
   2. IN was not behaving in the same was way as comparisons
   when performed over an INT/DATE/DATETIME/TIMESTAMP column
   and a constant. The ordinary comparisons in that case 
   convert the constant to an INTEGER value and do int 
   comparisons. Fixed the IN to do the same.
   
   3. IN is not taking into account the unsigned flag when 
   calculating <expr> IN (<int_const1>, <int_const2>, ...).
   Extended the implementation of IN to store and process
   the unsigned flag for its arguments.
[6 Mar 2007 16:55] 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/21241

ChangeSet@1.2410, 2007-03-06 18:52:00+02:00, gkodinov@macbook.gmz +2 -0
  Bug#19342: additional test case for code coverage
[9 Mar 2007 23:08] Alexey Botchkov
pushed in 5.0.38
[15 Mar 2007 3:13] Paul Dubois
Noted in 5.0.38 changelog.

For expr IN(value_list), the result could be incorrect if BIGINT
UNSIGNED values were used for expr or in the value list.
[15 Mar 2007 8:40] Georgi Kodinov
Pushed in 5.1.17
[15 Mar 2007 13:19] Paul Dubois
Noted in 5.1.17 changelog.