- - - 29-Jul-2019 18:20 Kjell Rilbe kjell.rilbe@... [firebird-support] wrote: - - -

>>[...]

> Geoff,
>
> The value 8.415 (and probably other part results) cannot be exactly
> represented in binary form, just like 1/3 cannot in decimal form. You
> get an infinite number of binary "decimals". That means that even the
> double precision variables that you use will store rounded results.
> These small rounding errors will result in some expressions to land just
> under the exact result 8.415, and will thus be rounded down. Other will
> land just above 8.415 and be rounded up.
>
> If you need this to be handled in an exact manner, you will have to use
> some data type that does not suffer from this kind of rounding errors.
>
> Note: all floating point types have this problem.
>
> Regards,
>
> Kjell Rilbe

Thanks for your comments Kjell. The surprise came because my old
rounding function gives consistent results across all these
expressions - and try as I might, I cannot find a way to show
the variation between the example expressions (even when output
to 18 digits - well beyond the significance of double). AFAICT
the results are all within epsilon of 8.415.

(Nor do I see the problem appear when I tested this specific
example other environments, although in this case I suspect it
may be that normal programming environments boost to long double
for their intermediate results when dealing with compound
calculations like this, whereas I'm guessing that PSQL does not.)

In order to retain consistency with the previous version of my
product I will override the built-in round function with one that
continues to use epsilon in its rounding comparisons (at least
Firebird makes this very easy to do).

--
Geoff Worboys
Telesis Computing Pty Ltd