On Sun, Dec 15, 2013 at 3:08 PM, Tom Lane <tgl@sss.pgh.pa.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.
