Postgres Pro
Downloads
Home   >   mailing lists

Thread: Bug or Feature ?

Bug or Feature ?

From
Norbert Meissner
Date:
Hi all,

i found a strange behaviour in Postgresql 7.0 on FreeBSD 4.0

$ createdb foo
$psql foo
foo=# create table number (a_number int);
foo=# insert into number values(1500000000);
foo=# insert into number values(1600000000);
select avg(a_number) from number;
    avg
------------
 -597483648

This seems like an overflow, but i think that this shouldn't happen on a
database because one can think "ok, there is enough space in the type,
let's average this rows" and gets under some circumstances an average
value that looks like the right value, but it is not. It's just a matter
how many rows one have...

Norbert

Re: Bug or Feature ?

From
Karel Zak
Date:
On Fri, 26 May 2000, Norbert Meissner wrote:

> Hi all,
>
> i found a strange behaviour in Postgresql 7.0 on FreeBSD 4.0
>
> $ createdb foo
> $psql foo
> foo=# create table number (a_number int);
> foo=# insert into number values(1500000000);
> foo=# insert into number values(1600000000);
> select avg(a_number) from number;
>     avg
> ------------
>  -597483648
>


test=# select avg(a_number::int8) from number;
    avg
------------
 1550000000
(1 row)

 The parser select function from column type. You use 'int' --- the parser
use avg(int4)....
                        Karel

Re: Bug or Feature ?

From
Norbert Meissner
Date:

> > Hi all,
> >
> > i found a strange behaviour in Postgresql 7.0 on FreeBSD 4.0
> >
> > $ createdb foo
> > $psql foo
> > foo=# create table number (a_number int);
> > foo=# insert into number values(1500000000);
> > foo=# insert into number values(1600000000);
> > select avg(a_number) from number;
> >     avg
> > ------------
> >  -597483648
> >
>
> test=# select avg(a_number::int8) from number;
>     avg
> ------------
>  1550000000
> (1 row)
>
>  The parser select function from column type. You use 'int' --- the parser
> use avg(int4)....
>                                                 Karel

Dear Karel,

thank you for your solution. You can do it this way, but if you have really
big numbers or many rows, you cannot be sure, at which point you have to use
your solution or you have to use it every time, just to be sure.

I was thinking about a solution that doesn't cause an overflow. Please
consider this situation: At the time you will start the query, you will know
how many rows are affected. So you can calculate the average using the formula

row_1 / number_of_rows + row_2 / number_of_rows + ...

you will have to use floats for this calculation and i'm not sure on the
effect of processing time, but you will never get an overflow.

Norbert

Re: Bug or Feature ?

From
Karel Zak
Date:
>
> I was thinking about a solution that doesn't cause an overflow. Please

 Use 'numeric' datetype. It never overflow and allow you to use deciamal
numbers, but is slowly and is needs more memory.

                            Karel

Re: Bug or Feature ?

From
Norbert Meissner
Date:
Dear Karel,

thank you for answers to my "minor" problems. It seems, that I have to take
some care on designing tables for speed or robustness. I found out that the
int8 type needs also much more time on calculations. I haven't tried it with
numeric, but I think the result is even more worse.


Norbert


Norbert

Karel Zak schrieb:

> >
> > I was thinking about a solution that doesn't cause an overflow. Please
>
>  Use 'numeric' datetype. It never overflow and allow you to use deciamal
> numbers, but is slowly and is needs more memory.
>
>                                                         Karel