Weird type selection choice

--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, then the 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)