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

Re: BUG #6440: Window function in WHERE evaluated after agregate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: logik(at)centrum(dot)cz
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6440: Window function in WHERE evaluated after agregate
Date: 2012-02-08 16:52:21
Message-ID: 27361.1328719941@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
logik(at)centrum(dot)cz writes:
> Window function in WHERE clause - as all other functions used in where -
> should be evaluated before agregating takes place.

Hmm?  WHERE clauses are necessarily evaluated before aggregating; that's
one of the main reasons why there's a difference between WHERE and
HAVING in the first place.  Read the SQL standard, or any book about SQL.

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

The real reason this query isn't allowed can be found in SQL:2008
section 4.15.3 "Window functions":

	Window functions may only appear in the <select list> of a <query
	specification> or <select statement: single row>, or the <order by
	clause> simply contained in a <query expression> that is a
	simple table query.

I agree that the error message could be improved --- it'd be better if
it complained that you can't put a window function call there.  You
would eventually get "window functions not allowed in WHERE clause",
but the other check is being made first.

As far as fixing your problem goes, maybe you should put the aggregate,
GROUP BY, and row_number() calls into a sub-select and put the WHERE in
the upper level.  Or consider using LIMIT, which is going to be a lot
more efficient than this row_number() = 1 locution anyway.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: petr.jedinyDate: 2012-02-08 19:01:32
Subject: BUG #6444: Postgresql crash
Previous:From: logikDate: 2012-02-08 13:50:40
Subject: BUG #6440: Window function in WHERE evaluated after agregate

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