On Thu, Jan 8, 2015 at 04:45:45PM +0000, Kevin Grittner wrote:
> "tneumann@users.sourceforge.net" <tneumann@users.sourceforge.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.
I researched this and it isn't the ordering that is the issue, but
rather the case where one type can represent all nullif() arguments. As
you can see the order does not matter:
test=> select pg_typeof(nullif(2.3, 1));
pg_typeof
-----------
numeric
(1 row)
test=> select pg_typeof(nullif(1, 2.3));
pg_typeof
-----------
numeric
(1 row)
Numeric is chosen because integer and numeric can be represented as
numeric. With mismatched types, the order also doesn't matter:
test=> select pg_typeof(nullif('a', 2.3));
ERROR: invalid input syntax for type numeric: "a"
LINE 1: select pg_typeof(nullif('a', 2.3));
^
test=> select pg_typeof(nullif(2.3, 'a'));
ERROR: invalid input syntax for type numeric: "a"
LINE 1: select pg_typeof(nullif(2.3, 'a'));
You can see that transformCaseExpr() calls select_common_type() to set
the CASE data type.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +