Re: Window Functions patch v06 - Mailing list pgsql-hackers

From Hitoshi Harada
Subject Re: Window Functions patch v06
Date
Msg-id e08cc0400810111300qf3c8c74p81d34a84da112857@mail.gmail.com
Whole thread Raw
In response to Re: Window Functions patch v06  ("Hitoshi Harada" <umi.tanuki@gmail.com>)
Responses Re: Window Functions patch v06
List pgsql-hackers
I am drunk. I forgot cc to -hackers. The talk between me and Ian was like that.

2008/10/12 Hitoshi Harada <umi.tanuki@gmail.com>:
> 2008/10/12 Ian Caulfield <ian.caulfield@gmail.com>:
>> 2008/10/11 Hitoshi Harada <umi.tanuki@gmail.com>:
>>> 2008/10/12 Ian Caulfield <ian.caulfield@gmail.com>:
>>>> 2008/10/11 Hitoshi Harada <umi.tanuki@gmail.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.
>
>
> Regards,
>
>
> --
> Hitoshi Harada
>



-- 
Hitoshi Harada


pgsql-hackers by date:

Previous
From: "D'Arcy J.M. Cain"
Date:
Subject: Re: The Axe list
Next
From: "Ian Caulfield"
Date:
Subject: Re: Window Functions patch v06