Skip site navigation (1) Skip section navigation (2)

BUG #1188: evaluation order of select seems to be wrong

From: "PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1188: evaluation order of select seems to be wrong
Date: 2004-07-07 12:58:51
Message-ID: 20040707125851.31357CF4983@www.postgresql.com (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1188
Logged by:          Holger Jakobs

Email address:      holger(at)jakobs(dot)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.



Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2004-07-07 16:03:20
Subject: Re: BUG #1188: evaluation order of select seems to be wrong
Previous:From: Dario V. FassiDate: 2004-07-07 06:31:25
Subject: ResultSerMetaData.getColumnDisplaySize() with timestamp error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group