window function count(*) and limit
От | Jesper Krogh |
---|---|
Тема | window function count(*) and limit |
Дата | |
Msg-id | 4CC30C7D.4020305@krogh.cc обсуждение исходный текст |
Ответы |
Re: window function count(*) and limit
|
Список | 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
В списке pgsql-hackers по дате отправления: