The following bug has been logged online:
Bug reference: 1188
Logged by: Holger Jakobs
Email address: holger@jakobs.com
PostgreSQL version: 7.4
Operating system: Linux
Description: evaluation order of select seems to be wrong
Details:
There is a table like
create table games (
teamnr serial,
player integer not null,
win integer not null,
lose integer not null
);
I have the following select statement:
select teamnr, sum(win)/sum(lose)
from games
group by teamnr
having sum(lose) > 0;
According to what I know about select, the expressions of the from clause
have to be evaluated last, so that the case that sum(lose) is zero will be
filtered _before_ the division by 0 takes place.
Unfortunately, PostgreSQL 7.4.2 gives a "division by zero" error if there
are teams which have not yet won any game. Those teams should just not
appear in the output.
Example insert statements:
insert into games values (1, 11, 3, 0);
insert into games values (1, 12, 5, 2);
insert into games values (2, 21, 6, 0);
Without the last insert everything works fine. Adding the last insert
produces a team with zero numbers of games won leading to the error.
Btw, Oracle 8 handels this correctly.