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