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

On 15 December 2013 01:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Berkus <josh@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 |   12 |   2 |   33 | NaN | NaN4 |   3 | NaN5 |   4 |   7
(5 rows)

but with this patch produces:
a |  b  | sum
---+-----+-----1 |   1 |   12 |   2 |   33 | NaN | NaN4 |   3 | NaN5 |   4 | NaN
(5 rows)

Regards,
Dean