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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
