Description:
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.
How to repeat:
CREATE TABLE `Tmp1` (
`A` char(4) NOT NULL default '',
`B` double 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;
Description: 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. How to repeat: CREATE TABLE `Tmp1` ( `A` char(4) NOT NULL default '', `B` double 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;