Ah, I see now. That makes sense. Sorry to have been so dense.
Michael
Paul DuBois wrote:
> At 21:37 -0500 11/10/04, Michael Stassen wrote:
>
>> 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.
>
>
> You're overthinking it. Strings don't need quotes unless you're writing
> them as string literals. The quotes aren't _part of the string_.
>
> QUOTE() is intended for generating string values to be used for
> constructing SQL statements. Consider the following sequence of
> statements:
>
> SET @a = 'abc', @b = 'def';
> SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b),
> ');');
> SELECT @stmt;
> SET @a = 'abc', @b = NULL;
> SET @stmt = CONCAT('INSERT INTO t VALUES(', QUOTE(@a), ',', QUOTE(@b),
> ');');
> SELECT @stmt;
>
> The intended result is:
>
> +------------------------------------+
> | @stmt |
> +------------------------------------+
> | INSERT INTO t VALUES('abc','def'); |
> +------------------------------------+
> +-----------------------------------+
> | @stmt |
> +-----------------------------------+
> | INSERT INTO t VALUES('abc',NULL); |
> +-----------------------------------+
>
> That only works if QUOTE(NULL) returns the word NULL without quotes.
> That's why it's a bug for it to actually return a NULL value. The
> actual result from the preceding statements is:
>
> +------------------------------------+
> | @stmt |
> +------------------------------------+
> | INSERT INTO t VALUES('abc','def'); |
> +------------------------------------+
> +-------+
> | @stmt |
> +-------+
> | NULL |
> +-------+
>
>
>>
>> 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.
>
>
> String literals do.
>
>>
>> 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?
>
>
> The string consisting of the four characters N U L L.
>
>>
>>> 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'.
>
>
> It returns a string consisting of the four characters N U L L.
>
