Bug #12611 ESCAPE + LIKE do not work when the escape char is a multibyte one
Submitted: 16 Aug 2005 19:43 Modified: 14 Sep 2005 3:32
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.12-200508162000 OS:Linux (SuSE 9.3)
Assigned to: Alexander Barkov CPU Architecture:Any

[16 Aug 2005 19:43] Andrey Hristov
Description:
According to the SQL standard ESCAPE combined with LIKE is used to set the escape char to escape % and _ which has special meaning when used with a string with LIKE clause. Howerver, if the escape string is in a multibyte encoding, for example utf8 and the escape char is a multibyte one (in the test case a cyrillic letter encoded with 2 bytes) then it does not work.

mysql> create table like_escape(a varchar(100) character set utf8);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into like_escape values ('hakan%'), ('hakank'), ('andre%'), ('andrey');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from like_escape where a like 'hakan*%' escape '*';
+--------+
| a      |
+--------+
| hakan% |
+--------+
1 row in set (0.00 sec)

mysql> select * from like_escape where a like 'andre*%' escape '*';
+--------+
| a      |
+--------+
| andre% |
+--------+
1 row in set (0.00 sec)

mysql> select * from like_escape where a like _utf8'andre*%' escape _utf8'*';
+--------+
| a      |
+--------+
| andre% |
+--------+
1 row in set (0.00 sec)

mysql> select * from like_escape where a like _utf8'andreй%' escape _utf8'й';
Empty set (0.00 sec)

How to repeat:
create table like_escape(a varchar(100) character set utf8);
insert into like_escape values ('hakan%'), ('hakank'), ('andre%'), ('andrey');
select * from like_escape where a like _utf8'andreй%' escape _utf8'й';
[16 Aug 2005 19:59] MySQL Verification Team
mysql>  select * from like_escape where a like _utf8'andreñ%' escape _utf8'ñ';
Empty set (0.00 sec)
[31 Aug 2005 9:08] Bugs System
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/internals/29086
[6 Sep 2005 11:21] Bugs System
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/internals/29360
[7 Sep 2005 13:22] Alexander Barkov
Pushed into 4.1.15 and 5.0.13
[14 Sep 2005 3:32] Mike Hillyer
Documented in 4.1.15 and 5.0.13 changelogs:

<listitem><para>
       The <literal>LIKE ... ESCAPE</literal> syntax produced invalid results when escape character was larger than one byte. (Bug #12611)
      </para></listitem>