Description:
I'll just paste in the relevant email message:
Date: Tue, 3 Feb 2004 11:51:12 +0100
From: Sergei Golubchik
To: Paul DuBois
Subject: Re: NULLIF() doesn't behave as described in manual
Hi!
On Jan 17, Paul DuBois wrote:
> At 19:18 +0100 1/17/04, Sergei Golubchik wrote:
> >Hi!
> >
> >On Jan 15, Paul DuBois wrote:
> >> The manual says:
> >>
> >> >`NULLIF(expr1,expr2)'
> >> > If `expr1 = expr2' is true, return `NULL' else return `expr1'.
> >> > This is the same as `CASE WHEN x = y THEN NULL ELSE x END':
> >>
> >> In fact, NULLIF() returns NULL if either or both arguments are NULL:
> >>
> >> mysql> SELECT NULLIF(NULL,NULL), NULLIF(NULL,1), NULLIF(1,NULL);
> >> +-------------------+----------------+----------------+
> >> | NULLIF(NULL,NULL) | NULLIF(NULL,1) | NULLIF(1,NULL) |
> >> +-------------------+----------------+----------------+
> >> | NULL | NULL | NULL |
> >> +-------------------+----------------+----------------+
> >>
> >> The third expression does not match the manual. The arguments are
> >> not equal, so NULLIF() should return the first one (1). It does
> >> not; it returns NULL.
> >>
> >Yes, according to the code NULLIF returns NULL if at least one argument
> >is NULL. It is consistent with the "UNKNOWN" meaning of NULL, but it
> >does contradict the description in the manual.
>
> What should I do about this?
>
> Consider it a bug and file a bug report?
> Or just update the manual to point out that the function returns NULL if
> any of the arguments are NULL?
Bugreport.
I asked Monty, and he said it's a compatibility feature, and in "other
DBMS'es" it would return 1 for NULLIF(1,NULL).
Regards,
Sergei
How to repeat:
See above.