2008/10/11 Hitoshi Harada <[EMAIL PROTECTED]>: > I am drunk. I forgot cc to -hackers. The talk between me and Ian was like > that. > > 2008/10/12 Hitoshi Harada <[EMAIL PROTECTED]>: >> 2008/10/12 Ian Caulfield <[EMAIL PROTECTED]>: >>> 2008/10/11 Hitoshi Harada <[EMAIL PROTECTED]>: >>>> 2008/10/12 Ian Caulfield <[EMAIL PROTECTED]>: >>>>> 2008/10/11 Hitoshi Harada <[EMAIL PROTECTED]> >>>>>> >>>>>> I'm afraid the patch was too huge, trying to send it again without >>>>>> attachment... >>>>>> >>>>>> I made up my mind to scratch former window functions and redesigned >>>>>> completely new execution model, based on the discussion with Heikki. >>>>>> Attached is the v06 against HEAD today. >>>>> >>>>> Small nit - I get this from the following query: >>>>> >>>>> postgres=# select a, sum(a) over (order by a) from generate_series(1,10) >>>>> a; >>>>> a | sum >>>>> ----+----- >>>>> 1 | 55 >>>>> 2 | 55 >>>>> 3 | 55 >>>>> 4 | 55 >>>>> 5 | 55 >>>>> 6 | 55 >>>>> 7 | 55 >>>>> 8 | 55 >>>>> 9 | 55 >>>>> 10 | 55 >>>>> (10 rows) >>>>> >>>>> From what I can tell of the spec, the 'sum' column should contain a >>>>> running sum (ie 1,3,6 etc). You mention that window frames haven't >>>>> been implemented, but it seems like this case should return an error >>>>> rather than the wrong results... >>>>> >>>>> Thanks, >>>>> Ian >>>>> >>>> >>>> Thanks for notice. >>>> I didn't know that. Ordered aggregate has only rows until current row? >>>> I guess I need read more spec. >>> >>> That's how I read it, the relevant part of the spec seems to be: >>> >>> 5) WD also defines for each row R of RTE the window frame WF of R, >>> consisting of a collection of rows. WF >>> is defined as follows. >>> >>> Case: >>> a) If WD has no window framing clause, then >>> >>> Case: >>> i) If the window ordering clause of WD is not present, then WF is the >>> window partition of R. >>> ii) Otherwise, WF consists of all rows of the partition of R that >>> precede R or are peers of R in the >>> window ordering of the window partition defined by the window ordering >>> clause. >>> >>> Ian >>> >> >> It seems you're right. I will fix it soon probably. >> By this spec, some of the regression tests including nth_value() etc. >> are wrong. Generally we hold only preceding rows in the frame when >> ORDER BY is specified, not only aggregate case. >> Thanks again.
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 Howver, someone who is better at parsing the spec than I am probably ought to check... Ian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
