Steve:

Thank you for the article. It was really good. But I read it and If I
understood well, floating point numbers can give errors, for example, when
you substract two values whicha are ALMOST the same. But If you substract
two values that are the same (for example 34.27 - 34.27) then the result
must be zero.

Gustavo

----- Original Message -----
From: "Steve Wiser" <steve@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, November 09, 2007 11:24 AM
Subject: Re: [firebird-support] Rounding error


> This is not a Firebird thing. It always exists when you use floating
> point numbers.
>
> See http://en.wikipedia.org/wiki/Floating_point#Accuracy_problems for
> more information...
>
> -steve
>
> Gustavo wrote:
>>
>> Svein:
>>
>> I understand. MONTOPEND is an amount of money. So perhaps it would be
>> a good
>> idea to use a NUMERIC field. But this field is only an example and I have
>> fields which are quantities and in some cases I need DOUBLE PRECISION
>> fields.
>>
>> I agree with you that, in theory, to say that a line is 100 meters or
>> 99.99957 meters is "almost" the same (but not exactly the same).
>> 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.
>>
>> UPDATE PMOVCC
>> SET MONTOPEND = NULLIF(COALESCE(MONTOPEND,0) - 34.27,0)
>> WHERE (CLAVEMOVIM = 'XXX')
>>
>> 34.27 - 34.27 shoud be 0 even if it is a DOUBLE PRECISSION field.
>>
>> I have a lot of tables already defined with DP fields and I think it´s
>> not
>> convenient now to ALTER them to NUMERIC fields. I think what I can do
>> is to
>> replace the NULLIF using the ROUND function as follows (when I install
>> FireBird 2.1):
>>
>> UPDATE PMOVCC
>> SET MONTOPEND = NULLIF(ROUND(COALESCE(MONTOPEND,0) - 34.27,8),0)
>> WHERE (CLAVEMOVIM = 'XXX')
>>
>> Without installing FireBird 2.1, in some of the fields, I can use CAST as
>> follows:
>>
>> UPDATE PMOVCC
>> SET MONTOPEND = NULLIF(CAST(COALESCE(MONTOPEND,0) - 34.27 AS
>> NUMERIC(18,8)),0)
>> WHERE (CLAVEMOVIM = 'XXX')
>>
>> Gustavo
>>
>> ----- Original Message -----
>> From: "Svein Erling Tysvær" <svein.erling.tysvaer@...
>> <mailto:svein.erling.tysvaer%40kreftregisteret.no>>
>> To: <firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>>
>> Sent: Friday, November 09, 2007 6:06 AM
>> Subject: RE: [firebird-support] Rounding error
>>
>> Hi Gustavo!
>>
>> Do you understand the difference between floating point and fixed point
>> numbers? I'd say the cause of your problem is that you apply fixed
>> decimal
>> logic to floating point values! If I ask someone to give me a line
>> that was
>> 100 meters, it would be ridiculous to complain if I measured it to be
>> 99.99957 metres. However, if I was to be given 10000 euro, and I got
>> 9999.99, I would have reason to complain (at least in theory). The
>> difference is that length is something approximate, i.e. floating point,
>> whereas money can be measured exactly - i.e. fixed point. Speaking
>> Firebird,
>> this could be represented as
>>
>> MyLength DOUBLE PRECISION
>> MyCurrency DECIMAL
>>
>> You should treat these two types of numbers differently, DOUBLE
>> PRECISION is
>> approximate, whereas DECIMAL is exact. Using
>>
>> WHERE MyCurrency = FixedValue
>>
>> is fine, but with MyLength you should never do this. MyLength is
>> approximate
>> and you can never trust it to equal anything - you have to do
>> something like
>>
>> WHERE MyLength BETWEEN FixedValue - 0.001 and FixedValue + 0.001
>>
>> I expect that using ROUND would be similar to BETWEEN (except that ROUND
>> doesn't use indexes if used in WHERE clauses, except if you use
>> expression
>> indexes), but it is worth considering whether MONTOPEND really should
>> be a
>> floating point number (I have no idea what MONTOPEND means).
>>
>> HTH,
>> Set
>>
>> -----Original Message-----
>> From: firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>
>> [mailto:firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>] On Behalf Of Gustavo
>> Sent: 9. november 2007 02:48
>> To: firebird-support@yahoogroups.com
>> <mailto:firebird-support%40yahoogroups.com>
>> Subject: Re: [firebird-support] Rounding error
>>
>> Helen:
>>
>> Thank you for your answer.
>>
>> I don´t understand something. What do you mean when you say "However,
>> if the
>> result of the evaluation is false, the non-zero value that you store back
>> into the DP column will be different to what it would be if the
>> expression
>> had worked on floating-point values"?
>>
>> I think I got an idea to solve this. I didn´t install FireBird 2.1
>> yet, but
>> I read it has a new UDF ROUND. So I think I can modify my UPDATE
>> statement
>> using this function as follows:
>>
>> UPDATE PMOVCC
>> SET MONTOPEND = NULLIF(ROUND(COALESCE(MONTOPEND,0) - 34.27 , 8) , 0)
>> WHERE (CLAVEMOVIM = 'XXX')
>>
>> Do you think this will work all right?
>>
>> Gustavo
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org <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
>> <http://www.ibphoenix.com>
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Yahoo! Groups Links
>>
>> __________ Información de NOD32, revisión 2645 (20071108) __________
>>
>> Este mensaje ha sido analizado con NOD32 antivirus system
>> http://www.nod32.com <http://www.nod32.com>
>>
>>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
> __________ Información de NOD32, revisión 2649 (20071109) __________
>
> Este mensaje ha sido analizado con NOD32 antivirus system
> http://www.nod32.com
>
>