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