Erik Wienhold <ewie@ewie.name> writes:
> On 13/10/2022 18:20 CEST Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>> select power(10, -18::numeric);
>> power
>> --------------------
>> 0.0000000000000000
>>
>> Why is the cast throwing off the result?
> Calling power(numeric, numeric) is what I expect in that case instead of
> downcasting the exponent argument to double precision, thus losing precision.
An inexact result isn't surprising, but it shouldn't be *that* inexact.
It looks to me like numeric.c's power_var_int() code path is setting the
result rscale without considering the possibility that the result will
have negative weight (i.e. be less than one). The main code path in
power_var() does adjust for that, so for example
regression=# select power(10, -18.00000001::numeric);
power
-------------------------------------
0.000000000000000000999999976974149
(1 row)
but with an exact-integer exponent, not so much --- you just get 16 digits
which isn't enough.
I'm inclined to think that we should push the responsibility for choosing
its rscale into power_var_int(), because internally that already does
estimate the result weight, so with a little code re-ordering we won't
need duplicative estimates. Don't have time to work on that right now
though ... Dean, are you interested in fixing this?
regards, tom lane