Bug #12612 quoted bigint unsigned value and the use of "in" in where clause
Submitted: 17 Aug 2005 0:48 Modified: 2 Dec 2005 3:20
Reporter: Ming Zhou Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:Mysql 4.1.13/BK source OS:Windows (Windows)
Assigned to: Jim Winstead CPU Architecture:Any

[17 Aug 2005 0:48] Ming Zhou
Description:
Query:
select * from table where col in ('xxxxx')
fails for some range of numbers if col is a bigint unsigned column.

How to repeat:
create table bigint_test (bigint_col bigint unsigned );
insert into bigint_test VALUES (4828532208463511553);
select * from bigint_test where bigint_col in ('4828532208463511553');
select * from bigint_test where bigint_col ='4828532208463511553';

third query returns empty result where as fourth query returns the row.

The range of numbers that cause this problem appears to be starting from 4828532208463510000, up to 4828532208463519999

Suggested fix:
select * from bigint_test where bigint_col in (4828532208463511553);
will work.
[19 Aug 2005 7:44] Vasily Kishkin
I was able to reproduce the bug on Window 2003:

mysql> select * from bigint_test where bigint_col in ('4828532208463511553');
Empty set (0.03 sec)

but:

mysql> select * from bigint_test where bigint_col in (4828532208463511553);
+---------------------+
| bigint_col          |
+---------------------+
| 4828532208463511553 |
+---------------------+
1 row in set (0.00 sec)

Wait for verification on Linux please
[23 Aug 2005 21:52] MySQL Verification Team
This is a Windows server specific bug, it not happens on Linux:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-nt

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

mysql> create table bigint_test (bigint_col bigint unsigned );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into bigint_test VALUES (4828532208463511553);
Query OK, 1 row affected (0.03 sec)

mysql> select * from bigint_test where bigint_col in ('4828532208463511553');
Empty set (0.01 sec)

mysql> select * from bigint_test where bigint_col ='4828532208463511553';
+---------------------+
| bigint_col          |
+---------------------+
| 4828532208463511553 |
+---------------------+
1 row in set (0.00 sec)

mysql>

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.15-debug-log

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

mysql> create table bigint_test (bigint_col bigint unsigned );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into bigint_test VALUES (4828532208463511553);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bigint_test where bigint_col in ('4828532208463511553');
+---------------------+
| bigint_col          |
+---------------------+
| 4828532208463511553 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from bigint_test where bigint_col ='4828532208463511553';
+---------------------+
| bigint_col          |
+---------------------+
| 4828532208463511553 |
+---------------------+
1 row in set (0.01 sec)

mysql>
[22 Oct 2005 1:05] 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/internals/31306
[7 Nov 2005 22:57] 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/internals/32062
[2 Dec 2005 2:33] Jim Winstead
Fixed in 5.0.17 and 5.1.4.
[2 Dec 2005 3:20] Paul DuBois
Noted in 5.0.17, 5.1.4 changelogs.