On Tue, 14 May 2024 at 14:53, Huw Rogers <djnz00@gmail.com> wrote:
> test=# select ('170141183460469231731687303715884105727'::numeric / '9223372036854775808'::numeric) *
'9223372036854775808'::numeric;
> ?column?
> -----------------------------------------
> 170141183460469231731687303715884105728
I don't have enough experience in NUMERIC to tell if this is a bug or
not. There's a comment that explains the standard does not offer much
guidance on this. If you want higher precision then you should use a
typemod in the numeric cast to specify that.
The scale selection for the division is done in select_div_scale(). A
comment there says:
/*
* The result scale of a division isn't specified in any SQL standard. For
* PostgreSQL we select a result scale that will give at least
* NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
* result no less accurate than float8; but use a scale not less than
* either input's display scale.
*/
NUMERIC_MIN_SIG_DIGITS is 16.
That comment appears correct to me. It's no less accurate than float8.
Adding add a (pretty large) typemod shows a more accurate answer:
select ('170141183460469231731687303715884105727'::numeric(1000,900) /
'9223372036854775808'::numeric * '9223372036854775808'::numeric);
results in 170141183460469231731687303715884105727.
I'm not sure what we could do to make a better choice of scale. Making
it larger will make the code slower and unless we put the scale at the
limit of the numeric type, someone could still complain about wrong
answers.
David