Thread
-
bug on aggregate function AVG()
Jose Soares <jose@sferacarta.com> — 1998-11-03T08:16:27Z
prova=> select * from a; int_2| int_4| int_8 -----+----------+------------------- 32767|2147483647|9223372036620802086 32767|2147483647|9223372036620802086 (2 rows) prova=> select avg(int_2), avg(int_4), avg(int_8) from a; avg|avg| avg ---+---+---------- -1| -1|-233973722 (1 row) Jose'
-
Re: bug on aggregate function AVG()
Jose Soares <jose@sferacarta.com> — 1998-11-03T08:21:05Z
Jose' Soares wrote: > > prova=> select * from a; > int_2| int_4| int_8 > -----+----------+------------------- > 32767|2147483647|9223372036620802086 > 32767|2147483647|9223372036620802086 > (2 rows) > > prova=> select avg(int_2), avg(int_4), avg(int_8) from a; > avg|avg| avg > ---+---+---------- > -1| -1|-233973722 > (1 row) > > Jose' The same on SUM(): prova=> select sum(int_2), sum(int_4), sum(int_8) from a; sum|sum| sum ---+---+---------- -2| -2|-467947444 (1 row) but it works as follow: prova=> select avg(int_2*1.0), avg(int_4*1.0), avg(int_8*1.0) from a; avg| avg|avg -----+----------+------------------- 32767|2147483647|9.2233720366208e+18 (1 row) prova=> select sum(int_2*1.0), sum(int_4*1.0), sum(int_8*1.0) from a; sum| sum|sum -----+----------+-------------------- 65534|4294967294|1.84467440732416e+19 (1 row) Jose'
-
Re: [HACKERS] Re: bug on aggregate function AVG()
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-11-04T06:44:55Z
> > prova=> select avg(int_2), avg(int_4), avg(int_8) from a; > The same on SUM(): Sure. For some reason, on most platforms integers are allowed to overflow in Postgres. Of course, both SUM() and AVG() take a running sum, and once they overflow you are hosed... - Tom -
Re[2]: [HACKERS] Re: bug on aggregate function AVG()
jose' soares <sferac@bo.nettuno.it> — 1998-11-04T15:00:39Z
Hi Tom, mercoledì, 4 novembre 98, you wrote: >> > prova=> select avg(int_2), avg(int_4), avg(int_8) from a; >> The same on SUM(): TGL> Sure. For some reason, on most platforms integers are allowed to TGL> overflow in Postgres. Of course, both SUM() and AVG() take a running TGL> sum, and once they overflow you are hosed... ^^^^^ I don't know what does the word "hosed" mean Tom, I hope you don't want to tell me there's no solution for this problem. I see that AVG() and SUM() uses an accumulator not enough big to hold the result of calculation, but the point is: should we consider this thing a "terrible" bug or an acceptable feature ? What about to convert every accumulator to float8 ? select intero4 from a; intero4 ---------- 2147483647 2147483647 2147483647 (3 rows) select sum(inter04),sum(intero4*1.0) from a; sum| sum ----------+---------- 2147483645|6442450941 (1 row) select avg(intero4),avg(intero4*1.0) from a; avg| avg ---------+---------- 715827881|2147483647 (1 row) Anyway I think we need to work a little bit on aggregates: MIN() and MAX() doesn't accept a string as parameter. SUM() and AVG() gives a wrong result because it goes on overflow. and none of them allows the clause DISTINCT. What do you think about ? ;) Jose' -
Re: [HACKERS] Re: bug on aggregate function AVG()
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-11-04T15:16:28Z
> I don't know what does the word "hosed" mean Tom, I hope you don't > want to tell me there's no solution for this problem. As you guessed, "hosed" isn't good ;) > I see that AVG() and SUM() uses an accumulator not enough big to hold > the result of calculation, but the point is: should we consider this > thing a "terrible" bug or an acceptable feature ? > What about to convert every accumulator to float8 ? imho we can't do that because we lose the exact qualities of integers. If you accumulate in float8, and if you take a sum over a very large table, you might start ignoring values. That is, if you have accumulated 15 or 16 digits worth of number, and then try adding 1 as the next number, the result will be the same as the input. With integers that is never the case, but we have to deal with overflows better. I would think we should start signalling overflows rather than silently overflowing, but I'm not sure what that entails. > Anyway I think we need to work a little bit on aggregates: > MIN() and MAX() doesn't accept a string as parameter. Yes, at the moment only numeric quantities are supported. > SUM() and AVG() gives a wrong result because it goes on overflow. > and none of them allows the clause DISTINCT. Yes, SELECT SUM(DISTINCT i) FROM t; is not yet supported. That's a project for v6.5. btw, I'm also planning on working on your "NULL problem" you mentioned earlier... - Tom -
Re: [HACKERS] Re: bug on aggregate function AVG()
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-12-08T06:40:27Z
> MIN() and MAX() doesn't accept a string as parameter. I've added capabilities to do min() and max() on strings. To help with this I've put the new implicit type coersion techniques into the aggregate function handling. So, for example, the same routine which handles min(text) will also handle min(char()) and min(varchar()). These changes are already in the main cvs tree, but are not in the v6.4.x branch since they require a small number of catalog changes to implement. Will post the patches on the patches list in case anyone needs them before v6.5 comes out... - Tom