Description:
Selecting a large unsigned bigint value fails if the value is quoted. Using the 5.0.18-standard release of MySQL. Using the mysql client on Linux, connecting to the local database:
mysql> DROP TABLE IF EXISTS testme;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE testme
-> (
-> mykey bigint unsigned NOT NULL PRIMARY KEY
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> -- both select queries work
mysql> INSERT INTO testme (mykey) VALUES(95443716734461832);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM testme WHERE mykey = 95443716734461832;
+-------------------+
| mykey |
+-------------------+
| 95443716734461832 |
+-------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testme WHERE mykey = '95443716734461832';
+-------------------+
| mykey |
+-------------------+
| 95443716734461832 |
+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> -- the 2nd select query fails to find the record
mysql> INSERT INTO testme (mykey) values(18446744073709551602);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM testme WHERE mykey = 18446744073709551602;
+----------------------+
| mykey |
+----------------------+
| 18446744073709551602 |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM testme WHERE mykey = '18446744073709551602';
Empty set (0.00 sec)
How to repeat:
To reproduce, execute the following SQL statements:
DROP TABLE IF EXISTS testme;
CREATE TABLE testme
(
mykey bigint unsigned NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
-- both select queries work
INSERT INTO testme (mykey) VALUES(95443716734461832);
SELECT * FROM testme WHERE mykey = 95443716734461832;
SELECT * FROM testme WHERE mykey = '95443716734461832';
-- the 2nd select query fails to find the record
INSERT INTO testme (mykey) values(18446744073709551602);
SELECT * FROM testme WHERE mykey = 18446744073709551602;
SELECT * FROM testme WHERE mykey = '18446744073709551602';
Description: Selecting a large unsigned bigint value fails if the value is quoted. Using the 5.0.18-standard release of MySQL. Using the mysql client on Linux, connecting to the local database: mysql> DROP TABLE IF EXISTS testme; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE testme -> ( -> mykey bigint unsigned NOT NULL PRIMARY KEY -> ) ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql> mysql> -- both select queries work mysql> INSERT INTO testme (mykey) VALUES(95443716734461832); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM testme WHERE mykey = 95443716734461832; +-------------------+ | mykey | +-------------------+ | 95443716734461832 | +-------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM testme WHERE mykey = '95443716734461832'; +-------------------+ | mykey | +-------------------+ | 95443716734461832 | +-------------------+ 1 row in set (0.00 sec) mysql> mysql> -- the 2nd select query fails to find the record mysql> INSERT INTO testme (mykey) values(18446744073709551602); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM testme WHERE mykey = 18446744073709551602; +----------------------+ | mykey | +----------------------+ | 18446744073709551602 | +----------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM testme WHERE mykey = '18446744073709551602'; Empty set (0.00 sec) How to repeat: To reproduce, execute the following SQL statements: DROP TABLE IF EXISTS testme; CREATE TABLE testme ( mykey bigint unsigned NOT NULL PRIMARY KEY ) ENGINE=InnoDB; -- both select queries work INSERT INTO testme (mykey) VALUES(95443716734461832); SELECT * FROM testme WHERE mykey = 95443716734461832; SELECT * FROM testme WHERE mykey = '95443716734461832'; -- the 2nd select query fails to find the record INSERT INTO testme (mykey) values(18446744073709551602); SELECT * FROM testme WHERE mykey = 18446744073709551602; SELECT * FROM testme WHERE mykey = '18446744073709551602';