Re: Anomaly with SUM(). - Mailing list pgsql-general

/*
**
** 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@digitalflex.net]
> Sent: Friday, August 08, 2003 12:01 PM
> To: pgsql-general@postgresql.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
>