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:
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

[6 Jun 2007 19:02] mike breuner
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\''.
[12 Jun 2007 15:35] MySQL Verification Team
Thank you for the bug report. Verified as described.
[29 Jan 2008 18:48] Timothy Smith
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:



[30 Jan 2008 15:36] Paul DuBois
I see nothing to do here. The QUOTE() description already
indicates that the return value includes enclosing single quotes,
unless the argument is NULL.