Incorrect results from numeric round() and trunc()
Dean Rasheed <dean.a.rasheed@gmail.com>
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-07-07T11:28:32Z
Lists: pgsql-hackers
Attachments
- fix-numeric-round-and-trunc-limits.patch (text/x-patch) patch
The numeric round() and trunc() functions clamp the scale argument to the range between +/- NUMERIC_MAX_RESULT_SCALE, which is +/- 2000. That's a long way short of the actual allowed range of type numeric, so they produce incorrect results when rounding/truncating more than 2000 digits before or after the decimal point. For example, round(1e-5000, 5000) returns 0 instead of 1e-5000. Attached is a patch fixing that, using the actual documented range of type numeric. I've also tidied up a bit by replacing all instances of SHRT_MAX with a new constant NUMERIC_WEIGHT_MAX, whose name more accurately describes the limit, as used in various other overflow checks. In doing so, I also noticed a comment in power_var() which claimed that ln_dweight could be as low as -SHRT_MAX (-32767), which is wrong. It can only be as small as -NUMERIC_DSCALE_MAX (-16383), though that doesn't affect the point being made in that comment. I'd like to treat this as a bug-fix and back-patch it, since the current behaviour is clearly broken. Regards, Dean
Commits
-
Fix scale clamping in numeric round() and trunc().
- 8badee7875c0 12.20 landed
- ece2969266d1 13.16 landed
- a3c0124f69ff 14.13 landed
- 47ca912de029 15.8 landed
- f7aec8c1df90 16.4 landed
- 7a8977d2587f 17.0 landed
- 1ff39f4ff2b6 18.0 landed