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

I think its because of.. ENUM : Manual says valid entires are (in your 
case) 'Y','N', and undefined but still there: NULL, ''
it enumerates elements in set in some manner and assigns them ?integer? 
values (position in set) and then does search by int so...
''=not valid ot not found in set element and it probably get assigned 
some INT val by which search is done That's why ''=N <> '' empty string
so seems that is correct behavior in *your* case where .. IS NULL :-)
but not in mine WHERE ... <=> NULL :(
Would some guru shed some light here please...

I wonder what will happen if you explicitly put '' in your enum('', 'N', 
'Y') :-))
or if I put NULL in mine :-) enum(NULL, '', 'N', 'Y')
Yep that's a little offtopic!

Remo Tex wrote:
> 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]