MySQL Bugs: #20042: Select returns empty result although it shouldn't

Bug #20042	Select returns empty result although it shouldn't
Submitted:	24 May 2006 12:23	Modified:	24 May 2006 17:28
Reporter:	Guenter Hopf	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0.21	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I'm having trouble selecting data from a ndbcluster table.
Select returns an empty result on engine=ndbcluster although it does not, when the table engine is MyISAM. The problem seems to be linked to the data i store in the table, as with simple test records it does not occur.
Also, if there is only one row in the table, the result is correct, after inserting another row the problem is there again.

How to repeat:
use http://www.qe1.org/VHosts.sql to create the table and insert data.
select * from VHosts where domain like '%secure.tda.at';
(1 row)

select * from VHosts where domain='secure.tda.at';
(0 rows)

update VHosts set domain='secure.tda.at' where domain like '%secure.tda.at';
(1 matched, 0 changed !!!)

alter table VHosts engine=MyISAM;

select * from VHosts where domain='secure.tda.at';
(1 row)

alter table VHosts engine=ndbcluster;
create table test (config text, domain varchar(80) primary key) engine=ndbcluster;
insert into test select config, domain from VHosts where domain like '%secure.tda.at';

select * from test where domain='secure.tda.at';
(1 row)

insert into test select config, domain from VHosts where domain like '%www.cryo.at';

select * from test where domain='secure.tda.at';
(0 rows)

Changing category to Cluster.

This is a duplicate of bug #19956 "Problems with VARCHAR primary key and BLOB fields" which has recently been fixed, the fix should be included in 5.0.22

For now the suggested workaround is to change the VARCHAR primary key column to CHAR instead.
As cluster VARCHAR fields are fixed length internally anyway this does not impact memory consumption, the only thing you have to be aware of is the different handling of trailing whitespace, but i don't think this is an issue in your case