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

On Jan15, 2014, at 19:56 , Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> It strikes me that for numeric what you really need is to just tell
> the sum operation, whether through a parameter or otherwise, how many
> decimal places to show in the output. Obviously that's not a
> practical change for sum() itself, but if we're inventing new stuff it
> can be done.

You can already do that, just cast the result of SUM(numeric) to
an appropriately constrained NUMERIC type, i.e. to NUMERIC(prec, scale).

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

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

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.

best regards,
Florian Pflug