Subject Re: [firebird-support] Double precision field problem
Author bill_lam
Svein Erling Tysvaer wrote:
> You get what you ask for, Ibrahim. Floating point numbers (e.g. double
> precision) are good for measuring things, it doesn't matter whether the
> length of a line is 1.57 metres or 1.5699999999999 metres. Field1
> doesn't have the exact value 79.69, it has a value of approximately
> 79.69 (e.g. 79.69000000000000001238) and similar for Field2.
> If such differences do matter, you should probably use fixed point
> numbers - e.g. decimal or numeric.
> HTH,
> Set
> ibrahim bulut wrote:
>> i am in trouble double precision field with subtract operation
>> field1 is double precision
>> field2 is double precision
>> field1's value is 79.69
>> field2's value is 78.12
>> when i subtract field2 from field1 with this sql statement
>> select field1 - field2 from table1
>> the result is 1,56999999999999
>> the result must be 1,57

For some reason, I prefer using double instead of exact numeric storage, usually
it doesn't cause any problem because applications will take care of rounding
error. But I also interested to know what is the firebird's correct syntax to do
rounding up to 4 places after decimal? and does it need udf for the function round?
update t set field1= round(field1-field2,4)

floating numbers usually will not be compared for equality, to test whether
1.57 = 79.69-78.12
test something like
ct > abs(1.57-(79.69-78.12))

alternatively you may want to test the relative error
ct*max(abs(1.57),abs(79.69-78.12)) > abs(1.57-(79.69-78.12))

traditionally ct (comparison tolerance) is chosen to be smaller than 2^_34 so
that adjacent 32-bit integers will not be compared equal.