Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 186Server version: 5.0.51a-24+lenny4 (Debian)Type 'help;' or '\h' for help. Type '\c' to clear the buffer.(nick@localhost) [(none)]> set names utf8;Query OK, 0 rows affected (0.00 sec)(nick@localhost) [(none)]> select version();+-------------------+| version() |+-------------------+| 5.0.51a-24+lenny4 |+-------------------+1 row in set (0.00 sec)(nick@localhost) [(none)]> use testReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed(nick@localhost) [test]> drop table if exists t1;Query OK, 0 rows affected (0.01 sec)(nick@localhost) [test]> 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;Query OK, 0 rows affected (0.06 sec)(nick@localhost) [test]> insert into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0(nick@localhost) [test]> select * from t1 where t like 'te%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger |+----+----------------+3 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'test%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger |+----+----------------+3 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcas%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcasel%';+----+----------------+| id | t |+----+----------------+| 3 | testcaselonger |+----+----------------+1 row in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'tä%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast |+----+-------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'ta%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast |+----+-------+2 rows in set (0.00 sec)(nick@localhost) [test]> select /* like 'tae%' returns zero rows. That's perfectly fine. */ * from t1 where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> insert /* the duplicates */ into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0(nick@localhost) [test]> select * from t1 where t like 'te%';+----+----------------+| id | t |+----+----------------+| 1 | test || 6 | test || 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'test%';+----+----------------+| id | t |+----+----------------+| 1 | test || 6 | test || 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcas%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcasel%';+----+----------------+| id | t |+----+----------------+| 3 | testcaselonger || 8 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select /* We expect 4, got 4, but only want 2 */ * from t1 where t like 'tä%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast || 9 | täst || 10 | tast |+----+-------+4 rows in set (0.00 sec)(nick@localhost) [test]> select /* We expect 4, got 4, but only want 2 */ * from t1 where t like 'ta%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast || 9 | täst || 10 | tast |+----+-------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> select /* Table scans reassure us that index usage is correct. */ * from t1 ignore index (t_idx) where t like 'te%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger || 6 | test || 7 | testcase || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcas%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger || 7 | testcase || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger || 7 | testcase || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcasel%';+----+----------------+| id | t |+----+----------------+| 3 | testcaselonger || 8 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'tä%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast || 9 | täst || 10 | tast |+----+-------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'ta%';+----+-------+| id | t |+----+-------+| 4 | täst || 5 | tast || 9 | täst || 10 | tast |+----+-------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> drop table if exists t1;Query OK, 0 rows affected (0.01 sec)(nick@localhost) [test]> 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;Query OK, 0 rows affected (0.02 sec)(nick@localhost) [test]> insert into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0(nick@localhost) [test]> select /* As long as each entry is unique, result sets are correct. */ * from t1 where t like 'te%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger |+----+----------------+3 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'test%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger |+----+----------------+3 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcas%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'testcasel%';+----+----------------+| id | t |+----+----------------+| 3 | testcaselonger |+----+----------------+1 row in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'tä%';+----+-------+| id | t |+----+-------+| 4 | täst |+----+-------+1 row in set (0.00 sec)(nick@localhost) [test]> select * from t1 where t like 'ta%';+----+------+| id | t |+----+------+| 5 | tast |+----+------+1 row in set (0.00 sec)(nick@localhost) [test]> select /* Here, like 'tae%' returns zero rows. That's perfectly fine again. */ * from t1 where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> insert /* Now we insert the duplicates, and selects start yielding rather unexpected result sets. */ into t1 (t) values ('test'),('testcase'),('testcaselonger'),('täst'),('tast');Query OK, 5 rows affected (0.01 sec)Records: 5 Duplicates: 0 Warnings: 0(nick@localhost) [test]> select /* We expect 6 rows, get 0. */ * from t1 where t like 'te%';Empty set (0.00 sec)(nick@localhost) [test]> select /* A full match, and all of a sudden the query works as expected, ex 6, got 6. */ * from t1 where t like 'test%';+----+----------------+| id | t |+----+----------------+| 1 | test || 6 | test || 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select /* 4 expected, got 0 */ * from t1 where t like 'testcas%';Empty set (0.00 sec)(nick@localhost) [test]> select /* Full match, with a longer string, result set is ok with 4 of 4 rows */ * from t1 where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 7 | testcase || 3 | testcaselonger || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 0 */ * from t1 where t like 'testcasel%';Empty set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 0 */ * from t1 where t like 'tä%';Empty set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 0 */ * from t1 where t like 'ta%';Empty set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 0 */ * from t1 where t like 'tae%';Empty set (0.00 sec)(nick@localhost) [test]> 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%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger || 6 | test || 7 | testcase || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'test%';+----+----------------+| id | t |+----+----------------+| 1 | test || 2 | testcase || 3 | testcaselonger || 6 | test || 7 | testcase || 8 | testcaselonger |+----+----------------+6 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcas%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger || 7 | testcase || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcase%';+----+----------------+| id | t |+----+----------------+| 2 | testcase || 3 | testcaselonger || 7 | testcase || 8 | testcaselonger |+----+----------------+4 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'testcasel%';+----+----------------+| id | t |+----+----------------+| 3 | testcaselonger || 8 | testcaselonger |+----+----------------+2 rows in set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 2, wanted 2 */ * from t1 ignore index (t_idx) where t like 'tä%';+----+-------+| id | t |+----+-------+| 4 | täst || 9 | täst |+----+-------+2 rows in set (0.00 sec)(nick@localhost) [test]> select /* 2 expected, got 2, wanted 2 */ * from t1 ignore index (t_idx) where t like 'ta%';+----+------+| id | t |+----+------+| 5 | tast || 10 | tast |+----+------+2 rows in set (0.00 sec)(nick@localhost) [test]> select * from t1 ignore index (t_idx) where t like 'tae%';Empty set (0.00 sec)