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
