Re: NULLIF() didn't work in where clause Temp.sol.

Confirmed!
Same problem here
В· Server: 4.0.24-standard-log
В· Client: 3.23.52
В· Protocol-Version: 10
  I can see as temporary solution/fix only this:
Try
   where NULLIF(A,'') <=> NULL
instead of
   where NULLIF(A,'') IS NULL

P.S. Seems if IS NULL skipped it show correctly the other two rows yet 
if IS NOT NULL is the culprit it show 3 rows so... 1 row must be both 
NULL and NOT NULL at the same time :-)
Hope to see this fixed soon...

select * from testcase where NULLIF(A,'') IS NULL;
ID=3
select * from testcase where NULLIF(A,'') IS NOT NULL;
ID=1,2,4
select * from testcase where NULLIF(A,'');
ID=1,4

select * from testcase where NULLIF(A,'')<=>NULL;
ID=2,3 /* as expected yet this is mysql specific I think ... */
Rene Fertig wrote:
> 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
> 
> 

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