Re: review: More frame options in window functions

2010/1/19 Tom Lane <tgl@sss.pgh.pa.us>:
> Hitoshi Harada <umi.tanuki@gmail.com> writes:
>> In a RANGE offset mode query, for example:
>
>> SELECT sum(ten) over (PARTITION BY four ORDER BY four RANGE BETWEEN 2
>> PRECEDING AND 1 PRECEDING) FROM tenk1
>
>> the frame is determined as "from the first row which has <four> value
>> - 2 to the last row which has <four> value - 1" and executor should
>> know <four> value *is* the sort column even if the column is not
>> actually significant. But the planner removes that information.
>
> Maybe we're just talking past each other.  My point is that the planner
> should record the fact that four is the sort column someplace where the
> executor can find it easily.  AFAICS that doesn't mean it can't be the
> canonicalized form of the sort key.  If a column is dropped out of the
> canonical sort key then it's simply redundant, and hence not relevant to
> determining the range.

Yeah, that's my point, too. The planner has to distinguish "four" from
sort pathkeys and to teach the executor the simple information which
column should be used to determine frame. I was bit wrong because some
of current executor code isn't like it, like using ordNumCols == 0 to
know whether partition equals to frame, though....

Regards,


--
Hitoshi Harada