Thread: BUG #3991: pgsql function sum()
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. With regards Mirek
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):
> wv and wb are defined as double precision
>
> Mirek
>
> Zdenek Kotala pisze:
>> 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
>>
>>
>> __________ NOD32 Informacje 2903 (20080226) __________
>>
>> Wiadomosc zostala sprawdzona przez System Antywirusowy NOD32
>> http://www.nod32.com lub http://www.nod32.pl
>>
>>
>
>
>