Anomaly with SUM().

On Fri, 8 Aug 2003, Anthony Best wrote:

I've noticed that the SUM() seems to overflow under some situations.

The only difference is the order that the data is retrived from the
database.

Is amount a float type column (float4 or float8)? If so, you're probably
just running into issues with float precision problems. Changing the
order of the operations can change the final value of a sequence of
operations on float.

are these float values?

Anthony Best wrote:

Anthony Best <abest@digitalflex.net> writes:

I've noticed that the SUM() seems to overflow under some situations.
The only difference is the order that the data is retrived from the
database.

accounting=# select sum(amount) from transactions, chart WHERE
account=chart.id;
sum
---------------------
5.6843418860808e-14
(1 row)

accounting=# select sum(amount) from transactions, chart WHERE
account=chart.id AND amount=amount;
sum
-----
0
(1 row)

That's not an overflow, it's merely roundoff error. If this surprises
you, possibly you should be using type NUMERIC instead of float.

regards, tom lane

Stephan Szabo wrote:

On Fri, 8 Aug 2003, Anthony Best wrote:

I've noticed that the SUM() seems to overflow under some situations.

The only difference is the order that the data is retrived from the
database.

Is amount a float type column (float4 or float8)? If so, you're probably
just running into issues with float precision problems. Changing the
order of the operations can change the final value of a sequence of
operations on float.

It's "double precision." (Which is float8?).

So, should I tweak my join to preserve order, or something else?

Tom Lane wrote:

Anthony Best <abest@digitalflex.net> writes:

I've noticed that the SUM() seems to overflow under some situations.
The only difference is the order that the data is retrived from the
database.

accounting=# select sum(amount) from transactions, chart WHERE
account=chart.id;
sum
---------------------
5.6843418860808e-14
(1 row)

accounting=# select sum(amount) from transactions, chart WHERE
account=chart.id AND amount=amount;
sum
-----
0
(1 row)

That's not an overflow, it's merely roundoff error. If this surprises
you, possibly you should be using type NUMERIC instead of float.

regards, tom lane

Oh, thats right. 'overflow' was the wrong word. I was thinking numeric
was used internally for 'double precision' for some reason.

thank you.
Anthony.

/*
**
** Not a surprise, to them that knows:
**
*/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>

#define A_LEN 500

static float foo[A_LEN];

static double bar[A_LEN];

int
main (void)
{
long i;
double d;
float f;
srand((unsigned)(double)time(NULL));
for (i = 0; i < A_LEN; i++)
{
d = rand () / (rand () + 1.0);
d *= d;
if (rand () % 2)
d = -d;
foo[i] = (float) d;
bar[i] = d;
}
f = 0;
d = 0;
for (i = 0; i < A_LEN; i++)
{
f += foo[i];
d += bar[i];
}
printf ("forward float sum = %.20f\n", f);
printf ("forward double sum = %.20f\n", d);
f = 0;
d = 0;
for (i = A_LEN - 1; i >= 0; i--)
{
f += foo[i];
d += bar[i];
}
printf ("backward float sum = %.20f\n", f);
printf ("backward double sum = %.20f\n", d);
return 0;
}
/*
Typical output:
forward float sum = 231466.62182403207000000000
forward double sum = 231466.62885047426000000000
backward float sum = 231466.62182403210000000000
backward double sum = 231466.62885047423000000000
*/