List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:November 11 2004 2:37am
Subject:Re: quote and null
View as plain text  

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
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