Re: Window functions patch v04 for the September commit fest

2008/9/5 Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>:
> Heikki Linnakangas wrote:
>>
>> I'll review the parser/planner changes from the current patch.
>
> Looks pretty sane to me. Few issues:
>
> Is it always OK to share a window between two separate window function
> invocations, if they both happen to have identical OVER clause? It seems OK
> for stable functions, but I'm not sure that's correct for expressions
> involving volatile functions. I wonder if the SQL spec has anything to say
> about that.

It may be here:

---quote---
In general, two <window function>s are computed independently, each
one performing its own sort of its data, even if they use the same
data and the same <sort specification list>. Since sorts may specify
partial orderings,
the computation of <window function>s is inevitably non-deterministic
to the extent that the ordering is not total. Nevertheless, the user
may desire that two <window function>s be computed using the same
ordering, so that, for example, two moving aggregates move through the
rows of a partition in precisely the same order.

Two <window function>s are computed using the same (possibly
non-deterministic) window ordering of the rows if any of the following
are true:

― The <window function>s identify the same window structure descriptor.

― The <window function>s' window structure descriptors have window
partitioning clauses that enumerate the same number of column
references, and those column references are pairwise equivalent in
their order
of occurrence; and their window structure descriptors have window
ordering clauses with the same number of <sort key>s, and those <sort
key>s are all column references, and those column references are
pairwise equivalent in their order of occurrence, and the <sort
specification>s pairwise specify or imply <collate clause>s that
specify equivalent <collation name>s, the same <ordering
specification> (ASC or DESC), and the same <null ordering> (NULLS
FIRST or NULLS LAST).

― The window structure descriptor of one <window function> is the
ordering window of the other <window function>, or both window
structure descriptors identify the same ordering window.
/---quote---

But it doesn't say anything about volatile functions. Do you have
example that is bad with the current design?

The other issuses are OK. I missed those cases. will fix them.

Regards,

--
Hitoshi Harada