Bug #20471 LIKE search fails with indexed utf8 char column

Description:
I create a table with one ut8 column and I insert 6 rows.
I create an index (if there's no index, there's no problem).
I do a LIKE search for some Japanese characters.
The search fails, but the row is there.
If I then delete a different row, the search succeeds.

How to repeat:
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tu3 (s1 char(5) character set utf8);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tu3 values ('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create index itu3 on tu3 (s1);
Query OK, 6 rows affected (0.04 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tu3 where s1 like 'ペテ%';
Empty set (0.01 sec)

mysql> delete from tu3 where s1 = 'Y';
Query OK, 1 row affected (0.01 sec)

mysql> select * from tu3 where s1 like 'ペテ%';
+-----------------+
| s1              |
+-----------------+
| ペテルグル |
+-----------------+
1 row in set (0.00 sec)

Thank you for the bug report.

mysql> create table tu3 (s1 char(5) character set utf8);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tu3 values
    -> ('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create index itu3 on tu3 (s1);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tu3 where s1 like 'ペテ%';
Empty set (0.00 sec)

mysql> delete from tu3 where s1 = 'Y';
Query OK, 1 row affected (0.01 sec)

mysql> select * from tu3 where s1 like 'ペテ%';
+-----------------+
| s1              |
+-----------------+
| ペテルグル |
+-----------------+
1 row in set (0.00 sec)

Version 4.1BK isn't affected:

mysql> insert into tu3 values ('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); (s1);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create index itu3 on tu3 (s1);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from tu3 where s1 like 'ペテ%';';
+-----------------+
| s1              |
+-----------------+
| ペテルグル |
+-----------------+
1 row in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 4.1.21-debug |
+--------------+
1 row in set (0.00 sec)

mysql>

We have duplicate bug #20744

The same problem exists in 4.1 with utf8_general_ci

ysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.02 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values
    -> ('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create index it1 on t1 (s1);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';
Empty set (0.00 sec)

mysql> delete from t1 where s1 = 'Y';
Query OK, 1 row affected (0.00 sec)

mysql> select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';
Empty set (0.00 sec)

The same problem exists in 4.1 with utf8_bin:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.19-debug-log |
+------------------+
1 row in set (0.00 sec)

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (s1 char(5) character set utf8 collate utf8_bin);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values
    -> ('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> create index it1 on t1 (s1);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select s1 as before_delete_bin from t1 where s1 like 'ペテ%';
Empty set (0.00 sec)

mysql> delete from t1 where s1 = 'Y';
Query OK, 1 row affected (0.00 sec)

mysql> select s1 as after_delete_bin from t1 where s1 like 'ペテ%';
Empty set (0.00 sec)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/8547

ok to push

Suggestions made by Sergey Petrunia after reviewing the patch:

* Please document the fact that max_sort_char may have different meanings
* When merging, please try to use my_like_range_mb as it was after fix for BUG#16674.

Setting back to "In progress".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9374

Pushed into 4.1-kt

We Japanese concern about this problem.
When I faced this problem, I changed the utf8 column to binary.

When will these problem be fixed in version 5.0.x?

Dear Shigeki, this fix will most likely appear in the next 5.0 release
(5.0.27)

Dear Alexander Barkov,

We hope a fast release.

Thank you!

Appeared in 5.0 main, marked as 5.0.27
Appeared in 5.1 main, marked as 5.1.13-beta

Noted in 4.1.22, 5.0.30, 5.1.13 changelogs.