I wrote:
> That's basically roundoff error, which I'm afraid we're not going
> to be able to do much about. You could probably find other examples
> where the numeric calculation is "right" and the float8 calculation
> is "wrong". For example, the case
> regression=# select width_bucket(20::numeric,10,100,9);
> width_bucket
> --------------
> 1
> (1 row)
> is essentially computing floor() of
> regression=# select 9 * (10::numeric / 90::numeric) + 1;
> ?column?
> ------------------------
> 1.99999999999999999999
> (1 row)
...
> [ thinks for awhile... ] It's tempting to propose that we take
> the next-to-last result (1.99999999999999999999 here) and round
> it to one fewer decimal place before applying floor(). It's
> still scary to contemplate whether that might make as many cases
> worse as it does better, but I suspect the reason why we get a
> nicer answer for this case in the float8 code is that the CPU is
> doing something equivalent to that in the float calculation.
No, waitasecond. Studying the float code a little closer, I realize
that what it's doing is equivalent to
regression=# select (9 * 10::numeric) / 90::numeric + 1;
?column?
------------------------
2.00000000000000000000
(1 row)
That is, the numeric code is doing the calculations in an order that
must lead to an inexact result, whereas we could easily do them in
an order that always gives an exact result when one is possible.
Independently of whether trying to round a little harder would be
advisable, that seems like a clear win.
There are certain parties around here who would probably bitch
if I were to back-patch this change, but it seems fine to do in
HEAD.
regards, tom lane