MySQL Bugs: #11583: escape ' ( \' ) does not seem to work

Bug #11583	escape ' ( \' ) does not seem to work
Submitted:	26 Jun 2005 20:54	Modified:	16 Jul 2005 20:42
Reporter:	Paul Sanderson	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.06 Beta	OS:	Windows (XP Pro SP2)
Assigned to:		CPU Architecture:	Any

Description:
Coding an application in Borland C++ Builder 6.

when atempting to insert a strin (a path name) containing a quote character i.e.
'c:\my documents\paul's docs' escaping the apostrophe in paul's does not seem to work. and an error occurs, 'missing right quote'

Using a double quote i.e. 
'c:\my documents\paul''s docs', does work.

How to repeat:
See above

Same issue as this one:
http://bugs.mysql.com/bug.php?id=11496

.. which was incorrectly I guess classified as "Not a Bug"

Links to discussion about this issue in another Forum
http://bugs.mysql.com/bug.php?id=11496
http://www.webyog.com/forums/index.php?act=ST&f=6&t=1503&s=9ff73617019456fe50b113bb2707309...

Hi!
I didn't catch from the bug report why you set subject "escape ' ( \' ) does not seem to work". Actually I didn't see in description that you used escape sequences.  Could you explain more detailed how did you use escape sequences when you inserted data?

I made a little check against mysql 4.1 and 5.0.7-beta and did see any errors here:

mysql> insert into `tablename1` ( `id` ) values (  'c:\\my documents\\paul\'s docs');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tablename1;
+-----------------------------+
| id                          |
+-----------------------------+
| c:\my documents\paul's docs |
+-----------------------------+
1 row in set (0.00 sec)

try my case then!

create database if not exists `test`;

USE `test`;

/*Table structure for table `test` */

drop table if exists `test`;

CREATE TABLE `test` (
`artist` varchar(255) default NULL,
`albumtitle` varchar(255) default NULL,
`title` varchar(255) default NULL,
`filename` char(255) default NULL
);

/*Data for the table `test` */

insert into `test` values ('Abaji','Oriental Voyage','maa','M:\\Music\\Abaji\\Oriental Voyage\\11 maa.mp3');

/* Queries that work as expected */
/*1*/ select * from test.test where filename like 'M:%';
/*2*/ select * from test.test where filename like '%mp3';
/*3*/ select * from test.test where filename like '%\\11 maa.mp3';

/* Queries that don't work but should */
/* It seems like that 'too many' \ -characters in data and/or query influences error */
/*4*/ select * from test.test where filename like '%Voyage\\11 maa.mp3';
/*5*/ select * from test.test where filename like '%\\Oriental Voyage\\11 maa.mp3';
/*6*/ select * from test.test where filename like '%\\Abaji\\Oriental Voyage\\11 maa.mp3'; 
/*7*/ select * from test.test where filename like '%\\Oriental Voyage\\%';

/* However these two works - but I think they should not! */
/* - 8 should match string '\Oriental Voyage\%' since \% is the escape sequence of of a literal %-character */
/*8*/ select * from test.test where filename like '%\\Oriental Voyage\\\%';
/*9*/ select * from test.test where filename like 'M:\\\%';

/* there isn't much logic, since these two don't work - again 'too many' \ characters ? */
/*10*/ select * from test.test where filename like 'M:\\Music\\\%';
/*11*/ select * from test.test where filename like 'M:\\Music\\Abaji\\Oriental Voyage\\\%';

/* these don't work either */
/*12*/ select * from test.test where filename like 'M:\\%';
/*13*/ select * from test.test where filename like 'M:\\Music%';
/*14*/ select * from test.test where filename like 'M:\\Music\\Abaji\\Oriental Voyage\\%';
/*15*/ select * from test.test where filename like 'M:\\Music\\Abaji\\Oriental Voyage\\11 maa.mp3';

/* this one works here - compare with 15! */
/*16*/ select * from test.test where filename = 'M:\\Music\\Abaji\\Oriental Voyage\\11 maa.mp3';

/* NOTE: Result the same with this case no matter what column is the PK (or if there are any PK or indexes at all!) */ 
/* and inserts, updates and deletes function/malfunction the same way with the same WHERE-clauses */

Thank You Aleksey for getting yourself involved :-)

I saw your posting in the other thread and it inspired me to test some variations of my queries.  I must say that to me it seems totally inpredictable when you must escape the \-character like "\\" and when like "\\\\".  

if you still believe it works as it should, then the four lines in the mySQL doc's qouted by Victoria isn't sufficient documentation!  If for instance the "folder tree" (file path) is expanded some levels you will have to experiment with the queries to find the right syntax for exactly that folder tree.  That can't be right!

Expanded exaamples: 
/*5 works not */ select from test.test where filename like '%\\Oriental Voyage\\11 maa.mp3';
/*5a works */ select * from test.test where filename like '%\\Oriental Voyage\\\\11 maa.mp3';
/*5b works not */ select * from test.test where filename like '%\\\\Oriental Voyage\\11 maa.mp3';
/*5c works */ select * from test.test where filename like '%\\\\Oriental Voyage\\\\11 maa.mp3';

/*6 works not */ select * from test.test where filename like '%\\Abaji\\Oriental Voyage\\11 maa.mp3'; 
/*6a works not */ select * from test.test where filename like '%\\Abaji\\Oriental Voyage\\\\11 maa.mp3'; 
/*6b works */ select * from test.test where filename like '%\\Abaji\\\\Oriental Voyage\\\\11 maa.mp3'; 
/*6c works not */ select * from test.test where filename like '%\\\\Abaji\\\\Oriental Voyage\\11 maa.mp3'; 
/*6d works */ select * from test.test where filename like '%\\\\Abaji\\\\Oriental Voyage\\\\11 maa.mp3'; 

/*7 works not */ select * from test.test where filename like '%\\Oriental Voyage\\%';
/*7a works not */ select * from test.test where filename like '%\\\\Oriental Voyage\\%';
/*7b works */ select * from test.test where filename like '%\\Oriental Voyage\\\\%';
/*7c works */ select * from test.test where filename like '%\\\\Oriental Voyage\\\\%';

Sorry Aleksey ...

I believe I understand now.  
I can understand, predict and verify the result of all queries that I posted here now! You won't have to answer any of my postings.

But - My God - how the doc's are cryptic .... :-(