Subject Re: [firebird-support] Re: Rounding error Gustavo 2007-11-12T01:15:35Z

I agree with you that

7,105427357601E-15 = 0.00710542735760100000 != 0

But the problem is that the result of doing 34.27 - 34.27 should be ZERO and
not 7,105427357601E-15. I still think this is an error (of floating point
arithmetic or of FireBird).

Anyway I guess you are right when you say that I am using floating point
data type to store exact fixed point data and may be this is my real
problem.

Thank you very much for your advice.

Gustavo

----- Original Message -----
To: <firebird-support@yahoogroups.com>
Sent: Friday, November 09, 2007 9:06 PM
Subject: [firebird-support] Re: Rounding error

> Anyway, I
> think it´s an error of FireBird that if the DP field MONTOPEND has a
> value of 34.27 and after executing the statement bellow, then the
> field has the value 7,105427357601E-15 instead of null.

No, it makes perfect sense.

7,105427357601E-15 = 0.00710542735760100000 != 0

So why on earth would you expect:
NullIf(7,105427357601E-15, 0) = null ?

The problem is simple. You have used an approximating floating point
style data type to store exact fixed point data, and because of
approximation and rounding errors, subtractions you are hoping to
equal zero; unfortunately don't.

This is a common mistake, and I would be lying to you if I said I have
never made the same mistake myself. But I would suggest that I am
doubtful that Firebird would 'fix' this behaviour to work contrary
with how all programming languages and databases expect floating point
numbers to behave, so you would have two choices.

Either fix the data type to hold numerics (you will still need to deal
with roundings at conversion), or live with these oddities popping up
every now and then. I suggest you fix your data types.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++