Thread: Aggregate Function (AVG) not calculated correctly

Gavin Evans (gavin@consultant.com) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Aggregate Function (AVG) not calculated correctly

Long Description
Using the following code:
SELECT airline_code, AVG(distance) FROM flights GROUP BY airline_code;

The first line of the result set (AI           | -2864) is calculated wrongly.

I have included all code needed to prove the bug, this is a SERIOUS BUG!

G

Sample Code
eg3ab=# select * from flights;
 flight_number | aircraft | distance | airline_code | origin | destination
---------------+----------+----------+--------------+--------+-------------
 AI434         | 310      |     3417 | AI           | BOM    | PER
 AI306         | 747      |     1724 | AI           | BOM    | BKK
 AI410         | 310      |     2012 | AI           | BOM    | KUL
 AI158         | 740      |     2720 | AI           | FRA    | BOM
 AI112         | 744      |     9632 | AI           | JFK    | HKG
 CX254         | 744      |     8736 | CX           | LHR    | HKG
 VS200         | 340      |     8736 | VS           | LHR    | HKG
 VS501         | 744      |    14737 | VS           | LHR    | SYD
 BA009         | 744      |    12882 | BA           | LHR    | SYD
 QF10          | AB3      |    13368 | QF           | LHR    | SYD
 GA881         | M11      |     8035 | GA           | LGW    | BKK
 PR731         | 74E      |     9148 | PR           | LGW    | BKK
 TG915         | 744      |    10905 | TG           | LHR    | BKK
 TG911         | 744      |     9422 | TG           | LHR    | SIN
 BA011         | 744      |     9422 | BA           | LHR    | SIN
 AI180         | 747      |     6422 | AI           | LHR    | SIN
 QF12          | AB3      |     1839 | QF           | MEL    | PER
 BA014         | 767      |      321 | BA           | LHR    | FRA
 AI200         | 747      |     3705 | AI           | JFK    | FRA
 AI181         | 747      |     6422 | AI           | SIN    | LHR
 AI201         | 747      |     3705 | AI           | FRA    | JFK
(21 rows)

eg3ab=# \i ex13.query
 airline_code |  avg
--------------+-------
 AI           | -2864
 BA           |  7541
 CX           |  8736
 GA           |  8035
 PR           |  9148
 QF           |  7603
 TG           | 10163
 VS           | 11736
(8 rows)

No file was uploaded with this report

pgsql-bugs@postgresql.org wrote:
>
> Gavin Evans (gavin@consultant.com) reports a bug with a severity of 1
> The lower the number the more severe it is.
>
> Short Description
> Aggregate Function (AVG) not calculated correctly
>
> Long Description
> Using the following code:
> SELECT airline_code, AVG(distance) FROM flights GROUP BY airline_code;
>
> The first line of the result set (AI           | -2864) is calculated wrongly.
> I have included all code needed to prove the bug, this is a SERIOUS BUG!

You have not included all information required to understand the
problem. In particular, what is the schema? What version of PostgreSQL
are you using? What kind of machine?

Do you happen to be using int2 for the distance fields? In that case you
are likely (silently) overflowing your calculation.

Please post the schema and the data in such a way that we can read it
in! I'd suggest using pg_dump on the relevant table, but other
techniques can be used.

Regards.

                     - Thomas

pgsql-bugs@postgresql.org writes:
> I have included all code needed to prove the bug,

No, you haven't: where are the table declarations?

But I'll take a guess anyway: you declared "distance" as int2, didn't
you?  AVG(int2) currently uses an int2 accumulator, and you're suffering
overflow.  Try "AVG(distance::float8)" instead.

We've changed AVG() to use a numeric accumulator for 7.1, but that
won't help you today.

            regards, tom lane