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

[7 Jun 2005 16:12] Miguel Solorzano
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;
[13 Jun 2005 18:20] Igor Babaev
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.
[13 Jun 2005 18:24] Bugs System
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
[13 Jun 2005 18:38] Bugs System
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
[15 Jun 2005 10:38] Igor Babaev
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.
[24 Jun 2005 17:29] Mike Hillyer
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>