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

On Jan9, 2014, at 18:09 , Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> For float 4 and float8, wasn't the consensus that the potential
>> lossy-ness of addition makes this impossible anyway, even without the
>> NaN issue? But...
>
> Well, that was my opinion, I'm not sure if it was consensus ;-).

I'd say your example showing how it could produce completely bogus
results was pretty convincing...

> But NaN is an orthogonal problem I think. I'm not sure whether it
> has analogues in other data types.

Transfer functions which are partially invertible are not that
uncommon, I'd say. Browsing through 9.3's list of aggregate functions,
the following come to mind

max()
Values smaller than the maximum can be removed, removing the current
maximum requires a rescan. By remembering the N largest values,
the number of required rescans can be reduced, but never fully
eliminated. Same works for min().

bool_or()
FALSE can be removed, removing TRUE requires a rescan. Could be made
fully invertible by counting the number of TRUE and FALSE values,
similar to my suggestion for how to handle NaN for sum(numeric).
Same works for bool_and().

bit_or()
Like boo_or(), 0 can be removed, everything else requires a rescan.
Same works for bit_and()

Plus, any aggregate with a strict transfer function would be in
exactly the same situation regarding NULL as sum(numeric) is regarding
NaN. AFAIK we don't have any such aggregate in core, though.

best regards,
Florian Pflug