From: Jell...@pts.nl To: win...@lists.mysql.com Subject: bug with using bigint values in SELECT query

Description:

I have a table with a record which contains a bigint field with the following value: 9051184014819471 When I try to lookup this record with a SELECT query and a WHERE component for specifying this bigint value, I get a strange result: If I make a 'WHERE field=value' string and I supply the bigintvalue surrounded with quotes, MYSQL doesn't find the record. If I don't supply the quotes, the record is found. Strangely enough, if I use this value but incremented by 1 or decremented by 1 it seems to work in both cases (including the quotes scenario).

How-To-Repeat:

# 1. first create test table with bigint column. CREATE TABLE test (x BIGINT (20) );

# 2. insert two records into test table INSERT INTO test (x) VALUES (9051184014819471); INSERT INTO test (x) VALUES (9051184014819472);

# 3. try to find first record; doesn't work although it should. select x from test where x="9051184014819471"; # -> 0 records, nothing found. BUG?

# 4. try to find next record; works! select x from test where x="9051184014819472"; # -> 1 record found

My question is: what is going on here? In both cases there should have been found records. In reality only the last query returns a record.

How can I solve this problem? I prefer using quotes in my select queries to keep things simple and robust.

Fix: don't use quotes in the where component to specify bigint values. This would induce quit a lot of rework in our SQL generating software.

Exectutable: MySql-win 3.23.54-nt Environment: laptop, default installation System: Win 2000 professional (dutch) Architecture: intel