Bug #6122 SELECT * FROM paths WHERE pathname LIKE 'c:\\%'

• Submitted: 16 Oct 2004 1:04
• Modified: 16 Oct 2004 5:50
• Reporter: Spencer Fairbairn
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.0.15 and 4.1.5
• OS: Windows (windows 2000, XP)
• CPU Architecture: Any

[16 Oct 2004 1:04] Spencer Fairbairn

Description:
When a slash is in a field's text the LIKE comparison returns 0 records if slashes are included in the search. This is the same for both MySQL Control center and using the C api.
SELECT * FROM paths WHERE pathname LIKE 'c:%';
returns all subdirectories of c:
the same with:  LIKE 'c:\%"   returns 0 rows
as does   LIKE 'c:\\%'
however LIKE 'c:\\\%' returns all subdirectories of c:

All directories starting with c:\foo allways returns zero rows i.e.
LIKE 'c:\\foo%'  returns 0 rows
LIKE 'c:\\foo\%' returns 0 rows
LIKE 'c:\foo%' nothing

try anything.

How to repeat:
create a table of pathnames indexed by pathID
insert paths
c:\
c:\mysql\
c:\mysqladmin\
c:\mysql\bin\
c:\mysql\data\

and SELECT * FROM pathnames WHERE pathname LIKE ......

[16 Oct 2004 5:50] Alexander Keremidarski

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:

\ is escaping character in both MySQL server parser and client command line parser. It has to be escaped twice:

When writing 'c:\\%' into the client the string sent to the server becomes 'c:\%'  which of course will match only exact 'c:%' . Being escaped % is not a wildcard anymore

With 'c:\\\%' client sends 'c:\\%' to the server which is exactly what is needed to match strings with prefix `c:\'