Re: Some semantic details of the window-function spec

2008/12/23 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "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.

Still I don't see any use case that is useful with the agglevelsup.
However, contrast to aggregate that can reduce rows into one, window
functions are not expected that you receive only one row, though it is
possible. If this is not documented in the spec, the final decision is
per the community, but in my humble opinion it is not worth.

>>> * 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.

Yes, fixable. But I feel that it's not worth as well. To me the spec
has been saying the order of window function invocations are not
deterministic and they may be separated or not depending on the
implementation, though of course it is not what the spec exactly says.
But it doesn't say "must" or "should" either. Simply, "Two <window
function>s are computed using the same (possibly non-deterministic)
window ordering". In the talk about executor design, we have sometimes
discussed about separating invocations even they are in the identical
window. So I suppose we'd better leave some chances to separate
invocations for some future day.

And the order of each invocations is relevant with nested window
functions talk you gave before. Since window functions, even though
they are contained in the identical window, may be invoked in
different window or different order, nesting should not be supported
obviously.

Also I have documented about order of window function invocation
somewhere in sgml doc, saying "it is not predictable".

If I am asked whether it is said per spec, I cannot find clearly in
the spec. But I feel reasonable.

Regards,

--
Hitoshi Harada