Fw: bug with using bigint values in SELECT query

From: Jelle.Dijkstra@pts.nl
To: win32@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.

Synopsis: bug with using bigint values in SELECT query
Submitter-Id:
Originator: Jelle Dijkstra
Organization: PTS, KNSB
MySQL support: MySQL license: #52616
Severity: serious
Priority: high
Category: mysqld
Class: sw-bug
Release: MySql-win 3.23.54-nt

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


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13562@lists.mysql.com
To unsubscribe, e-mail