Bug #58194 LIKE on indexed column over custom utf8 collation fails on partial match
Submitted: 15 Nov 2010 8:55 Modified: 17 Dec 2010 8:51
Reporter: Nicolas Kratz Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.0 OS:Linux
Assigned to: CPU Architecture:Any
Tags: utf8 uca collation index

[15 Nov 2010 8:55] Nicolas Kratz
Description:
Using LIKE '$string%' on a char or varchar column
 - containing duplicate values
 - using a custom utf8 collation
 - having an index which is actually used
does not return partial matches.

Additional observations:
- The actual collation definition does not seem to matter, as long as it is valid and contains at least one rule.
- It happens with MyISAM and InnoDB.
- Forcing a table scan yields the expected results
- Bug observed on versions: 5.0.51a-debug (built from source), 5.0.51a-24+lenny4-log, 5.0.75-0ubuntu10.5, aka all 5.0 versions I could lay my hands on.
- Bug not observed on various 5.1 linux and mac desktops scattered across the office.

How to repeat:
create table t1 (
     id int unsigned primary key auto_increment,
     t varchar(64) charset utf8 collate utf8_custom_ci,
     index t_idx (t)
);
insert into t1 (t) values ('test'),('testcase'),('testcaselonger');
insert into t1 (t) values ('test'),('testcase'),('testcaselonger');

select * from t1 where t like 'testcase%';

Result:
+----+----------+
| id | t        |
+----+----------+
|  2 | testcase | 
|  5 | testcase | 
+----+----------+
2 rows in set (0.00 sec)

Expected:
+----+----------------+
| id | t              |
+----+----------------+
|  2 | testcase       | 
|  3 | testcaselonger | 
|  5 | testcase       | 
|  6 | testcaselonger | 
+----+----------------+
4 rows in set (0.00 sec)
[17 Nov 2010 8:51] Susanne Ebrecht
As I already pointed out on IRC .. I am not able to repeat this with our 5.0 packages on Ubuntu.

This really seems to be a Debian/Ubuntu package problem.

Did you already got feedback from Debian?
[18 Dec 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".