Anomaly with SUM().

[Anthony Best]

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

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

Anthony Best <ab***@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Anthony Best]

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?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly