On 15 December 2013 01:57, Tom Lane <t...@sss.pgh.pa.us> wrote: > Josh Berkus <j...@agliodbs.com> writes: >> I think even the FLOAT case deserves some consideration. What's the >> worst-case drift? > > Complete loss of all significant digits. > > The case I was considering earlier of single-row windows could be made > safe (I think) if we apply the negative transition function first, before > incorporating the new row(s). Then for example if you've got float8 1e20 > followed by 1, you compute (1e20 - 1e20) + 1 and get the right answer. > It's not so good with two-row windows though: > > Table correct sum of negative-transition > this + next value result > 1e20 1e20 1e20 + 1 = 1e20 > 1 1 1e20 - 1e20 + 0 = 0 > 0 > >> In general, folks who do aggregate operations on >> FLOATs aren't expecting an exact answer, or one which is consistent >> beyond a certain number of significant digits. > > Au contraire. People who know what they're doing expect the results > to be what an IEEE float arithmetic unit would produce for the given > calculation. They know how the roundoff error ought to behave, and they > will not thank us for doing a calculation that's not the one specified. > I will grant you that there are plenty of clueless people out there > who *don't* know this, but they shouldn't be using float arithmetic > anyway. > >> And Dave is right: how many bug reports would we get about "NUMERIC is >> fast, but FLOAT is slow"? > > I've said this before, but: we can make it arbitrarily fast if we don't > have to get the right answer. I'd rather get "it's slow" complaints > than "this is the wrong answer" complaints. >

Hi, Reading over this, I realised that there is a problem with NaN handling --- once the state becomes NaN, it can never recover. So the results using the inverse transition function don't match HEAD in cases like this: create table t(a int, b numeric); insert into t values(1,1),(2,2),(3,'NaN'),(4,3),(5,4); select a, b, sum(b) over(order by a rows between 1 preceding and current row) from t; which in HEAD produces: a | b | sum ---+-----+----- 1 | 1 | 1 2 | 2 | 3 3 | NaN | NaN 4 | 3 | NaN 5 | 4 | 7 (5 rows) but with this patch produces: a | b | sum ---+-----+----- 1 | 1 | 1 2 | 2 | 3 3 | NaN | NaN 4 | 3 | NaN 5 | 4 | NaN (5 rows) Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers