Hi,

It seems like it is a bug in MySQL version 4.0.20-standard-log. Comparison between string and concat function returns wrong result. Look at short example:

CREATE TABLE `Tmp` ( `A` char(4) NOT NULL default '', `B` double default NULL, `C` date default NULL, `D` tinyint(4) default NULL ) TYPE=MyISAM;

insert into Tmp (A,B,C,D) values ('AAAA',105,'2003-03-01',1);

select * from Tmp where concat(A,C,B,D) = 'AAAA2003-03-011051'; # 0 records - BUG!!!

It returns 0 records and it is wrong! But if you will change fields order in the concat function it will work fine (it returns 1 record):

select * from Tmp where concat(A,B,C,D) = 'AAAA1052003-03-011';

# FALSE as comparison result - BUG!!! select concat(A,C,B,D),'AAAA2003-03-011051',(concat(A,C,B,D) = 'AAAA2003-03-011051') from Tmp; # TRUE as comparison result select concat(A,B,C,D),'AAAA1052003-03-011',(concat(A,B,C,D) = 'AAAA1052003-03-011') from Tmp; drop table Tmp;

But if you change type of the 'B' field from double to int everything is working fine:

CREATE TABLE `Tmp1` ( `A` char(4) NOT NULL default '', `B` int default NULL, `C` date default NULL, `D` tinyint(4) default NULL ) TYPE=MyISAM; insert into Tmp1 (A,B,C,D) values ('AAAA',105,'2003-03-01',1); select * from Tmp1 where concat(A,C,B,D) = 'AAAA2003-03-011051'; select * from Tmp1 where concat(A,B,C,D) = 'AAAA1052003-03-011'; select concat(A,C,B,D),'AAAA2003-03-011051',(concat(A,C,B,D) = 'AAAA2003-03-011051') from Tmp1; select concat(A,B,C,D),'AAAA1052003-03-011',(concat(A,B,C,D) = 'AAAA1052003-03-011') from Tmp1; drop table Tmp1;

Best regards, Mikhail.