Re: [PATCH] Negative Transition Aggregate Functions (WIP)

On Jan10, 2014, at 17:46 , Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> On Jan10, 2014, at 15:49 , Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Also, it might be reasonable for both the regular and the inverse
>>> transition functions to be strict. If a null entering the window
>>> does not matter, then a null exiting the window doesn't either, no?
>
>> That's not true, I think, unless we're special-casing strict transition
>> functions somewhere. AFAICS, an aggregate with a strict transition function
>> will produce the state NULL whenever any of the inputs was NULL, i.e. we won't
>> ever transition out of the NULL state once we got there.
>
> Nope, not the case; read xaggr.sgml and/or the CREATE AGGREGATE reference
> page. An aggregate with a strict transition function essentially just
> ignores null input rows.

I wasn't aware of that, sorry for the noise.

> I suspect the inverse transition function could
> just be made strict with a similar special-case rule (viz, keep the old
> transition value when deleting a null input from the window); but maybe
> I'm missing something and it has to work harder than that anyway.

This seems to hold at least if the state never becomes NULL, i.e. if there's
a non-NULL initiate state (S) and the transfer function never becomes NULL. If
the transfer function (I) and inverse transfer function (T) obey that
I(T(...T(T(S,a),b)...,z), a) = T(...T(S,b)...,z)
for a non-NULL a, then setting T(s,NULL) = I(s,NULL) = s makes the same
thing work for NULL and non-NULL values for a.

If, however, a strict state function T ever returns NULL, things break.
If T(S,a) is NULL in the above, the whole expression becomes NULL because
T is strict (we only special-case the *value* input, not the state input).
But T(...T(S,b)...,z) might very well be non-NULL. That's pretty much
the same situation, unsurprisingly, as with NaNs.

A variant of that problem arises also if the initial state is NULL.
We then wait for the first non-NULL value, and use that as the initial
state. When we later apply the inverse transition function, we might or
might not have to reset the state back to NULL, depending on whether there
are other non-NULL values in the frame or not. This case can be solved by
tracking the number of non-NULL rows in the current frame - whenever
that number if 0, the the output value is NULL, otherwise it's determined
by the state.

So in conclusion, things work for pairs of strict transfer and inverse
transfer functions AFAICS, as long as we do the non-null tracking and
complain should the transfer function ever return NULL.

best regards,
Florian Pflug