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

window function count(*) and limit

From: Jesper Krogh <jesper(at)krogh(dot)cc>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: window function count(*) and limit
Date: 2010-10-23 16:25:33
Message-ID: 4CC30C7D.4020305@krogh.cc (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi.

I have been puzzled about the evaluation order when using window
functions and limit.

jk=# select * from testtable;
  id | value
----+-------
   1 |     1
   2 |     2
   3 |     3
   4 |     4
   5 |     5
   6 |     6
   7 |     7
   8 |     8
   9 |     9
  10 |    10
(10 rows)

jk=# select id,count(*) over () from testtable where id < 9 limit 3;
  id | count
----+-------
   1 |     8
   2 |     8
   3 |     8
(3 rows)


So the first element "id" is definately picked after the "limit 3", whereas
the window function is applied before. I have been digging in the
documentation but I didnt find this case specified out.

This behaviour may be correct, but it hugely surprises me...
I expected it to either count to 3 or blow up and tell me that count(*)
wasn't a window function.

It looks like something about the type of the function where
count(*) is a "agg" and row_number() is a "window". But shouldn't
count(*) exist as a type "window" and behave accordingly?

Same goes on for min() max() and other standard aggregates.

.. postgresql 8.4.4 (but couldn't find anyting mentioned in 8.4.5/9.0 
release notes about this).

Jesper

-- 
Jesper

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2010-10-23 16:42:49
Subject: Re: window function count(*) and limit
Previous:From: Tom LaneDate: 2010-10-23 16:12:44
Subject: Re: ask for review of MERGE

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