Обсуждение: Bug or Feature ?
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
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
> > 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
> > 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
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