Re: Window Functions patch v06

2008/10/11 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> I am drunk. I forgot cc to -hackers. The talk between me and Ian was like that.
>
> 2008/10/12 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>> 2008/10/12 Ian Caulfield <ian(dot)caulfield(at)gmail(dot)com>:
>>> 2008/10/11 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>>> 2008/10/12 Ian Caulfield <ian(dot)caulfield(at)gmail(dot)com>:
>>>>> 2008/10/11 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>
>>>>>>
>>>>>> 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