MySQL Bugs: #8495: LIKE operator does not handle escape character on indexed field

Bug #8495	LIKE operator does not handle escape character on indexed field
Submitted:	14 Feb 2005 12:19	Modified:	28 Jun 2005 6:04
Reporter:	Alexander Motin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.9	OS:	FreeBSD (FreeBSD 5.3, FreeBSD 5.2.1)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
There are some strange problem with LIKE operator on indexed field declared as "char(5) binary". In seems like it does not support escaping char well. 

If field is not indexed or it is not "... binary" everything works fine.

May be it is somehow related with used collation.

How to repeat:
CREATE TABLE `a` (
  `a` char(5) character set koi8r collate koi8r_bin NOT NULL default '',
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=koi8r

... insert some data ...

mysql> select * from a where a like '\_d%';
Empty set (0,00 sec)

mysql> select * from a where a like '_d%';
+-------+
| a     |
+-------+
| _d    |
| _dzzz |
| kd    |
| kdzzz |
+-------+

mysql> alter table a change a a char(5) NOT NULL default ''

mysql> select * from a where a like '_d%';
+-------+
| a     |
+-------+
| kd    |
| kdzzz |
| _d    |
| _dzzz |
+-------+
4 rows in set (0,00 sec)

mysql> select * from a where a like '\_d%';
+-------+
| a     |
+-------+
| _d    |
| _dzzz |
+-------+
2 rows in set (0,01 sec)

i'd like to report same problem with czech characters, using either latin2_bin or cp1250_bin collation

As additional information:-

We have a DB table with surnames in as an index and some have ' (eg O'Reily).  Some of these had become corrupted by a function double escaping them so we had \' (and even \\\\') (eg O\'Reily).  
We tried to use the query "select * from Members where Surname like '%\\%'" to find those that had become corrupt but it didn't find them.  Only when we had the like clause as '%\\\%' did it work.  To me that would read as we are searching for something ending in \%.

mysql  Ver 14.7 Distrib 4.1.10, for pc-linux-gnu (i686)
O.S.  Red hat Linux unknown version.

These problems were fixed earlier:

bug#8560 in 4.1.11
bug#9759 in 4.1.13