Paul DuBois wrote:
> At 21:11 -0500 11/10/04, Michael Stassen wrote:
>
>> 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.
>
>
> It's not. The manual says that if the argument is NULL, the return
> value is _the word_ NULL without quotes. In other words, it's a string
> but the string doesn't include surrounding quotes.
Without quotes, it's not a string. I agree the manual is not worded as
clearly as it should be, but everywhere else in MySQL, NULL without quotes
means NULL, not a string. Why should here be any different.
INSERT INTO mytable VALUES
('NULL'),
(NULL);
The first is a string, the second is NULL.
The manual is quite clear that QUOTE expects a string as input. Strings
require quotes.
mysql> SELECT QUOTE(a string);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'string)' at line 1
mysql> SELECT QUOTE(string);
ERROR 1054 (42S22): Unknown column 'string' in 'field list'
Interestingly, MySQL will attempt to convert non-string values to strings so
that QUOTE can work on them.
mysql> SELECT QUOTE(2);
+----------+
| QUOTE(2) |
+----------+
| '2' |
+----------+
1 row in set (0.00 sec)
What string should NULL be converted to?
> QUOTE() is supposed to produce values similar to what you get with the
> DBI quote() function.
And if the value should be NULL? 'NULL' won't do, then. I believe
$dbh->quote(UNDEF) will return NULL, not 'NULL'.
Michael
