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

BUG #6440: Window function in WHERE evaluated after agregate

From: logik(at)centrum(dot)cz
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6440: Window function in WHERE evaluated after agregate
Date: 2012-02-08 13:50:40
Message-ID: E1Rv7uy-0002pB-Vf@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6440
Logged by:          Matyas Novak
Email address:      logik(at)centrum(dot)cz
PostgreSQL version: 9.1.1
Operating system:   Linux
Description:        

Window function in WHERE clause - as all other functions used in where -
should be evaluated before agregating takes place. But it seems that
postgresql try evaluate its after agregating, as it'd be in HAVING clause.

E.g. in folowing example I'l try to sum the best results of given persons
from all dispciplines - so I agregate over persons and best result in each
discipline wann get using window function.

(I'm aware that it can be done by various subselects, or that there may be
better to use different window function or nested agregates, but it's
simplified example from a bit complex example.)

Test example:

create table results
(
 id serial not null primary key,
 person integer,
 discipline integer,
 result integer
);

select person, sum(result) from
  results 
where
  row_number() over (partition by person, discipline order by result desc) =
1
group by
  person

Error:
ERROR:  column "results.result" must appear in the GROUP BY clause or be
used in an aggregate function
LINE 19: ...() over (partition by person, discipline order by result des...


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-02-08 16:52:21
Subject: Re: BUG #6440: Window function in WHERE evaluated after agregate
Previous:From: vipinassabuDate: 2012-02-08 10:21:51
Subject: BUG #6439: problems with streaming replication

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