Re: BUG #12462: NULLIF changes the argument type

"tneumann(at)users(dot)sourceforge(dot)net" <tneumann(at)users(dot)sourceforge(dot)net> wrote:

> The SQL standard in Section 6.11, Syntax rule 1 c) says that
>
> ""
> NULLIF (V1, V2) is equivalent to the following <case specification>:
> CASE WHEN
> V1=V2 THEN
> NULL ELSE V1
> END
> ""
>
> which is currently not the case in Postgres. Postgres promotes V1 to the
> type of V2, which can lead to behavior changes.
>
> Example query as illustration: It should produce 0,0,0 (and does on SQL
> Server and DB2), but PostgreSQL promotes the type and produces 0,0.5,0
>
> select 1/2,nullif(1,2.3)/2,case when 1=2.3 then NULL else 1 end/2

I agree that this fails to conform to the standard and should be
considered a bug. FWIW, Oracle, SQL Lite, and MySQL also return
matching values for the three columns in your sample query;
although Oracle and MySQL return 0.5,0.5,0.5 instead of 0,0,0.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company