Bug #3232 Error in searching with like
Submitted: 18 Mar 2004 14:09 Modified: 20 Mar 2004 16:44
Reporter: Zoltán Boros Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.1a-alpha-max-nt OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[18 Mar 2004 14:09] Zoltán Boros
Description:
The first select gives the correct result (both Michael Jackson and George Michael returned).
The second select only returns Soul II Soul.

How to repeat:
drop table if exists song;
create table song (artist varchar(30));
insert into song values ('Soul II Soul'),('De La Soul'),('Michael Jackson'),('George Michael');
select * from song where artist like ('%Michael%');
select * from song where artist like ('%Soul%');
[20 Mar 2004 16:44] Alexander Keremidarski
I got correct result from second query:

mysql> select * from song where artist like ('%Soul%');
+--------------+
| artist       |
+--------------+
| Soul II Soul |
| De La Soul   |
+--------------+
[21 Mar 2004 1:20] Zoltán Boros
I'm sorry, I wasn't exact enough. I realized that this problem occurs when using latin2 character set. I tried the following script with different collations. They gave the same result, but when binary collation was used the server had gone. Here's the full how-to-repeat script:

drop database if exists songs;
create database songs default character set latin2;
use songs;
set character set latin2;
drop table if exists song;
create table song (artist varchar(30));
insert into song values ('Soul II Soul'),('De La Soul'),('Michael Jackson'),('George Michael');
select * from song where artist like ('%Michael%');
select * from song where artist like ('%Soul%');
select * from song where artist like ('%Soul');
drop database songs;