MySQL Bugs: #11496: PK cant seem to handle \ correctly

Bug #11496	PK cant seem to handle \ correctly
Submitted:	22 Jun 2005 8:45	Modified:	22 Jun 2005 8:57
Reporter:	Ritesh Nadhani	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.7	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
My select version() says 4.1.7-nt  but it is reproduced in 5.x too.

I have a PK defined on a column (varchar(50) that has data like:

M:\xx\yy\mp3a
M:\xx\yy\mp3b
M:\xx\yy\mp3c
M:\xx\yy\mp3d
M:\xx\yy\mp3e
M:\xx\yy\mp3f
M:\xx\yy\mp3g
M:\xx\yy\mp3h

Query like:

select `col` from tablename1 where `col` like 'M:\\%'

or

select `col` from tablename1 where `col` like 'M:\%'

always returns 0 rows.

Is this a known bug?

How to repeat:
create table `TableName1` (    `id` char (50)   NOT NULL  , PRIMARY KEY ( `id` )  );

insert into `tablename1` ( `id` ) values (  'M:\\xx\\yy\\mp3a' );
insert into `tablename1` ( `id` ) values (  'M:\\xx\\yy\\mp3b' );
insert into `tablename1` ( `id` ) values (  'M:\\xx\\yy\\mp3c' );

Query:

select * from tablename1 where id like 'M:\\%'

I'll do a comment on this:
I have the need to build a PK based on a char/varchar column containing a complete Windows filename (like "D:\folder1\folder2\filename.ext").  I can build the PK but not use it for anything meaningful (neither inserts, updates or deletes).  It's the same with command-line client, MySQL Query Browser and third-party clients.  Must be an issue with the server itself.  I have reproduced it on server versions 4.0.24 and 5.0.7 (both max-nt)

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

From the MySQL manual:

Because MySQL uses the C escape syntax in strings (for example, '\n' to represent newline), you must double any '\' that you use in your LIKE strings. For example, to search for '\n', specify it as '\\n'. To search for '\', specify it as '\\\\' (the backslashes are stripped once by the parser and another time when the pattern match is done, leaving a single backslash to be matched).

http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html

I have read the MySQL Doc on MySQL Escape Sequence.

Both the queries return me 0 results.

select `col` from tablename1 where `col` like 'M:\\%'
or
select `col` from tablename1 where `col` like 'M:\%'

How should I write a query then that will return me the correct result?

I too can add that I do escape the \ -character when trying to use this PK of mine - either doing it myself or by using a GUI-client that does it transparently (and I can verify what that client actually does send to the server).

mysql> select * from tablename1 where id like 'M:\\\\%';
+---------------+
| id            |
+---------------+
| M:\xx\yy\mp3a |
| M:\xx\yy\mp3b |
| M:\xx\yy\mp3c |
+---------------+
3 rows in set (0.00 sec)