Обсуждение: Weird type selection choice

Поиск
Список
Период
Сортировка

Weird type selection choice

От
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.  Clearly, the numeric variant should be preferred anyway.  
What's wrong here?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: Weird type selection choice

От
Bernd Helmle
Дата:
--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


Re: Weird type selection choice

От
Peter Eisentraut
Дата:
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/


Re: Weird type selection choice

От
Tom Lane
Дата:
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


Re: Weird type selection choice

От
Peter Eisentraut
Дата:
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/


Re: Weird type selection choice

От
Gregory Stark
Дата:
"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!


Re: Weird type selection choice

От
Peter Eisentraut
Дата:
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/


Re: Weird type selection choice

От
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?

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com


Re: Weird type selection choice

От
Peter Eisentraut
Дата:
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/


Re: Weird type selection choice

От
Tom Lane
Дата:
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


Re: Weird type selection choice

От
Alvaro Herrera
Дата:
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)