| 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: | |
| 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: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>
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'й';