MySQL Bugs: #28932: the quote function does not seem to return a string compatible with INSERT

Bug #28932	the quote function does not seem to return a string compatible with INSERT
Submitted:	6 Jun 2007 19:02	Modified:	30 Jan 2008 15:36
Reporter:	mike breuner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.27/5.0BK/5.1BK	OS:	Any (example below)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	sql insert quote string function

Description:
After inserting quote('Y') into a single-character field,
it gave "Data too long".
 
Should quote('Y') have returned the
three characters 'Y', suitable for INSERTing
the single character 'Y'?
 
It actually seems to have returned the 5 characters '\'Y\''/
so that, had the field been been 3 or more characters
long, it would have contained the 3 characters 'Y' 
instead of the single character Y.

What should it have done in the example?

How to repeat:
drop table if exists test_quote_problem;
create table test_quote_problem (h char(1));

insert into test_quote_problem values('X');
  -- The first insert worked.

insert into test_quote_problem values(quote('Y'));
  -- The second insert failed with the message
  --    Data too long for column 'h'.
  

Suggested fix:
Possibly either document what the quote returns 
when used in an INSERT, or else change it
to return 'Y' instead of '\'Y\''.

Thank you for the bug report. Verified as described.

Hi, Mike, thank you for your bug report.

This is not a bug in MySQL, but rather the intended behavior of the QUOTE() function.  It returns a quoted string - including the enclosing quotes.  It is not useful directly in an INSERT statement.

It may be useful to keep the returned value in your *application*, and splice it into a string there which will be sent as an SQL command.  For example, you might select a QUOTE()ed value from one server, and then insert it into a table on another server.

I am setting this as a documentation bug, in case the documentation team may find a way to clarify this here:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_quote

Regards,

Timothy

I see nothing to do here. The QUOTE() description already
indicates that the return value includes enclosing single quotes,
unless the argument is NULL.