| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 4.1.XX BK Source | OS: | Linux (Linux) |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[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>
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;