BUG #3991: pgsql function sum()

mirek napsal(a):

The following bug has been logged online:

Bug reference: 3991
Logged by: mirek
Email address: mirek@mascort.com.pl
PostgreSQL version: 8.2.4
Operating system: Linux fedora(for work) and windows(for development)
Description: pgsql function sum()
Details:

I saw a very strange behavior when i used function SUM() in query (used
inside pg function), query:

select magazyn, ciag, numer, datawp, vat, sum(wnetto-wnettop) as wn,
sum(wvat-wvatp) as wv, sum(wbrutto-wbruttop) as wb from vzk_pozycjezinfo
where magazyn = $1 and ciag = $2 and numer = $3 and datawp = $4
group by magazyn, ciag, numer, datawp, vat
order by magazyn, ciag, numer, datawp, vat

vzk_pozycjezinfo is view

problem is in: sum(wvat-wvatp) where field in view wvat = 33.08 and wvatp =
36.09
Result is -3.01000000000001

If I ask postgres manualy: select sum(33.08 - 36.09)
result is ok -3.01

Now I fix it with round function but i think that is a bug.

How are wv and wb attribute defined? Are they number or flout/double?

Zdenek

"mirek" <mirek@mascort.com.pl> writes:

problem is in: sum(wvat-wvatp) where field in view wvat = 33.08 and wvatp =
36.09
Result is -3.01000000000001

If I ask postgres manualy: select sum(33.08 - 36.09)
result is ok -3.01

Now I fix it with round function but i think that is a bug.

You evidently haven't got much experience with working with
floating-point arithmetic.

regression=# select 33.08::numeric - 36.09::numeric;
?column?
----------
-3.01
(1 row)

regression=# select 33.08::float8 - 36.09::float8;
?column?
-------------------
-3.01000000000001
(1 row)

This is not a bug, it's an inherent consequence of the fact that these
decimal values are not exactly represented in a binary floating-point
system. If you don't like it, don't use float.

regards, tom lane

I think it is main problem. Try

select sum(cast(33.08 as float) - cast(36.09 as float));

you get

sum
-------------------
-3.01000000000001

Better is to use numeric type if you don't have reason to use
float/double. See
http://www.postgresql.org/docs/8.3/static/datatype-numeric.html#DATATYPE-FLOAT

It describes your problem.

Zdenek

Mirosław Marek napsal(a):