On Thu, Jan 16, 2014 at 9:45 AM, Florian Pflug <fgp@phlo.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);
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.