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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.14
• OS: Windows (Windows NT)
• 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