Обсуждение: 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)

are these float values?

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.
>
>
>
> 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
>

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.

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

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