List:General Discussion« Previous MessageNext Message »
From:Toro Hill Date:November 10 2004 10:35pm
Subject:quote and null
View as plain text  
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?

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