I confirmed this on Oracle:
select last_value(id) over (order by id) as last_id, id from foo;
LAST_ID ID
------- --
0 0
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
So when you specify ORDER BY clause on window definition, the frame
always contains rows preceding from current row, not only on
aggregate.
>
> Doing a bit of poking around in the spec and the Oracle documentation,
> I think (but I'm not 100% sure) that the results returned were correct
> for the query:
>
> postgres=# select a, sum(a) over () from generate_series(1,10) a;
> ERROR: either PARTITION BY or ORDER BY must be specified in window clause
The empty window definition is forbidden temporarily because I didn't
know how to take it although there's no problem to execute it. I
haven't found any description on spec yet, I know at least Oracle
allows it.
I can find how to do it with the new (window execution model) design,
(and the design is suitable to fix it above,) but at first before
going into trivial specs, I would like core hackers to review the
model is better than before or not. Thank you for your cooperation.
Regards,
--
Hitoshi Harada