Bug #31820 inconsistent String Bigint comparison
Submitted: 24 Oct 2007 17:19 Modified: 30 Oct 2007 16:09
Reporter: Giuseppe DAmico Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S2 (Serious)
Version:5.0.45 OS:Any
Assigned to: CPU Architecture:Any
Tags: BIGINT, comparison, string

[24 Oct 2007 17:19] Giuseppe DAmico
Description:
with a Bigint, containing the same number, inconsistent results are obtained;   
examples: 
length 18
   "100000000000000001"=100000000000000001 -> returns true: correct!
   "100000000000000010"=100000000000000010 -> returns false: wrong!
   "100000000000000100"=100000000000000100 -> returns true: correct!
   "100000000000001000"=100000000000000100 -> returns false: correct!
   "101000000000000000"=101000000000000000 -> returns true: correct!
   "101000000000000000"=101000000000000001 -> returns true: wrong!
length 19
   "1000000000000000010"=1000000000000000010 -> returns true: correct!
   "1000000000000000100"=1000000000000000100 -> returns false: wrong!
   "1000000000000001000"=1000000000000001000 -> returns true: correct!
   "1000000000000010000"=1000000000000001000 -> returns false: correct!
   "1010000000000000000"=1010000000000000000 -> returns true: correct!
   "1010000000000000000"=1010000000000000010 -> returns true: wrong!

How to repeat:
After having created a table and inserted some test values with the following instructions:

Create table  cc (string varchar(30), number Bigint(19) UNSIGNED,field3 tinyint(3));
Create table  cc (number_s varchar(100), number_b Bigint(19) UNSIGNED,length tinyint(3));
INSERT INTO cc values ("100000000000001", 100000000000001, 15),
("100000000000010", 100000000000010, 15),
("100000000000100", 100000000000100, 15),
("100000000001000", 100000000001000, 15),
("100000000010000", 100000000010000, 15),
("100000000100000", 100000000100000, 15),
("100000001000000", 100000001000000, 15),
("100000010000000", 100000010000000, 15),
("100000100000000", 100000100000000, 15),
("100001000000000", 100001000000000, 15),
("100010000000000", 100010000000000, 15),
("100100000000000", 100100000000000, 15),
("101000000000000", 101000000000000, 15),
("110000000000000", 110000000000000, 15),
("1000000000000001", 1000000000000001, 16),
("1000000000000010", 1000000000000010, 16),
("1000000000000100", 1000000000000100, 16),
("1000000000001000", 1000000000001000, 16),
("1000000000010000", 1000000000010000, 16),
("1000000000100000", 1000000000100000, 16),
("1000000001000000", 1000000001000000, 16),
("1000000010000000", 1000000010000000, 16),
("1000000100000000", 1000000100000000, 16),
("1000001000000000", 1000001000000000, 16),
("1000010000000000", 1000010000000000, 16),
("1000100000000000", 1000100000000000, 16),
("1001000000000000", 1001000000000000, 16),
("1010000000000000", 1010000000000000, 16),
("1100000000000000", 1100000000000000, 16),
("10000000000000001", 10000000000000001, 17),
("10000000000000010", 10000000000000010, 17),
("10000000000000100", 10000000000000100, 17),
("10000000000001000", 10000000000001000, 17),
("10000000000010000", 10000000000010000, 17),
("10000000000100000", 10000000000100000, 17),
("10000000001000000", 10000000001000000, 17),
("10000000010000000", 10000000010000000, 17),
("10000000100000000", 10000000100000000, 17),
("10000001000000000", 10000001000000000, 17),
("10000010000000000", 10000010000000000, 17),
("10000100000000000", 10000100000000000, 17),
("10001000000000000", 10001000000000000, 17),
("10010000000000000", 10010000000000000, 17),
("10100000000000000", 10100000000000000, 17),
("11000000000000000", 11000000000000000, 17),
("100000000000000001", 100000000000000001, 18),
("100000000000000010", 100000000000000010, 18),
("100000000000000100", 100000000000000100, 18),
("100000000000001000", 100000000000000100, 18),
("100000000000001000", 100000000000001000, 18),
("100000000000010000", 100000000000010000, 18),
("100000000000100000", 100000000000100000, 18),
("100000000001000000", 100000000001000000, 18),
("100000000010000000", 100000000010000000, 18),
("100000000100000000", 100000000100000000, 18),
("100000001000000000", 100000001000000000, 18),
("100000010000000000", 100000010000000000, 18),
("100000100000000000", 100000100000000000, 18),
("100001000000000000", 100001000000000000, 18),
("100010000000000000", 100010000000000000, 18),
("100100000000000000", 100100000000000000, 18),
("101000000000000000", 101000000000000000, 18),
("101000000000000000", 101000000000000001, 18),
("110000000000000000", 110000000000000000, 18),
("1000000000000000001", 1000000000000000001, 19),
("1000000000000000010", 1000000000000000010, 19),
("1000000000000000100", 1000000000000000100, 19),
("1000000000000001000", 1000000000000001000, 19),
("1000000000000010000", 1000000000000001000, 19),
("1000000000000010000", 1000000000000010000, 19),
("1000000000000100000", 1000000000000100000, 19),
("1000000000001000000", 1000000000001000000, 19),
("1000000000010000000", 1000000000010000000, 19),
("1000000000100000000", 1000000000100000000, 19),
("1000000001000000000", 1000000001000000000, 19),
("1000000010000000000", 1000000010000000000, 19),
("1000000100000000000", 1000000100000000000, 19),
("1000001000000000000", 1000001000000000000, 19),
("1000010000000000000", 1000010000000000000, 19),
("1000100000000000000", 1000100000000000000, 19),
("1001000000000000000", 1001000000000000000, 19),
("1010000000000000000", 1010000000000000000, 19),
("1010000000000000000", 1010000000000000001, 19),
("1100000000000000000", 1100000000000000000, 19);

executing the following SELECT the inconsistent results are shown:
SELECT number_s, number_b, number_s=number_b, length FROM cc c;

next SELECT will show results as they are expected:
SELECT number_s, number_b, number_s=cast(number_b AS CHAR), length FROM cc c;
[24 Oct 2007 17:48] Giuseppe DAmico
Errata corrige 
   Description:
     Comparing a string greater than 17 characters, containing a number, with a Bigint, containing the same number, ....

  How to repeat:
After having created a table and inserted some test values with the following
instructions:

Create table  cc (number_s varchar(30), number_b Bigint(19) UNSIGNED,length tinyint(3));
...
[24 Oct 2007 18:00] Valeriy Kravchuk
Thank you for a problem report. What exact version of MySQL server (4.0.x, 4.1.x, 5.0.x, 5.1.x or what) you had used?
[26 Oct 2007 12:13] Giuseppe DAmico
The version of the server I used is 5.0.45.
The problem is slightly different under linux, but always with incoherent results.
Till string that represent number of 16 digits the comparison seems working fine with numbers of 17 digits and more the comparison starts returning wrong results (as '10000000000000000'=10000000000000001 -> 1).
[28 Oct 2007 11:23] Valeriy Kravchuk
Sorry, but this is not a bug for versions 5.x.y. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html:

"...
In all other cases, the arguments are compared as floating-point (real) numbers."

For floating-point numbers, even double precision ones, you can not expect 17 or more correct digits representation. Read http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html for the details.

When you explicitely CAST to CHAR, you compare values as strings. Hence the results.
[30 Oct 2007 16:09] Giuseppe DAmico
Ok! Sorry!
Thank you!