Thread: Weird type selection choice
I noticed this problem in 8.2 and 8.3: pei=# select mod( trunc( 1 ), 2 ); ERROR: 42883: function mod(double precision, integer) does not exist LINE 1: select mod( trunc( 1 ), 2 ); ^ It apparently casts the 1 to double precision to pick the variant trunc(dp)=>dp instead of trunc(numeric)=>numeric. I was under the impression that we didn't want to cast integers to float types implicitly because this loses information. Clearly, the numeric variant should be preferred anyway. What's wrong here? -- Peter Eisentraut http://developer.postgresql.org/~petere/
--On Dienstag, November 06, 2007 16:31:05 +0100 Peter Eisentraut
<peter_e@gmx.net> wrote:
> I noticed this problem in 8.2 and 8.3:
>
> pei=# select mod( trunc( 1 ), 2 );
> ERROR: 42883: function mod(double precision, integer) does not exist
> LINE 1: select mod( trunc( 1 ), 2 );
> ^
>
> It apparently casts the 1 to double precision to pick the variant
> trunc(dp)=>dp instead of trunc(numeric)=>numeric. I was under the
> impression that we didn't want to cast integers to float types
> implicitly because this loses information. Clearly, the numeric variant
> should be preferred anyway. What's wrong here?
Indeed, if i go and make the implicit cast from int4 to float8 explicit or
implicit on assignment it's going to work:
bernd@localhost:bernd #= UPDATE pg_cast SET castcontext = 'e' WHERE
castsource = 23 AND casttarget = 701;
UPDATE 1
Time: 7,320 ms
bernd@localhost:bernd #= select mod( trunc( 1 ), 2 );mod
----- 1
(1 row)
-- Thanks
Bernd
Am Dienstag, 6. November 2007 schrieb Peter Eisentraut: > I noticed this problem in 8.2 and 8.3: > > pei=# select mod( trunc( 1 ), 2 ); > ERROR: 42883: function mod(double precision, integer) does not exist > LINE 1: select mod( trunc( 1 ), 2 ); > ^ > > It apparently casts the 1 to double precision to pick the variant > trunc(dp)=>dp instead of trunc(numeric)=>numeric. I was under the > impression that we didn't want to cast integers to float types implicitly > because this loses information. Well, duh, of course we can't disallow casting integer to float. But can we make it prefer numeric as a target if available? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> It apparently casts the 1 to double precision to pick the variant
> trunc(dp)=>dp instead of trunc(numeric)=>numeric. I was under the impression
> that we didn't want to cast integers to float types implicitly because this
> loses information. Clearly, the numeric variant should be preferred anyway.
There's nothing "clear" about that at all. float8 is the preferred type
in the numeric category, so preferring trunc(dp) over trunc(numeric) is
exactly what I'd expect to happen. This is not something that can be
readily changed, because if we made numeric the preferred type we'd be
violating the SQL spec. The result of, for example, float8 + numeric
has to be float8:
2) If the declared type of either operand of a dyadic arithmetic operator is approximate numeric,
thenthe declared type of the result is approximate numeric.
regards, tom lane
Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > It apparently casts the 1 to double precision to pick the variant > > trunc(dp)=>dp instead of trunc(numeric)=>numeric. I was under the > > impression that we didn't want to cast integers to float types > > implicitly because this loses information. Clearly, the numeric > > variant should be preferred anyway. > > There's nothing "clear" about that at all. The clarity stems from the fact that this is the variant that doesn't lose data whereas the other one does. The expression I originally posted works on Oracle. I wonder how they do it. -- Peter Eisentraut http://developer.postgresql.org/~petere/
"Peter Eisentraut" <peter_e@gmx.net> writes: > The clarity stems from the fact that this is the variant that doesn't > lose data whereas the other one does. I think double has a wider range. So you get a choice between losing precision or not being able to store all values. > The expression I originally posted works on Oracle. I wonder how they > do it. I think they only have one type which uses different storage formats depending on the data. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
I wrote: > I noticed this problem in 8.2 and 8.3: > > pei=# select mod( trunc( 1 ), 2 ); > ERROR: 42883: function mod(double precision, integer) does not exist > LINE 1: select mod( trunc( 1 ), 2 ); > ^ I suppose there will be little interest in including the obvious solution, namely CREATE FUNCTION pg_catalog.trunc(int) RETURNS int AS $$ SELECT $1; $$ LANGUAGE SQL STRICT IMMUTABLE; into PostgreSQL. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > I wrote: >> I noticed this problem in 8.2 and 8.3: >> >> pei=# select mod( trunc( 1 ), 2 ); >> ERROR: 42883: function mod(double precision, integer) does not exist >> LINE 1: select mod( trunc( 1 ), 2 ); >> ^ > > I suppose there will be little interest in including the obvious solution, > namely > > CREATE FUNCTION pg_catalog.trunc(int) RETURNS int AS $$ SELECT $1; $$ LANGUAGE > SQL STRICT IMMUTABLE; It does sound totally useless... Why would you run a query like that in the first place? It seems like a useless query as it is. Is there a bigger story behind it? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Am Mittwoch, 7. November 2007 schrieb Heikki Linnakangas: > Peter Eisentraut wrote: > > I wrote: > >> I noticed this problem in 8.2 and 8.3: > >> > >> pei=# select mod( trunc( 1 ), 2 ); > >> ERROR: 42883: function mod(double precision, integer) does not exist > >> LINE 1: select mod( trunc( 1 ), 2 ); > >> ^ > > > > I suppose there will be little interest in including the obvious > > solution, namely > > > > CREATE FUNCTION pg_catalog.trunc(int) RETURNS int AS $$ SELECT $1; $$ > > LANGUAGE SQL STRICT IMMUTABLE; > > It does sound totally useless... > > Why would you run a query like that in the first place? It seems like a > useless query as it is. Is there a bigger story behind it? The "1" is substituted from somewhere else. If the value happens to be, say, 1.5, it works, but not with 1 or 2. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Mittwoch, 7. November 2007 schrieb Heikki Linnakangas:
>> Why would you run a query like that in the first place? It seems like a
>> useless query as it is. Is there a bigger story behind it?
> The "1" is substituted from somewhere else.
Seems like textual substitution is not the optimal approach for such a
thing anyway --- why aren't they using a parameter? This is hardly the
only gotcha, as an unadorned numeric literal might be taken as either
int, bigint, or numeric depending on its value. I am sure there are
contexts in which a bigint might cause some surprising choices.
If they really want to stick with textual substitution, an explicit cast
inserted into the query seems the safest bet.
regards, tom lane
Peter Eisentraut wrote: > Am Mittwoch, 7. November 2007 schrieb Heikki Linnakangas: > > Peter Eisentraut wrote: > > > I wrote: > > >> I noticed this problem in 8.2 and 8.3: > > >> > > >> pei=# select mod( trunc( 1 ), 2 ); > > >> ERROR: 42883: function mod(double precision, integer) does not exist > > >> LINE 1: select mod( trunc( 1 ), 2 ); > > >> ^ > > > > > > I suppose there will be little interest in including the obvious > > > solution, namely > > > > > > CREATE FUNCTION pg_catalog.trunc(int) RETURNS int AS $$ SELECT $1; $$ > > > LANGUAGE SQL STRICT IMMUTABLE; > > > > It does sound totally useless... > > > > Why would you run a query like that in the first place? It seems like a > > useless query as it is. Is there a bigger story behind it? > > The "1" is substituted from somewhere else. If the value happens to be, say, > 1.5, it works, but not with 1 or 2. Maybe as a workaround these are useful: alvherre=# select mod( trunc( 1.0 ), 2 );mod ----- 1 (1 fila) alvherre=# select mod( trunc( 1::numeric ), 2 );mod ----- 1 (1 fila) -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1", W 73º 13' 56.4" Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'. After collecting 500 such letters, he mused, a university somewhere in Arizona would probably grant him a degree. (Don Knuth)