From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Josh Berkus <josh(at)agliodbs(dot)com>, Greg Stark <stark(at)mit(dot)edu>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCH] Negative Transition Aggregate Functions (WIP) |
Date: | 2013-12-15 09:53:43 |
Message-ID: | CAApHDvq_FUUXT84OT7TdXfaE2vrjpQFdcjZQev3Gi-7+fjPDEQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2013-12-15 10:14:40 | Re: [PATCH] Negative Transition Aggregate Functions (WIP) |
Previous Message | Tom Lane | 2013-12-15 08:29:30 | Re: [PATCH] Negative Transition Aggregate Functions (WIP) |