Re: Some semantic details of the window-function spec

"Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com> writes:
> 2008/12/23 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> * Unlike aggregates, there doesn't seem to be any concept of a window
>> function being attached to an outer-level query --- in fact 6.10 rule
>> 4 says that a window function's argument can't contain outer references
>> at all. That seems excessively strong, but it does seem that there is
>> no point in a "winlevelsup" field in WindowFunc, nor in implementing
>> any infrastructure for outer-level window functions.

> I am so ignorant that I don't know what exactly agglevelsup
> represents. Just copied it from agg to window functions... Could
> someone show me easy example?

In something like

select ..., (select avg(a.x) from b), ... from a;

the avg() invocation is actually an aggregate of the outer query over a.
It's a constant so far as any one invocation of the sub-select on b
is concerned. The SQL:2008 spec is pretty opaque about this (as it
is on a whole lot of matters :-() but if you read older versions like
SQL92 it's spelled out a bit more clearly. I don't see any wording
suggesting that window functions are supposed to work this way, however.

>> * The last part of section 4.14 states that two different window
>> functions must be evaluated against the same sorted row ordering if
>> they have syntactically identical partition and ordering clauses,
>> even if the windows are otherwise distinct (in particular there
>> could be different framing clauses). Since we don't currently implement
>> framing clauses the latter is not too interesting, but it's still true
>> that the patch as I currently have it doesn't fully meet that
>> requirement: if you intentionally specify separate but equivalent named
>> window definitions, it won't be smart enough to fold them together,
>> and you could end up with extra sorts happening and possibly a different
>> ordering of sort-peer rows. How worried are we about that?

> Is it? I intended all equivalent windows are folded into one as far as
> equal(w1->partitionClause, w2->partitionClause) &&
> equal(w1->orderClause, w2->orderClause) is true.

Well, I rewrote that code pretty heavily because it didn't work per spec
as far as references to existing windows went. The problem that remains
is that in something like

WINDOW w1 as (partition by x),
w2 as (partition by x),
w3 as (w1 order by y),
w4 as (w2 order by y)

w3 and w4 are equivalent but it's pretty hard to recognize that. And
even if we did recognize it, we couldn't simply fold them together into
a single windowClause entry without changing the way that the query
looks on reverse-listing. (The patch as submitted doesn't even *have*
reverse-listing capability for WINDOW clauses, but I plan to fix that
tomorrow ...)

This is doubtless fixable with another level of indirection, I'm just
wondering how much trouble it's worth. It seems like the only case
that will really arise in practice is duplicate anonymous windows
(identical OVER clauses), and the code does fold those together.

regards, tom lane