Bug #4225 INNODB, LIKE with VARCHAR-columns, latin1_german2_ci collation

• Submitted: 20 Jun 2004 14:33
• Modified: 21 Jun 2004 10:58
• Reporter: [ name withheld ]
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: 4.1.2-alpha-Max-log
• OS: Linux (Linux i386)
• CPU Architecture: Any

[20 Jun 2004 14:33] [ name withheld ]

Description:
Using the LIKE-operator on a varchar-column with collation latin1_german2_ci 
and an index on it gives wrong result sets. The '%' does not match the empty 
word: 
  
select * from a; 
+----+------+ 
| id | t    | 
+----+------+ 
|  1 | abc  | 
|  2 | abcd | 
+----+------+ 
select * from a where t like 'abcd%'; 
Empty set (0.00 sec) 
 
Without an index on t or with collation latin1_swedish_ci, the resultset is 
correct.  
 
We are using a build from the MySQL-soure-RPM with default charset latin1 and 
default collation latin1_german2_ci. 

How to repeat:
create table a ( id int auto_increment primary key, t varchar(100) not null 
collate latin1_german2_ci) engine=innodb; 
insert into a (t) values ('abc','abcd'); 
select * from a where t like 'abcd%'; 
alter table a add index test (t); 
select * from a where t like 'abcd%'; 
alter table a change t t varchar(100) not null collate latin1_swedish_ci; 
select * from a where t like 'abcd%';

[21 Jun 2004 10:58] Heikki Tuuri

Hi!

This is a duplicate of a LIKE 'b%' bug report files earlier. The reason for the bug was that MySQL-4.1.2 changed the ordering of '\0' and space.

This bug is fixed in upcoming 4.1.3.

Regards,

Heikki