Re: WIDTH_BUCKET inconsistency

Martin Visser <Martin(dot)Visser(at)brytlyt(dot)com> writes:
> Inconsistent results between numeric and double precision types.

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)

and that's really the right answer given

regression=# select 10::numeric / 90::numeric;
?column?
------------------------
0.11111111111111111111
(1 row)

We could carry that division out to more decimal places, but we'd
still just have a string of ones, which would become a string of
nines after multiplication by 9, and then floor() doesn't have a
choice about what to return. If we try to fudge that to get the
"right" answer, we'd be getting wrong answers for other cases.

[ 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.

While comparing the numeric and float8 versions of width_bucket,
I did notice that float8 allows +-Infinity for the first argument:

regression=# select width_bucket('inf'::float8,10,100,9);
width_bucket
--------------
10
(1 row)

while numeric doesn't:

regression=# select width_bucket('inf'::numeric,10,100,9);
ERROR: operand, lower bound, and upper bound cannot be infinity

That seems like an oversight in my recent patch to allow infinities
in numeric.

regards, tom lane