Re: Anomaly with SUM().

/*
**
** 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
*/

> -----Original Message-----
> From: Anthony Best [mailto:abest(at)digitalflex(dot)net]
> Sent: Friday, August 08, 2003 12:01 PM
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Anomaly with SUM().
>
>
> 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)
>
>
>
>
> More Info:
> accounting=# select amount from transactions, chart WHERE
> account=chart.id; amount
> --------
> -75
> 21.13
> -83
> 2.13
> -83
> 21
> 50
> 50
> 2.26
> -166
> 99
> 2.21
> -83
> -100
> 39
> 25
> -70
> -0.02
> 45
> -0.05
> -0.05
> -0.04
> -0.04
> 70
> 75
> 83
> 83
> 166
> 83
> 100
> 0.02
> 0.05
> 0.05
> 0.04
> 0.04
> -21
> -45
> -21.13
> -2.13
> -2.26
> -2.21
> -50
> -50
> -99
> -39
> -25
> (46 rows)
>
> accounting=# select amount from transactions, chart WHERE
> account=chart.id AND amount=amount; amount
> --------
> -70
> 70
> -75
> 75
> -0.02
> 0.02
> -45
> 45
> -21.13
> 21.13
> -0.05
> 0.05
> -83
> 83
> -0.05
> 0.05
> -2.13
> 2.13
> -83
> 83
> -21
> 21
> -50
> 50
> -0.04
> 0.04
> -50
> 50
> -2.26
> 2.26
> -166
> 166
> -99
> 99
> -0.04
> 0.04
> -2.21
> 2.21
> -83
> 83
> -100
> 100
> -39
> 39
> -25
> 25
> (46 rows)
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>