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
