| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | Mysql 4.1.13/BK source | OS: | Windows (Windows) |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[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.
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.