MySQL Bugs: #25591: replace() function fails on string '\\\' for text field

Bug #25591	replace() function fails on string '\\\' for text field
Submitted:	12 Jan 2007 17:16	Modified:	13 Feb 2007 12:41
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.24	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
When run from a Query, this statement returns an error 1064

update `info` set `body`=(replace( `body`,'\\\',''))

The error message shows a halt on the first quotation mark like "\\\'..."

However, when run from the Console it hangs, ending:

mysql> update `info` set `body`=(replace( `body`,'\\\','')) where id>2499;
    -> 
    -> 

The function works on this server in every other case, so '\\\' must be a bug for this function under this version. 

How to repeat:
Do this:
- create a table `info` with a text field `body`,
- add some records with data such as 'hi how are \\\ you?' in the `body` field
- try to replace() the \\\ with an empty string using
 `body`=(replace( `body`,'\\\',''))

This should halt and give the error as described above. The update will not execute.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.27, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Not a bug according to http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html.

Hi all I have a problem.  Using version 5.0 of SQl and trying to run the following query which keeps giving me errors as I have displayed.

here is the query:
SELECT REPLACE(product_name, "\", " ")
FROM jos_vm_product 
WHERE product_sku LIKE '%FUE%';

Error I am getting
Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unclosed quote @ 36
STR: "
SQL: SELECT REPLACE[product_name, "\", " "]
FROM jos_vm_product 
WHERE product_sku LIKE '%FUE%';

SQL query: Documentation

SELECT REPLACE[product_name, "\", " "] FROM jos_vm_product WHERE product_sku LIKE '%FUE%';

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[product_name, "\", " "]
FROM jos_vm_product
WHERE product_sku LIKE '%FUE%'' at line 1

I think the problem is that two escape characters are being used with only one real character. The first \ is taken as an escape character which says take the next character literally. The second \ is the character to be taken literally and then the third \ is saying take the next character literally, but there is none. There is no bug.