Re: [HACKERS] Re: [BUGS] agregate function sum error

On Fri, 20 Feb 1998, Thomas G. Lockhart wrote:

> > > > > The limits are the same as for Postgres. The difference is that Postgres throws
> > > > > an error on floating overflows, rather than returning "infinity".
> > > >
> > > > I think overflows on float8 is OK, the problem is about overflows on int4,
> > > > int2 and float4.
>
> What would it take to have backends start signalling ERROR (not core dump :) on integer
> overflows? It would be nice to have the compile-time option; perhaps it is already
> somewhere in the backend.
>
> The SQL92 standard requires an abort on floating overflows, though _just_ because the
> standard says it doesn't mean we have to do it. We should do what makes sense. However,
> once an IEEE value becomes "Inf" further math operations keep it at "Inf", which
> propagates the problem farther into your calculation. Probably better to signal the
> error and let the user fix the overflow problem first. However, if you want to generate
> compile-time patches...
>
> > > > If PostgreSQL does all calculation using float8 (even on int2, int4 and float4)
> > > > we have acceptable overflows. Why we don't do this in this way ?
> > > Can you imagine the problems we would have? Integral types and floats
> > > are two different things. They behave differently in division,
> > > rounding, and overflows. For those reasons, and performance, we can't
> > > just use floats all the time.
> > >
> > I see..., then what we need is int8 as Tom said, to avoid to write code as:
> >
> > select cast mysmall as int4 * cast mysmall as int4 from mytable;
> > select mysmall::int4 * mysmall::int4 from mytable;
> > select int4(mysmall) * int4(mysmall) from mytable;
> > select sum(int4(mysmall)) from mytable;
> >
> > that has nothing to do with SQL standard, and isn't too friendly,
> > those conversions should be done automaticly by PostgreSQL whitout
> > user's help as MySQL, Solid and other databases do.
>
> Well, not all real databases do this, though I see your point. There is a performance
> tradeoff between always promoting types when doing arithmetic and leaving the type
> consistant. Also, "hidden" promotions may be less intuitive for some users, and lead to
> unexpected behavior elsewhere.
>
Hi Tom,
Apologies for my insistence and for my bad english, I like to watch things
from point of view of end users.
(are databases for end users or programmers ??)
We programmers, often forget that end users don't want to know what's happening
behind their own screen, they don't want write ostrogothics sentences ala C
to query a database, they had learned SQL whith so much effort and now they
want to query database without thought about dificulties, that C find when
it need convert numbers from one type to another.
I thing that one of the bigest dificulty of PostgreSQL is how it treats numbers.
SQL standard says that a number is comparable to, and compatible with, all
other numbers (that is: all numbers are mutually comparable and mutually
assignable). I think it means directly, without force it with the CAST or
functions.
I can't imagine end users using things like this:
UPDATE my_table SET my_small=int2(my_int4);
SELECT SUM(float8(my_int) FROM my_table;
SELECT mysmall::int4 * mysmall::int4 FROM my_table;

End users hate uneccessaries or non sense messages like the following
when he/she writes a sentence like this:

UPDATE my_table SET my_int=my_small;

and PostgreSQL show him/her this message ?
---------------------------------------------------------------------
WARN: type of my_small does not match target column my_int
---------------------------------------------------------------------
- What message is this ? If PostgreSQL understand that I want to do:
UPDATE my_table SET my_int=my_small::int4;
why it doesn't do that for me?

SELECT my_small * my_float FROM my_table;
---------------------------------------------------------------------
NOTICE:there is no operator * for types int2 and float8
NOTICE:You will either have to retype this query using an
NOTICE:explicit cast, or you will have to define the operator
WARN:* for int2 and float8 using CREATE OPERATOR
---------------------------------------------------------------------

- Well, if PostgreSQL know what I want why it doesn't do it for me ?
I know that conversion is more expensive than warnning but it's more
friendly. What do you think? ;-)
Regards, Jose'