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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
