Re: BUG #12462: NULLIF changes the argument type
От | David G. Johnston |
---|---|
Тема | Re: BUG #12462: NULLIF changes the argument type |
Дата | |
Msg-id | CAKFQuwazck37J7fA4pZOz8m9JsKMQQoPAftxRY0cA_n4R2xfbQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: BUG #12462: NULLIF changes the argument type (Bruce Momjian <bruce@momjian.us>) |
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления:
Следующее
От: jaime solerДата:
Сообщение: Re: BUG #12912: pgAdmin III : set fonts preferences makes pgAdmin to crash