At 18:08 -0600 11/10/04, Paul DuBois wrote:
>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
Followup:
The bug has been fixed in the 4.0 tree and should appear whenever 4.0.23
is released. It should also roll forward to the 4.1 and 5.0 series.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
