Bug #9088 WHERE clause fails with BIGINT if value is between quotes
Submitted: 10 Mar 2005 6:06 Modified: 2 Mar 2006 15:31
Reporter: Gerardo Narvaja Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10 and 5.0.2 OS:Linux (Linux)
Assigned to: Alexey Botchkov CPU Architecture:Any

[10 Mar 2005 6:06] Gerardo Narvaja
Description:
If the value of a BIGINT is enclosed in quotes ('), the where clause fails. Attached is a SQL script to perform the test and following is the generated output:

[code]
mysql> source test.sql
Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----------------------+
| bigint_col           |
+----------------------+
| 17666000000000000000 |
+----------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)
[/quote]
The second select is returning an empy set.

How to repeat:
See attached file: test.sql
[10 Mar 2005 6:07] Gerardo Narvaja
Script to reproduce the problem

Attachment: test.sql (application/octet-stream, text), 252 bytes.

[10 Mar 2005 6:16] Gerardo Narvaja
Verified in 5.0.2 too.
[10 Mar 2005 23:09] Gerardo Narvaja
Bug is not present in 4.1.3 running on Mac
[11 Mar 2005 3:46] Paul Dubois
Bug not present in 4.1.3 on Mac OS X or Gentoo Linux.
Bug is present in 4.1.4 on Mac OS X and Gentoo Linux.
[11 Mar 2005 10:51] Hartmut Holzgraefe
It works up to a value of ~9.223.373.000.000.000.000 which is the max. value of a SIGNED BIGINT, after that it fails.
[15 Mar 2005 3:00] 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/23013
[16 Mar 2005 19:40] 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/23105
[8 May 2005 1:09] Antony Curtis
Patch breaks test 'range'. Under investigation
[2 Jun 2005 19:56] 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/25539
[4 Jun 2005 18:11] Patrick Galbraith
Assigning to myself, and am waiting on consensus from various devs and documentation as to whether this is something that needs to be documented, since comparing two doubles (the final comparison in the server to produce a result) is unreliable.
[8 Jun 2005 17:40] Patrick Galbraith
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

This issue will be documented, waiting on documentation.
[17 Aug 2005 0:13] Ming Zhou
Running on mysql 4.1.13 on Windows:
the third query as listed below fails (fourth one works):

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';

Note 
4828532208463511553 < 
9223372036854775808 (2^63)

The bug is therefore not limited to the numbers > signed big int.
[1 Mar 2006 11:42] 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/commits/3311
[1 Mar 2006 12:28] Alexander Barkov
The patch looks ok to push.
[2 Mar 2006 15:31] Paul Dubois
Noted in 5.0.19 changelog.

A large <literal>BIGINT</literal> value specified in a 
<literal>WHERE</literal> clause could be treated differently
depending on whether it is specified as a quoted string. (For
example, <literal>WHERE bigint_col = 
17666000000000000000</literal> versus <literal>WHERE
bigint_col = '17666000000000000000'</literal>). (Bug #9088)