Bug #3756 ISNULL(QUOTE()) returns 1 for every row after 1st NULL
Submitted: 14 May 2004 6:17 Modified: 18 May 2004 11:29
Reporter: Michael Stassen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.17 OS:MacOS (Mac OS X)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[14 May 2004 6:17] Michael Stassen 
Description:
ISNULL(QUOTE(string)), QUOTE(string) IS NULL, and IFNULL(QUOTE(string), ... ) all behave as if string is 
NULL for all rows after the first row for which column string is null.

This was first reported on the mysql list by Toro Hill <http://lists.mysql.com/mysql/165405> for 
4.0.18, and repeated in my copy of 4.0.17.

The behavior seems similar to bug 3426, but that's for stored procedures in 5.0.  My apologies if this is 
a duplicate.

Note that QUOTE(string) <=> NULL works correctly.

How to repeat:
CREATE TEMPORARY TABLE nq (s CHAR(4));

INSERT INTO nq VALUES ('one'),('two'),(NULL),('four'),('five'),('six');

SELECT s, QUOTE(s), 
       ISNULL(QUOTE(s)) AS INQ,
       QUOTE(s) IS NULL AS QIN,
       IFNULL(QUOTE(s), 'NV') AS IFN
FROM nq;

+------+----------+-----+-----+-------+
| s    | QUOTE(s) | INQ | QIN | IFN   |
+------+----------+-----+-----+-------+
| one  | 'one'    |   0 |   0 | 'one' |
| two  | 'two'    |   0 |   0 | 'two' |
| NULL | NULL     |   1 |   1 | NV    |
| four | 'four'   |   1 |   1 | NV    |
| five | 'five'   |   1 |   1 | NV    |
| six  | 'six'    |   1 |   1 | NV    |
+------+----------+-----+-----+-------+
6 rows in set (0.00 sec)
[18 May 2004 8:49] Ramil Kalimullin 
ChangeSet
  1.1829 04/05/17 12:47:16 ram@gw.mysql.r18.ru +3 -0
  A fix (bug #3756: ISNULL(QUOTE()) returns 1 for every row after 1st NULL)
ChangeSet
  1.1830 04/05/17 13:40:02 ram@gw.mysql.r18.ru +1 -0
  added newline at the end of the file.
[18 May 2004 11:29] Ramil Kalimullin 
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html