470,647 Members | 1,056 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

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

Nov 11 '05 #1
3 3759
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

Nov 11 '05 #2
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

Nov 11 '05 #3
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

Nov 11 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

226 posts views Thread by Stephen C. Waterbury | last post: by
4 posts views Thread by Ben | last post: by
reply views Thread by Karam Chand | last post: by
reply views Thread by Karam Chand | last post: by
16 posts views Thread by clintonG | last post: by
52 posts views Thread by Paddy | last post: by
1 post views Thread by mai | last post: by
2 posts views Thread by Gerry | last post: by
54 posts views Thread by bearophileHUGS | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.