List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 11 2004 3:02am
Subject:Re: quote and null
View as plain text  
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.

-- 
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
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