set names utf8;select version();use testdrop table if exists t1;create /* First, we use utf8_unicode_ci, from which our custom collation is derived. The returned rows are what we expect, but not what we want. */ table t1 ( id int unsigned primary key auto_increment, t varchar(64) charset utf8 collate utf8_unicode_ci, index t_idx (t)) engine=innodb charset latin1;insert into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');select * from t1 where t like 'te%';select * from t1 where t like 'test%';select * from t1 where t like 'testcas%';select * from t1 where t like 'testcase%';select * from t1 where t like 'testcasel%';select * from t1 where t like 'tä%';select * from t1 where t like 'ta%';select /* like 'tae%' returns zero rows. That's perfectly fine. */ * from t1 where t like 'tae%';insert /* the duplicates */ into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');select * from t1 where t like 'te%';select * from t1 where t like 'test%';select * from t1 where t like 'testcas%';select * from t1 where t like 'testcase%';select * from t1 where t like 'testcasel%';select /* We expect 4, got 4, but only want 2 */ * from t1 where t like 'tä%';select /* We expect 4, got 4, but only want 2 */ * from t1 where t like 'ta%';select * from t1 where t like 'tae%';select /* Table scans reassure us that index usage is correct. */ * from t1 ignore index (t_idx) where t like 'te%';select * from t1 ignore index (t_idx) where t like 'testcas%';select * from t1 ignore index (t_idx) where t like 'testcase%';select * from t1 ignore index (t_idx) where t like 'testcasel%';select * from t1 ignore index (t_idx) where t like 'tä%';select * from t1 ignore index (t_idx) where t like 'ta%';select * from t1 ignore index (t_idx) where t like 'tae%';drop table if exists t1;create /* Now we switch to our custom collation, where the ordering is right, the umlauts correctly distinguished, but the other result sets b0rked. */ table t1 ( id int unsigned primary key auto_increment, t varchar(64) charset utf8 collate utf8_rsm_ci, index t_idx (t)) engine=innodb charset latin1;insert into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');select /* As long as each entry is unique, result sets are correct. */ * from t1 where t like 'te%';select * from t1 where t like 'test%';select * from t1 where t like 'testcas%';select * from t1 where t like 'testcase%';select * from t1 where t like 'testcasel%';select * from t1 where t like 'tä%';select * from t1 where t like 'ta%';select /* Here, like 'tae%' returns zero rows. That's perfectly fine again. */ * from t1 where t like 'tae%';insert /* Now we insert the duplicates, and selects start yielding rather unexpected result sets. */ into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');select /* We expect 6 rows, get 0. */ * from t1 where t like 'te%';select /* A full match, and all of a sudden the query works as expected, ex 6, got 6. */ * from t1 where t like 'test%';select /* 4 expected, got 0 */ * from t1 where t like 'testcas%';select /* Full match, with a longer string, result set is ok with 4 of 4 rows */ * from t1 where t like 'testcase%';select /* 2 expected, got 0 */ * from t1 where t like 'testcasel%';select /* 2 expected, got 0 */ * from t1 where t like 'tä%';select /* 2 expected, got 0 */ * from t1 where t like 'ta%';select /* 2 expected, got 0 */ * from t1 where t like 'tae%';select /* We repeat the above queries, but force a table scan. Result sets are correct. */ * from t1 ignore index (t_idx) where t like 'te%';select * from t1 ignore index (t_idx) where t like 'test%';select * from t1 ignore index (t_idx) where t like 'testcas%';select * from t1 ignore index (t_idx) where t like 'testcase%';select * from t1 ignore index (t_idx) where t like 'testcasel%';select /* 2 expected, got 2, wanted 2 */ * from t1 ignore index (t_idx) where t like 'tä%';select /* 2 expected, got 2, wanted 2 */ * from t1 ignore index (t_idx) where t like 'ta%';select * from t1 ignore index (t_idx) where t like 'tae%';