Thread: BUG #12462: NULLIF changes the argument type
The following bug has been logged on the website: Bug reference: 12462 Logged by: Thomas Neumann Email address: tneumann@users.sourceforge.net PostgreSQL version: 9.4.0 Operating system: Linux 3.16.0 Description: 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
"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. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
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. +
On Wednesday, April 1, 2015, Bruce Momjian <bruce@momjian.us> wrote:
> On Thu, Jan 8, 2015 at 04:45:45PM +0000, Kevin Grittner wrote:
> > "tneumann@users.sourceforge.net <javascript:;>" <
> tneumann@users.sourceforge.net <javascript:;>> wrote:
> >
> > > The SQL standard in Section 6.11, Syntax rule 1 c) says that
I'm curious...what does the standard have to say about implicit type
casting and checking equality between two different types?
> > >
> > > ""
> > > 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
Except your case needs to be "when 1.0=2.3 then" in which case columns two
and three do give the same results.
> >
> > 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.
>
>
Polymorphism and implicit casting have their advantages but it does mean
that you are letting the system make decisions for you if you supply
not-equivalent data types. There is no way for the example call to keep
the 2.3 as numeric and also return an integer since a polymorphic function
requires all pseudo type arguments to take on the same actual runtime
type. Breaking that seems like it would be non-trivial.
If you absolutely need this case to work then, in theory, you could write a
"nullif(integer, numeric) returns integer" function that could convert the
integer to numeric for comparison purposes while still returning the
integer to the caller.
This could be fixed in core if there was a psuedo type that can only be
used in the parameter list and requires at least one other of the existing
psuedo types to be present. The existing behavior would apply to all the
current types but this new type would take whatever type it's argument
possesses - in this case numeric - and so the anyelement type would take on
integer and cause the function to return integer. Neither argument would
require casting to match the function signature. The body of the function
would cast the integer to perform the equals comparison but would still
return an integer (either the first argument or a typed null).
David J.