Bug #983 incorrect parsing of escape character near wildcard with LIKE
Submitted: 3 Aug 2003 7:05 Modified: 4 Aug 2003 4:10
Reporter: Jim Hetland Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.14 OS:Microsoft Windows (Windows NT)
Assigned to: CPU Architecture:Any

[3 Aug 2003 7:05] Jim Hetland
Description:
This query

 select * from table1 where field1 like '\\%'

is intended to retrieve records where field1 contains a leading backslash character, such as  "\xxx".  It does not.

This query DOES retrieve the records intended by the previous one:

 select * from table1 where field1 like '\\\%'

Unless my brain is totally sprained, this is an error.  

I have tested with 3.23.54 on Win2K, 4.0.13 on WinNT, and now 4.0.14 on WinNT.  The result is the same with Navicat, MySQLCC and mysqlc.exe.

CHAR and VARCHAR fields appear to behave the same, and some other queries with values positioned differently do work.  The common factor appears to be a backslash adjacent to a wildcard.

How to repeat:
mysql> describe nada;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     |      | PRI | 0       |       |
| field1 | varchar(50) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from nada;
+----+--------+
| id | field1 |
+----+--------+
|  1 | xxxx   |
|  2 | \xxxx  |
+----+--------+
2 rows in set (0.01 sec)

mysql> select * from nada where field1 like '\\%';
Empty set (0.00 sec)

mysql> select * from nada where field1 not like '\\%';
+----+--------+
| id | field1 |
+----+--------+
|  1 | xxxx   |
|  2 | \xxxx  |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from nada where field1 like '\\\%';
+----+--------+
| id | field1 |
+----+--------+
|  2 | \xxxx  |
+----+--------+
1 row in set (0.00 sec)
[4 Aug 2003 4:10] Alexander Keremidarski
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Same as #412 http://bugs.mysql.com/bug.php?id=412