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

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