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
