List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 11 2004 2:22am
Subject:Re: quote and null
View as plain text  

Paul DuBois wrote:

> At 11:35 +1300 11/11/04, Toro Hill wrote:
> 
>> Hi all.
>> I have question about how the function quote() works with NULL values. 
>> Here is what the mysql manual say:
>> ---
>> QUOTE(str)
>> Quotes a string to produce a result that can be used as a properly 
>> escaped data value in an SQL statement. The string is returned 
>> surrounded by single quotes and with each instance of single quote 
>> (`''), backslash (`\'), ASCII NUL, and Control-Z preceded by a 
>> backslash. If the argument is NULL, the return value is the word 
>> ``NULL'' without surrounding single quotes. The QUOTE() function was 
>> added in MySQL 4.0.3.
>>
>> mysql> SELECT QUOTE('Don\'t!');
>>         -> 'Don\'t!'
>> mysql> SELECT QUOTE(NULL);
>>         -> NULL
<snip>
>> Does this seem correct? Have I missed or overlooked something?
>
> No, I think this is a bug.  QUOTE(NULL) should return a string,
> not a NULL value.  I've filed a bug report:
> 
> http://bugs.mysql.com/bug.php?id=6564

Why?  It is doing exactly as documented.  Quote is supposed to 
backslash-escape the given string.  NULL is not a string.  How do you 
backslash-escape a NULL string?  I think NULL is the only proper output of 
QUOTE(NULL), just as NULL is the only proper output of most functions when 
given NULL input (with the exception of the NULL-specific functions, of course).

   mysql> CREATE TABLE qt (s CHAR(10));
   Query OK, 0 rows affected (0.01 sec)

   mysql> INSERT INTO qt VALUES
       -> ('a string'),
       -> ('doesn\'t'),
       -> (NULL),
       -> ('C:\\dir1');
   Query OK, 4 rows affected (0.01 sec)
   Records: 4  Duplicates: 0  Warnings: 0

   mysql> SELECT s, QUOTE(s) FROM qt;
   +----------+------------+
   | s        | QUOTE(s)   |
   +----------+------------+
   | a string | 'a string' |
   | doesn't  | 'doesn\'t' |
   | NULL     | NULL       |
   | C:\dir1  | 'C:\\dir1' |
   +----------+------------+
   4 rows in set (0.00 sec)

Makes sense to me.

Michael
Thread
quote and nullToro Hill10 Nov
  • Re: quote and nullEric Bergen11 Nov
  • Re: quote and nullPaul DuBois11 Nov
    • Re: quote and nullMichael Stassen11 Nov
    • Re: quote and nullPaul DuBois12 Nov
  • Re: quote and nullMichael Stassen11 Nov
    • Re: quote and nullPaul DuBois11 Nov
      • Re: quote and nullMichael Stassen11 Nov
        • Re: quote and nullPaul DuBois11 Nov
          • Re: quote and nullMichael Stassen11 Nov