Обсуждение: nullif BUG???

I try

> select nullif(NULL,5)

+--------+
| case   |
+--------+
| <Null> |
+--------+



--

Guard,

> > select nullif(NULL,5)
>
> +--------+
> | case   |
> +--------+
> | <Null> |
> +--------+
>

Er... what were you expecting, exactly?

Except for IS NULL (and COALESCE, which uses IS NULL) any operation
involving a NULL is also NULL.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

"Josh Berkus" <josh@agliodbs.com> writes:
> Er... what were you expecting, exactly?

AFAICT, the quoted behavior is correct per the defined behavior of
nullif(), cf 
http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/functions-conditional.html
NULLIF(value1, value2)
The NULLIF function returns NULL if and only if value1 andvalue2 are equal. Otherwise it returns value1.

> Except for IS NULL (and COALESCE, which uses IS NULL) any operation
> involving a NULL is also NULL.

Well, that's not quite the correct reasoning.

NULLIF and COALESCE are both shorthands for CASE expressions, and hence
are capable of returning non-NULL for a NULL input.  It all depends on
how the CASE tests are phrased.  NULLIF is essentiallyCASE WHEN value1 = value2 THEN NULL ELSE value1 END
In the quoted example, "NULL = 5" will yield NULL, which is interpreted
as a FALSE case test, so you get the ELSE case, ie value1, ie NULL.
        regards, tom lane