List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 11 2004 4:06am
Subject:Re: quote and null
View as plain text  
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.
> 
Thread
quote and nullToro Hill10 Nov
  • Re: quote and nullEric Bergen11 Nov
  • Re: quote and nullPaul DuBois11 Nov
    • Re: quote and nullMichael Stassen11 Nov
    • Re: quote and nullPaul DuBois12 Nov
  • Re: quote and nullMichael Stassen11 Nov
    • Re: quote and nullPaul DuBois11 Nov
      • Re: quote and nullMichael Stassen11 Nov
        • Re: quote and nullPaul DuBois11 Nov
          • Re: quote and nullMichael Stassen11 Nov