Bug #20744 MySQL return no result(with utf8 collate)

• Submitted: 28 Jun 2006 5:06
• Modified: 28 Jun 2006 18:56
• Reporter: 貴史 海野
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.18
• OS: MiracleLinuxV3.0
• CPU Architecture: Any

[28 Jun 2006 5:06] 貴史 海野

Description:
Using utf8 character set, and short length of varchar, and long expression of like operator, MySQL return no result.

This behavior is same 5.0.22.

How to repeat:

mysql> create table test2 (name varchar(30)  not null primary key) engine=InnoDB
  default character set utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test2 (name) values ('あいうえおかきくけこさしすせそ');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test2;
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where name like 'あいうえおかきくけこさしすせそ';
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
+--------------------------------+
1 row in set (0.00 sec)

mysql> insert into test2 (name) values ('さしすせそかきくけこあいうえお');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
| さしすせそかきくけこあいうえお |
+--------------------------------+
2 rows in set (0.00 sec)

mysql> select * from test2 where name like 'さしすせそかきくけこあいうえお%';
Empty set (0.00 sec)

mysql> select * from test2 where name = 'さしすせそかきくけこあいうえお';
+--------------------------------+
| name                           |
+--------------------------------+
| さしすせそかきくけこあいうえお |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where name like 'あいうえおかきくけこさしすせそ';
Empty set (0.00 sec)

mysql> select * from test2 where name = 'あいうえおかきくけこさしすせそ';
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where name like 'あいうえおかきくけ%';
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
+--------------------------------+
1 row in set (0.00 sec)

mysql> alter table test2 modify column name varchar(100);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from test2 where name like 'あいうえおかきくけこさしすせそ';
+--------------------------------+
| name                           |
+--------------------------------+
| あいうえおかきくけこさしすせそ |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.0.18-max |
+------------+
1 row in set (0.02 sec)

mysql> 
mysql>

[28 Jun 2006 7:15] Sveta Smirnova

Which collation do you use?

[28 Jun 2006 8:02] 貴史 海野

>Which collation do you use?
I use utf8_general_ci collation.

[28 Jun 2006 18:56] Sveta Smirnova

Thank you for your interest in MySQL.

I checked with ALTER TABLE DROP PRIMARY KEY and MySQL 4.1bk too. Behaviour same as in bug #20471. So I mark this bug as duplicate. Please, if you want to add some comments to this issue, add they to the original bug http://bugs.mysql.com/bug.php?id=20471.

Also in my tests first select statement returns no result:

mysql> select * from test2 where name like
'あいうえおかきくけこさしすせそ';
Empty set (0,00 sec)