List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 11 2004 12:08am
Subject:Re: quote and null
View as plain text  
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
>---
>Now after reading this I thought that select quote(NULL) would 
>return the string 'NULL' (without quotes) and not the NULL value. 
>However, this is not true on the version of mysql that I'm using:
>
>mysql> select version();
>+---------------------+
>| version()           |
>+---------------------+
>| 4.0.22-standard-log |
>+---------------------+
>1 row in set (0.00 sec)
>
>mysql> select isnull(quote(NULL));
>+---------------------+
>| isnull(quote(NULL)) |
>+---------------------+
>|                   1 |
>+---------------------+
>1 row in set (0.01 sec)
>
>mysql> select isnull(quote('not null'));
>+---------------------------+
>| isnull(quote('not null')) |
>+---------------------------+
>|                         0 |
>+---------------------------+
>1 row in set (0.00 sec)
>
>So I guess I'm just confused by the documentation. It's easy enough 
>to get the string 'NULL' by using select ifnull(quote(NULL), 'NULL') 
>or something similiar.
>
>Based on the functionality I experienced I thought that the 
>documentation should read something like:
>---
>QUOTE(str)
>... If the argument is NULL, the return value is NULL. ...
>---
>
>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

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
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