problem: sum is incorrect (e-10)

[DM]

Hi all,
I've a problem.

The query select mo_qta from oobjm.movoptitt raded where mo_opzione =
'1,253CEUUS16M0 8' gives:
MO_QTA
------------------------
+2.503477100000 00E+005
+2.503477100000 00E+005
+2.503477100000 00E+005
+3.337969400000 00E+005
+1.001390820000 00E+006
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-3.3379694000000 0E+005
-1.0013908200000 0E+006

and the query select sum(mo_qta) from oobjm.movoptitt raded where
mo_opzione = '1,253CEUUS16M0 8' gives:
1
------------------------
+3.492459654808 04E-010

The field is a double.

I think is impossible...
How can i find where the problem is?

Thanks all,
DM

[Serge Rielau]

DM wrote:

Hi all,
I've a problem.

The query select mo_qta from oobjm.movoptitt raded where mo_opzione =
'1,253CEUUS16M0 8' gives:
MO_QTA
------------------------
+2.503477100000 00E+005
+2.503477100000 00E+005
+2.503477100000 00E+005
+3.337969400000 00E+005
+1.001390820000 00E+006
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-3.3379694000000 0E+005
-1.0013908200000 0E+006

and the query select sum(mo_qta) from oobjm.movoptitt raded where
mo_opzione = '1,253CEUUS16M0 8' gives:
1
------------------------
+3.492459654808 04E-010

The field is a double.

I think is impossible...
How can i find where the problem is?

Your problem is in binary floating point arithmetic.
You can either use DECIMAL to do exact math, or in DB2 9.5 you can use
DECFLOAT.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

DM wrote:

Hi all,
I've a problem.

The query select mo_qta from oobjm.movoptitt raded where mo_opzione =
'1,253CEUUS16M0 8' gives:
MO_QTA
------------------------
+2.503477100000 00E+005
+2.503477100000 00E+005
+2.503477100000 00E+005
+3.337969400000 00E+005
+1.001390820000 00E+006
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-3.3379694000000 0E+005
-1.0013908200000 0E+006

and the query select sum(mo_qta) from oobjm.movoptitt raded where
mo_opzione = '1,253CEUUS16M0 8' gives:
1
------------------------
+3.492459654808 04E-010

The field is a double.

I think is impossible...
How can i find where the problem is?

Ah, the classic floating point rounding issue. The answer is "correct"
in that DB2 doesn't "lie by rounding". Try the following under Python
(another thing which doesn't lie about floating point results by
rounding):

Python 2.5.1 (r251:54863, Oct 5 2007, 13:50:07)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright" , "credits" or "license" for more information.

>>(+2.503477100 00000E+005

.... +2.503477100000 00E+005
.... +2.503477100000 00E+005
.... +3.337969400000 00E+005
.... +1.001390820000 00E+006
.... -2.5034771000000 0E+005
.... -2.5034771000000 0E+005
.... -2.5034771000000 0E+005
.... -3.3379694000000 0E+005
.... -1.0013908200000 0E+006)
3.4924596548080 444e-10

The problem is that floating point can't accurately represent these
values. For example, again under Python:

>>+2.5034771000 0000E+005

250347.70999999 999

Many tools round off the last significant digit to "correct" the
answer, but DB2's CLP doesn't - it's "brutally honest" about the value
of floating point numbers. The best solution (provided the range of
numbers you're dealing will fit) is to use DECIMAL instead (e.g.
DECIMAL(10, 2) looks like it'd be sufficient for the values above).

See "Representa ble numbers, conversion and rounding" under
http://en.wikipedia.org/wiki/Floating_point for more information.
Cheers,

Dave.

[DM]

On 19 Mag, 18:20, "Dave Hughes" <d...@waveform. plus.comwrote:

DM wrote:

Hi all,
I've a problem.

The query select mo_qta from oobjm.movoptitt raded where mo_opzione =
'1,253CEUUS16M0 8' gives:
MO_QTA
------------------------
+2.503477100000 00E+005
+2.503477100000 00E+005
+2.503477100000 00E+005
+3.337969400000 00E+005
+1.001390820000 00E+006
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-2.5034771000000 0E+005
-3.3379694000000 0E+005
-1.0013908200000 0E+006

and the query select sum(mo_qta) from oobjm.movoptitt raded where
mo_opzione = '1,253CEUUS16M0 8' gives:
1
------------------------
+3.492459654808 04E-010

The field is a double.

I think is impossible...
How can i find where the problem is?

Ah, the classic floating point rounding issue. The answer is "correct"
in that DB2 doesn't "lie by rounding". Try the following under Python
(another thing which doesn't lie about floating point results by
rounding):

Python 2.5.1 (r251:54863, Oct 5 2007, 13:50:07)
[GCC 4.1.3 20070929 (prerelease) (Ubuntu 4.1.2-16ubuntu2)] on linux2
Type "help", "copyright" , "credits" or "license" for more information.>>( +2.503477100000 00E+005

... +2.503477100000 00E+005
... +2.503477100000 00E+005
... +3.337969400000 00E+005
... +1.001390820000 00E+006
... -2.5034771000000 0E+005
... -2.5034771000000 0E+005
... -2.5034771000000 0E+005
... -3.3379694000000 0E+005
... -1.0013908200000 0E+006)
3.4924596548080 444e-10

The problem is that floating point can't accurately represent these
values. For example, again under Python:

>+2.50347710000 000E+005

250347.70999999 999

Many tools round off the last significant digit to "correct" the
answer, but DB2's CLP doesn't - it's "brutally honest" about the value
of floating point numbers. The best solution (provided the range of
numbers you're dealing will fit) is to use DECIMAL instead (e.g.
DECIMAL(10, 2) looks like it'd be sufficient for the values above).

See "Representa ble numbers, conversion and rounding" underhttp://en.wikipedia.or g/wiki/Floating_pointf or more information.

Cheers,

Dave.

Thanks a lot.
We cannot change the db definition right now but we solved the problem
adding a having clause at the end of the query (we need to know for
wich "opzione" this sum is <0, now we use having abs(sum(mo_qta) ) >
0.0001).

Thanks.

DM