Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 186
Server 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 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database 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)