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

On Sun, Dec 15, 2013 at 3:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > It's not so good with two-row windows though:
>
> Actually, carrying that example a bit further makes the point even more
> forcefully:
>
> Table correct sum of negative-transition
> this + next value result
> 1e20 1e20 1e20 + 1 = 1e20
> 1 1 1e20 - 1e20 + 0 = 0
> 0 0 0 - 1 + 0 = -1
> 0 1 -1 - 0 + 1 = 0
> 1
>
> Those last few answers are completely corrupt.
>
>
For sake of the archives I just wanted to reproduce this...
I used the following query with the patch which was attached upthread to
confirm this:

SELECT sum(n::float8) OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1
FOLLOWING)
FROM (VALUES(1,1e20),(2,1)) n(i,n);

sum
--------
1e+020
0
(2 rows)

SUM(1) should equal 1 not 0.

But unpatched I get:

sum
--------
1e+020
1
(2 rows)

This discovery seems like good information to keep around, so I've added a
regression test in my local copy of the patch to try to make sure nobody
tries to add a negative trans for float or double in the future.

Regards

David Rowley

> regards, tom lane
>