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 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
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 correctlyLong 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