NULLIF() didn"t work in where clause

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


--
------------------------------------------------------------ ----------
ren:ux - internet-design & consulting
mailto:rene@renux.de http://www.renux.de
------------------------------------------------------------ ----------



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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/bugs?unsub=gcdmb-bugs@m.gmane.org

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/bugs?unsub=gcdmb-bugs@m.gmane.org

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

Hi Remo.

On Saturday 28 May 2005 07:05, Remo Tex wrote:
> I can see as temporary solution/fix only this:
> Try
> where NULLIF(A,'') <=> NULL
> instead of
> where NULLIF(A,'') IS NULL

This works for me, too. Thanks!

Nevertheless I will open a bug report.

Bye, Rene



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

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

Hi Remo.

On Saturday 28 May 2005 07:17, Remo Tex wrote:
> 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...
> ''=3Dnot valid ot not found in set element and it probably get assigned
> some INT val by which search is done That's why ''=3DN <> '' empty string
> so seems that is correct behavior in *your* case where .. IS NULL :-)
> but not in mine WHERE ... <=3D> NULL :(

This sounds right but I wonder why the same happens to a char-value!

> Would some guru shed some light here please...

Yes, that would be great.

Bye, Ren=E9


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: NULLIF() didn"t work in where clause

At 12:34 27/5/2005, Rene Fertig wrote:
Hello,

I opened the below bug report for this thread:

http://bugs.mysql.com/bug.php?id=3D11142


Regards,

Miguel Angel Sol=F3rzano
Florianopolis Santa Catarina - Brazil



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: NULLIF() didn"t work in where clause

Hi Miguel.

Am Dienstag, 7. Juni 2005 18:13 schrieb Miguel Angel Solorzano:
>
> I opened the below bug report for this thread:
>
> http://bugs.mysql.com/bug.php?id=11142

Thank you very much, I was on vacation and forgot to do it myself.

Bye, Rene


--
------------------------------------------------------------ ----------
ren:ux - internet-design & consulting
mailto:rene@renux.de http://www.renux.de
------------------------------------------------------------ ----------


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org