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
<snip>
>> 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
Why? It is doing exactly as documented. Quote is supposed to
backslash-escape the given string. NULL is not a string. How do you
backslash-escape a NULL string? I think NULL is the only proper output of
QUOTE(NULL), just as NULL is the only proper output of most functions when
given NULL input (with the exception of the NULL-specific functions, of course).
mysql> CREATE TABLE qt (s CHAR(10));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO qt VALUES
-> ('a string'),
-> ('doesn\'t'),
-> (NULL),
-> ('C:\\dir1');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT s, QUOTE(s) FROM qt;
+----------+------------+
| s | QUOTE(s) |
+----------+------------+
| a string | 'a string' |
| doesn't | 'doesn\'t' |
| NULL | NULL |
| C:\dir1 | 'C:\\dir1' |
+----------+------------+
4 rows in set (0.00 sec)
Makes sense to me.
Michael
