MySQL Bugs: #43622: text datatype column does not match the long value

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

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)

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

Can you try the same in 4.1.22 version - Its reproducable

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?

Probably duplicate of bug #27869, but circumstances under which repeatable this one still are not clear.

I use mysql-standard-4.1.22-unknown-linux-gnu-x86_64-glibc23.tar
and 64 bit linux

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

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?

Closing this since it works in 5.5+.