Is "+cast(0.000000001 as double precision)" good solution when trying to round arithmetical expression of double precision type?

[Alexander Skara]

I have Firebird 2.1 dialect 3 database, which, unfortunately, uses double precision fields almost exclusively for storing all the numberd, including the monetary amounts which should be stored as numeric(15,2), numeric(13,4) normally.

So - I have to deal with double precision arithmetic and I should pay special consideration when I need to round the result of double precision arithmetic.

This select is good example of the problem and its proposed solutions (2 alternatives):

select          
    cast(75.6 as double precision)+cast(0.945 as double precision),          
    cast(cast(75.6 as double precision)+cast(0.945 as double precision) as varchar(100)),
    round(cast(75.6 as double precision)+cast(0.945 as double precision), 2),
    round(cast(75.6 as double precision)+cast(0.945 as double precision)+cast(0.000000001 as double precision), 2),
    round(round(cast(75.6 as double precision)+cast(0.945 as double precision), 3), 2),
    round(cast(75.6 as double precision),2)+round(cast(0.945 as double precision),2)         
  from rdb$database          

On can observe, that the internal representation of the sum of 2 double precision numbers is a bit crazy: 76.5449999999999

And that the round is applied to the internal representation round(76.5449999999999,2) and gives logically incorrect value - 76.54.

There are 2 solutions:

1) adding 0.000000001 before rounding:

round(cast(75.6 as double precision)+cast(0.945 as double precision)+cast(0.000000001 as double precision), 2)

2) doing 2 consequtive roundings:

round(round(cast(75.6 as double precision)+cast(0.945 as double precision), 3), 2)

Both workarounds give 76.55 which we consider logically valid.

I am suspicious of the generlity of both workarounds:

1) I feel that negative number -0.0000001 should be added to the negative expressions, but I am not sure...

2) The double precision fields and values can contain numbers with 3, 4 and more decimal values and I am not sure whether round(round(..., 3), 2) is sufficient. Maybe yes for rounding to 2 positions.

Some theory should be done...

But may question is - do these workarounds sane solution to my problem: to make rounding of double precision expressions to be logically valid?

We used custom UDF (e.g. f_roundprice(...)) before and this UDF interally did such +/-0.000000001. This was hidden from the developers and developers where happy and certain. Now we are moving away from UDF (we are migrating to Firebird 3+) and now these things come up and now we see that they are ugly a bit. Most likely, we will have to create internal function (like f_roundprice(...)) again for the Firebird 3+ and make everyone calm again.

But for the time being - we should decide how to do ad hoc rounding, where such rounding as necessary.

Of course, it could be better to move to numeric(...) fields, but there are some fields that should be double precision anyway and they will have to be rounded.

Thanks, Alex

[Mark Rotteveel]

On 11-10-2023 12:29, Alexander Skara wrote:
> I have Firebird 2.1 dialect 3 database, which, unfortunately, uses
> double precision fields almost exclusively for storing all the numberd,
> including the monetary amounts which should be stored as numeric(15,2),
> numeric(13,4) normally.
>
> So - I have to deal with double precision arithmetic and I should pay
> special consideration when I need to round the result of double
> precision arithmetic.
>
> This select is good example of the problem and its proposed solutions (2
> alternatives):
>
> select
>     cast(75.6 as double precision)+cast(0.945 as double precision),
>     cast(cast(75.6 as double precision)+cast(0.945 as double precision)
> as varchar(100)),
>     round(cast(75.6 as double precision)+cast(0.945 as double
> precision), 2),
>     round(cast(75.6 as double precision)+cast(0.945 as double
> precision)+cast(0.000000001 as double precision), 2),
>     round(round(cast(75.6 as double precision)+cast(0.945 as double
> precision), 3), 2),
>     round(cast(75.6 as double precision),2)+round(cast(0.945 as double
> precision),2)
>   from rdb$database

You can make your life a little bit easier by using double precision
literals instead of casts (e.g 75.6e0 instead of cast(75.6 as double
precision) etc.)

> On can observe, that the internal representation of the sum of 2 double
> precision numbers is a bit crazy: 76.5449999999999

That is absolutely normal and expected when using floating point
numbers. That is the whole reason you shouldn't use floating point if
you expect any semblance of precision.

> And that the round is applied to the internal representation
> round(76.5449999999999,2) and gives logically incorrect value - 76.54.

Why is that "logically incorrect"? 76.54**4** rounds to 76.54, so that
is entirely expected. That it doesn't match your expectations is because
you still think of that number as 76.545, while it is not, or in other
words you're expecting exact numeric behaviour, and you don't have that
when using binary floating point numbers.

> There are 2 solutions:
>
> 1) adding 0.000000001 before rounding:
> round(cast(75.6 as double precision)+cast(0.945 as double
> precision)+cast(0.000000001 as double precision), 2)
>
> 2) doing 2 consequtive roundings:
> round(round(cast(75.6 as double precision)+cast(0.945 as double
> precision), 3), 2)
>
> Both workarounds give 76.55 which we consider logically valid.
>
> I am suspicious of the generlity of both workarounds:
> 1) I feel that negative number -0.0000001 should be added to the
> negative expressions, but I am not sure...
> 2) The double precision fields and values can contain numbers with 3, 4
> and more decimal values and I am not sure whether round(round(..., 3),
> 2) is sufficient. Maybe yes for rounding to 2 positions.
> Some theory should be done...
>
> But may question is - do these workarounds sane solution to my problem:
> to make rounding of double precision expressions to be logically valid?

These tricks will work most of the time when working with small numbers,
but not necessarily always. For example, when using very large numbers,
a value like 0.0000001 might be too small to produce a different value
from the other addend (that is, you get the exact same value back), or
the compound error of a floating point calculation might be so big that
adding 0.0000001 is not sufficient to correct it.

An alternative could be to cast to `NUMERIC` (or `DECFLOAT` once you
move to Firebird 4 or higher) when making the calculations, and casting
back to `DOUBLE PRECISION` when you're done with the calculation, but
this may introduce other forms of deviation.

Mark
--
Mark Rotteveel