MySQL Bugs: #9088: WHERE clause fails with BIGINT if value is between quotes

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

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

Script to reproduce the problem

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

Verified in 5.0.2 too.

Bug is not present in 4.1.3 running on Mac

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.

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.

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

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

Patch breaks test 'range'. Under investigation

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

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.

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.

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.

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

The patch looks ok to push.

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)