Thread: pgsql: Fix numeric_mul() overflow due to too many digits after decimal
Fix numeric_mul() overflow due to too many digits after decimal point. This fixes an overflow error when using the numeric * operator if the result has more than 16383 digits after the decimal point by rounding the result. Overflow errors should only occur if the result has too many digits *before* the decimal point. Discussion: https://postgr.es/m/CAEZATCUmeFWCrq2dNzZpRj5+6LfN85jYiDoqm+ucSXhb9U2TbA@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/e7fc488ad67caaad33f6d5177081884495cb81cb Modified Files -------------- src/backend/utils/adt/numeric.c | 10 +++++++++- src/test/regress/expected/numeric.out | 6 ++++++ src/test/regress/sql/numeric.sql | 2 ++ 3 files changed, 17 insertions(+), 1 deletion(-)
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
> This fixes an overflow error when using the numeric * operator if the
> result has more than 16383 digits after the decimal point by rounding
> the result. Overflow errors should only occur if the result has too
> many digits *before* the decimal point.
I think this needs a bit more thought. Before, a case like
select 1e-16000 * 1e-16000;
produced
ERROR: value overflows numeric format
Now you get an exact zero (with a lot of trailing zeroes, but still
it's just zero). Doesn't that represent catastrophic loss of
precision?
In general, I'm disturbed that we just threw away the previous
promise that numeric multiplication results were exact. That
seems like a pretty fundamental property --- which is stated
in so many words in the manual, btw --- and I'm not sure I want
to give it up.
regards, tom lane
On Sat, 10 Jul 2021 at 16:01, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > I think this needs a bit more thought. Before, a case like > select 1e-16000 * 1e-16000; > produced > ERROR: value overflows numeric format > Now you get an exact zero (with a lot of trailing zeroes, but still > it's just zero). Doesn't that represent catastrophic loss of > precision? Hmm, "overflow" isn't a great result for that case either. Zero is the closest we can get to the exact result with a fixed number of digits after the decimal point. > In general, I'm disturbed that we just threw away the previous > promise that numeric multiplication results were exact. That > seems like a pretty fundamental property --- which is stated > in so many words in the manual, btw --- and I'm not sure I want > to give it up. Perhaps we should amend the statement about numeric multiplication to say that it's exact within the limits of the numeric type's supported scale, which we also document in the manual as 16383. That seems a lot better than throwing an overflow error for a result that isn't very big, which limits what's possible with numeric multiplication to much less than 16383 digits. Regards, Dean