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

On Thu, Jan 16, 2014 at 9:45 AM, Florian Pflug <fgp(at)phlo(dot)org> wrote:

> BTW, AVG() and STDDEV() have the same issue. The problem is just partially
> masked by the division by N (or N-1) at the end, because we always emit as
> least 16 fractional digits when dividing. So you have to have an input
> value with a larger scale than that to trigger it.
>
> For the following query
>
> select avg(x) over (order by i rows between current row and 1 following)
> from (values
> (1,1), (2,1), (3,0.000000000000000000000000000000001), (4,1), (5,1)
> ) t(i,x);
>
> 9.3 returns
> avg
> -------------------------------------
> 1.00000000000000000000
> 0.500000000000000000000000000000001
> 0.500000000000000000000000000000001
> 1.00000000000000000000
> 1.00000000000000000000
>
> but HEAD+patch returns
> avg
> -------------------------------------
> 1.00000000000000000000
> 0.500000000000000000000000000000001
> 0.500000000000000000000000000000001
> 1.000000000000000000000000000000000
> 1.000000000000000000000000000000000
>
>
Uhhh, that's bad news indeed. That means that I'll need to remove not only
all inverse transition functions for all aggregates on numeric types, but
also avg for int types, the stddev* functions for everything, since they
internally use numeric. I guess that only leaves SUM for smallint, int,
bigint, cash and interval, along with count(exp), count(*)...

> I have to admit that I'm *very* tempted to suggest we simply ignore this -
> but that *would* mean accepting that windowed aggregates are non-
> deterministic in the sense that their result (even if only in the number
> of trailing zeros) depends on values outside of the frame. Which, I guess,
> is a box that best stays closed...
>
>
Yeah, I can understand the temptation but I agree we can't go changing
results.

> I'm currently thinking the best way forward is to get a basic patch
> without any NUMERIC stuff committed, and to revisit this after that's done.
>
>
Agreed... I'll warm up my delete key.

Regards

David Rowley

> best regards,
> Florian Pflug
>
>