Bug #3232 Error in searching with like

• Submitted: 18 Mar 2004 14:09
• Modified: 20 Mar 2004 16:44
• Reporter: Zoltán Boros
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.1a-alpha-max-nt
• OS: Windows (Windows XP)
• 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;