Bug #43622 text datatype column does not match the long value
Submitted: 13 Mar 2009 7:52 Modified: 12 Apr 2014 18:49
Reporter: Saminathan Arumugam Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:4.1.22, 5.0, 5.1 OS:Any (Linux 64-bit, 32-bit Mac)
Assigned to: CPU Architecture:Any
Tags: long, match, text, where
Triage: Triaged: D2 (Serious)

[13 Mar 2009 7:52] Saminathan Arumugam
Description:
In MySQL 4.1.22-standard-log, the following issue occurs:

create table testval(col1 text collate utf8_unicode_ci) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into testval values('73923000002519353');

select * from testval where col1 = 73923000002519353;
Empty set (0.00 sec)

select * from testval where col1 = '73923000002519353'; // This gives the result set

Why this happens for the value 73923000002519353 alone ??

I can solve this by having within quotes, just curious to know about the actual problem ?

Thanks for your time,

How to repeat:
create table testval(col1 text collate utf8_unicode_ci) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

insert into testval values('73923000002519353');

select * from testval where col1 = 73923000002519353;
Empty set (0.00 sec)
[13 Mar 2009 8:41] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior with current development sources. Please upgrade to at least current version 4.1.25
[13 Mar 2009 8:50] Saminathan Arumugam
Can you try the same in 4.1.22 version - Its reproducable
[13 Mar 2009 9:26] Sveta Smirnova
Thank you for the feedback.

We don't backport fixes anyway. But I noticed same problem on Mac with new version and could not repeat on Linux with mysql-standard-4.1.22-pc-linux-gnu-i686-glibc23 package. Please indicate which package do you use and version of Linux do you run. Is it 32-bit or 64-bit?
[13 Mar 2009 9:57] Sveta Smirnova
Probably duplicate of bug #27869, but circumstances under which repeatable this one still are not clear.
[13 Mar 2009 10:07] Saminathan Arumugam
I use mysql-standard-4.1.22-unknown-linux-gnu-x86_64-glibc23.tar
and 64 bit linux
[13 Mar 2009 17:11] Sveta Smirnova
Thank you for the feedback.

Verified as described. Most likely this is duplicate of bug #27869 (==feature request, see its descriptions for details), but there are several inconsistencies why I set this one as verified and not duplicate:

1. Problem is not repeatable on 32-bit Linux with any version
2. Problem is repeatable on 64-bit Linux with versions 4.1, 5.0, 5.1, but not 6.0
3. Problem is repeatable on Mac OSX 10.4 with versions 5.0 and 5.1, but not 4.1
[14 Mar 2014 9:42] Hartmut Holzgraefe
Still Reproducible with 5.1.70 on 64bit linux, 5.5.34 and 5.6.18 return a result just fine though.

So should be either "Closed" or "Won't fix" now that 5.1 is at EOL?
[12 Apr 2014 18:49] Erlend Dahl
Closing this since it works in 5.5+.