NULLIF() didn't work in where clause

Hello.

I'm sure if I discovered a real bug, but it seem so to me.

The manual said about NULLIF():

NULLIF(expr1,expr2)
    If expr1 = expr2 is true, return NULL else return expr1.

So when I do something like:

create table testcase (
  ID  int unsigned NOT NULL auto_increment,
  A   enum('Y','N') default NULL,
  B   enum('Y','N') default NULL,
  C   char(10),
  primary key (ID)
);

insert into testcase (A,B,C) values ('Y','N','Test1'), (NULL,'','Test2'), 
('','',NULL), ('N','Y', '');

select * from testcase;
+----+------+------+-------+
| ID | A    | B    | C     |
+----+------+------+-------+
|  1 | Y    | N    | Test1 |
|  2 | NULL |      | Test2 |
|  3 |      |      | NULL  |
|  4 | N    | Y    |       |
+----+------+------+-------+
4 rows in set (0.00 sec)

select ID, B, NULLIF(B,'') from testcase where NULLIF(B,'') is NULL;
+----+------+--------------+
| ID | B    | NULLIF(B,'') |
+----+------+--------------+
|  2 |      | NULL         |
|  3 |      | NULL         |
+----+------+--------------+
2 rows in set (0.00 sec)

This looks ok.

But:

select ID, A, NULLIF(A,'') from testcase where NULLIF(A,'') is NULL;
+----+------+--------------+
| ID | A    | NULLIF(A,'') |
+----+------+--------------+
|  3 |      | NULL         |
+----+------+--------------+
1 row in set (0.00 sec)

where is the row whit ID 2? It should be there, because NULLIF(A,'') should 
evaluate to NULL (A ist not equal '' so it returns A, which is NULL).

In the select part, NULLIF evaluates correct:

select ID, A, NULLIF(A,'') from testcase;
+----+------+--------------+
| ID | A    | NULLIF(A,'') |
+----+------+--------------+
|  1 | Y    | Y            |
|  2 | NULL | NULL         |
|  3 |      | NULL         |
|  4 | N    | N            |
+----+------+--------------+
4 rows in set (0.00 sec)

The same with a char field:

select ID, C, NULLIF(C,'') from testcase where NULLIF(C,'') is NULL;
+----+------+--------------+
| ID | C    | NULLIF(C,'') |
+----+------+--------------+
|  4 |      | NULL         |
+----+------+--------------+
1 row in set (0.00 sec)


I'm just updated to 4.1.12-Max which is the current stable, because the 
version 4.0.18-Max, which I used before, has a similar but inverse bug whith 
NULLIF. There only the rows where the values are NULL occur within the 
result, but not the empty ones.

So my question is: Is this really a bug or did I do anything wrong? Perhaps I 
missed something in the documentation?

Kind regards

	Rene


-- 
----------------------------------------------------------------------
                 ren:ux - internet-design & consulting
     mailto:[email protected]                     http://www.renux.de
----------------------------------------------------------------------



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]