List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 11 2004 2:11am
Subject:Re: quote and null
View as plain text  
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:

You've misunderstood, then.  Without the quotes, NULL is the NULL value, not 
a string.  You have to have quotes to be a string!  NULL is NULL, 'NULL' is 
a string.  MySQL is doing exactly what the manual says.

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

You don't need IFNULL for that.

   mysql> SELECT QUOTE('NULL');
   +---------------+
   | QUOTE('NULL') |
   +---------------+
   | 'NULL'        |
   +---------------+
   1 row in set (0.00 sec)

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

That is what it says, just not in those words.  Your wording is better, 
though, in my opinion.

> Does this seem correct? Have I missed or overlooked something?

It is correct in that it behaves as documented.

> Cheers.
> Toro

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