MySQL Bugs: #11142: NULLIF() didn't work in where clause

Bug #11142	NULLIF() didn't work in where clause
Submitted:	7 Jun 2005 16:12	Modified:	24 Jun 2005 17:29
Reporter:	Miguel Solorzano	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1.XX BK Source	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
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?

How to repeat:
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;

select ID, B, NULLIF(B,'') from testcase where NULLIF(B,'') is NULL;

select ID, A, NULLIF(A,'') from testcase where NULLIF(A,'') is NULL;

select ID, A, NULLIF(A,'') from testcase;

select ID, C, NULLIF(C,'') from testcase where NULLIF(C,'') is NULL;

This bug was analyzed by Alexander Ivanov.
Here's his report:
The cause of the bug is incorrect behavior of the Item_func_nullif::is_null() function (file: sql\item_cmpfunc.cpp) --
if args[0] and args[1] are different the function simply returns 0 though in this case it must check the null_value of args[0].

Alexander also prepared a patch to fix this bug.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25944

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25945

ChangeSet
  1.2292 05/06/13 11:24:26 igor@igor-inspiron.creware.com +3 -0
  func_if.result, func_if.test:
    Added a test case for bug #11142.
  item_cmpfunc.cc:
    Fixed bug #11142.
    Implementation of Item_func_nullif::is_null was corrected.

(see also the correction in 1.2293)

The fix will appear in 4.1.13 and 5.0.8.

Documented in 5.0.8 and 4.1.13 changelogs:

<listitem><para>
     <literal>NULLIF()</literal> function could produce incorrect
     results if first argument is <literal>NULL</literal>. (Bug #11142)
    </para></listitem>