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

On 15 December 2013 01:57, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Josh Berkus <josh(at)agliodbs(dot)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