Thread: BUG #2117: inconsistency in sum

The following bug has been logged online:

Bug reference:      2117
Logged by:          Adam Kolany
Email address:      dr.a.kolany@wp.pl
PostgreSQL version: 8.0.1
Operating system:   Linux, SuSe 9.2
Description:        inconsistency in sum
Details:

can you explain this?
======================================================
stoff=> select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from
rachunki where okres<6;
  sum  |   sum   | ?column?
-------+---------+----------
 73745 | 6712.55 |  67032.5
(1 row)

stoff=>
======================================================

in real, the 'sum(wplyw)' should be:  73745,05

On Thu, Dec 15, 2005 at 11:03:55AM +0000, Adam Kolany wrote:
> can you explain this?
> ======================================================
> stoff=> select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from
> rachunki where okres<6;
>   sum  |   sum   | ?column?
> -------+---------+----------
>  73745 | 6712.55 |  67032.5
> (1 row)

What data types are wplyw and wydatek?

--
Michael Fuhr

"Adam Kolany" <dr.a.kolany@wp.pl> writes:
> can you explain this?
> ======================================================
> stoff=> select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from
> rachunki where okres<6;
>   sum  |   sum   | ?column?
> -------+---------+----------
>  73745 | 6712.55 |  67032.5
> (1 row)

Seems odd, but you haven't provided nearly enough information to let
anyone else reproduce the problem.

            regards, tom lane

[Please copy the mailing list on replies.]

On Thu, Dec 15, 2005 at 07:32:54PM +0100, Adam Kolany wrote:
> Michael Fuhr napisa?(a):
> >On Thu, Dec 15, 2005 at 11:03:55AM +0000, Adam Kolany wrote:
> >>stoff=> select sum(wplyw),sum(wydatek), sum(wplyw)-sum(wydatek) from
> >>rachunki where okres<6;
> >> sum  |   sum   | ?column?
> >>-------+---------+----------
> >>73745 | 6712.55 |  67032.5
> >>(1 row)
> >
> >What data types are wplyw and wydatek?
> >
> they were float(2)
>
> casting them into numeric helped, so I have changed the types of the to
> numeric, instead of float(2)
>
> this is however a bug, I think.

float(2) gives you a real, aka float4 (32-bit floating point).  As
the documentation points out, that type has a precision of 6 decimal
digits; you seem to object to the rounding:

test=> SELECT 73745::float8 - 6712.55::float8;
 ?column?
----------
 67032.45
(1 row)

test=> SELECT 67032.45::float4;
 float4
---------
 67032.5
(1 row)

test=> SELECT 73745::float4 - 6712.55::float4;
 ?column?
----------
  67032.5
(1 row)

That's arguably not a bug: you've requested a low-precision data
type so you have to expect discrepancies around that 6th digit of
precision.  If you need greater precision then use a double precision
(float8) type, or if you need exact precision (e.g., for handling
money) then use numeric.

--
Michael Fuhr