Skip to content

Commit a3c0124

Browse files
deanrasheeddeanrasheed
committed
Fix scale clamping in numeric round() and trunc().
The numeric round() and trunc() functions clamp the scale argument to the range between +/- NUMERIC_MAX_RESULT_SCALE (2000), which is much smaller than the actual allowed range of type numeric. As a result, they return incorrect results when asked to round/truncate more than 2000 digits before or after the decimal point. Fix by using the correct upper and lower scale limits based on the actual allowed (and documented) range of type numeric. While at it, use the new NUMERIC_WEIGHT_MAX constant instead of SHRT_MAX in all other overflow checks, and fix a comment thinko in power_var() introduced by e54a758 -- the minimum value of ln_dweight is -NUMERIC_DSCALE_MAX (-16383), not -SHRT_MAX, though this doesn't affect the point being made in the comment, that the resulting local_rscale value may exceed NUMERIC_MAX_DISPLAY_SCALE (1000). Back-patch to all supported branches. Dean Rasheed, reviewed by Joel Jacobson. Discussion: https://postgr.es/m/CAEZATCXB%2BrDTuMjhK5ZxcouufigSc-X4tGJCBTMpZ3n%3DxxQuhg%40mail.gmail.com
1 parent c2342a9 commit a3c0124

File tree

3 files changed

+153
-9
lines changed

3 files changed

+153
-9
lines changed

‎src/backend/utils/adt/numeric.c

Lines changed: 26 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -251,6 +251,13 @@ struct NumericData
251251
| ((n)->choice.n_short.n_header & NUMERIC_SHORT_WEIGHT_MASK)) \
252252
: ((n)->choice.n_long.n_weight))
253253

254+
/*
255+
* Maximum weight of a stored Numeric value (based on the use of int16 for the
256+
* weight in NumericLong). Note that intermediate values held in NumericVar
257+
* and NumericSumAccum variables may have much larger weights.
258+
*/
259+
#define NUMERIC_WEIGHT_MAX PG_INT16_MAX
260+
254261
/* ----------
255262
* NumericVar is the format we use for arithmetic. The digit-array part
256263
* is the same as the NumericData storage format, but the header is more
@@ -1411,10 +1418,15 @@ numeric_round(PG_FUNCTION_ARGS)
14111418
PG_RETURN_NUMERIC(duplicate_numeric(num));
14121419

14131420
/*
1414-
* Limit the scale value to avoid possible overflow in calculations
1421+
* Limit the scale value to avoid possible overflow in calculations.
1422+
*
1423+
* These limits are based on the maximum number of digits a Numeric value
1424+
* can have before and after the decimal point, but we must allow for one
1425+
* extra digit before the decimal point, in case the most significant
1426+
* digit rounds up; we must check if that causes Numeric overflow.
14151427
*/
1416-
scale = Max(scale, -NUMERIC_MAX_RESULT_SCALE);
1417-
scale = Min(scale, NUMERIC_MAX_RESULT_SCALE);
1428+
scale = Max(scale, -(NUMERIC_WEIGHT_MAX + 1) * DEC_DIGITS - 1);
1429+
scale = Min(scale, NUMERIC_DSCALE_MAX);
14181430

14191431
/*
14201432
* Unpack the argument and round it at the proper digit position
@@ -1460,10 +1472,13 @@ numeric_trunc(PG_FUNCTION_ARGS)
14601472
PG_RETURN_NUMERIC(duplicate_numeric(num));
14611473

14621474
/*
1463-
* Limit the scale value to avoid possible overflow in calculations
1475+
* Limit the scale value to avoid possible overflow in calculations.
1476+
*
1477+
* These limits are based on the maximum number of digits a Numeric value
1478+
* can have before and after the decimal point.
14641479
*/
1465-
scale = Max(scale, -NUMERIC_MAX_RESULT_SCALE);
1466-
scale = Min(scale, NUMERIC_MAX_RESULT_SCALE);
1480+
scale = Max(scale, -(NUMERIC_WEIGHT_MAX + 1) * DEC_DIGITS);
1481+
scale = Min(scale, NUMERIC_DSCALE_MAX);
14671482

14681483
/*
14691484
* Unpack the argument and truncate it at the proper digit position
@@ -10266,7 +10281,8 @@ power_var(const NumericVar *base, const NumericVar *exp, NumericVar *result)
1026610281
/*
1026710282
* Set the scale for the low-precision calculation, computing ln(base) to
1026810283
* around 8 significant digits. Note that ln_dweight may be as small as
10269-
* -SHRT_MAX, so the scale may exceed NUMERIC_MAX_DISPLAY_SCALE here.
10284+
* -NUMERIC_DSCALE_MAX, so the scale may exceed NUMERIC_MAX_DISPLAY_SCALE
10285+
* here.
1027010286
*/
1027110287
local_rscale = 8 - ln_dweight;
1027210288
local_rscale = Max(local_rscale, NUMERIC_MIN_DISPLAY_SCALE);
@@ -10406,7 +10422,7 @@ power_var_int(const NumericVar *base, int exp, NumericVar *result, int rscale)
1040610422
* Apply crude overflow/underflow tests so we can exit early if the result
1040710423
* certainly will overflow/underflow.
1040810424
*/
10409-
if (f > 3 * SHRT_MAX * DEC_DIGITS)
10425+
if (f > 3 * NUMERIC_WEIGHT_MAX * DEC_DIGITS)
1041010426
ereport(ERROR,
1041110427
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
1041210428
errmsg("value overflows numeric format")));
@@ -10476,7 +10492,8 @@ power_var_int(const NumericVar *base, int exp, NumericVar *result, int rscale)
1047610492
* int16, the final result is guaranteed to overflow (or underflow, if
1047710493
* exp < 0), so we can give up before wasting too many cycles.
1047810494
*/
10479-
if (base_prod.weight > SHRT_MAX || result->weight > SHRT_MAX)
10495+
if (base_prod.weight > NUMERIC_WEIGHT_MAX ||
10496+
result->weight > NUMERIC_WEIGHT_MAX)
1048010497
{
1048110498
/* overflow, unless neg, in which case result should be 0 */
1048210499
if (!neg)

‎src/test/regress/expected/numeric.out

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1346,6 +1346,108 @@ FROM generate_series(-5,5) AS t(i);
13461346
5 | -300000 | -200000 | -100000 | 100000 | 200000 | 300000
13471347
(11 rows)
13481348

1349+
-- Check limits of rounding before the decimal point
1350+
SELECT round(4.4e131071, -131071) = 4e131071;
1351+
?column?
1352+
----------
1353+
t
1354+
(1 row)
1355+
1356+
SELECT round(4.5e131071, -131071) = 5e131071;
1357+
?column?
1358+
----------
1359+
t
1360+
(1 row)
1361+
1362+
SELECT round(4.5e131071, -131072); -- loses all digits
1363+
round
1364+
-------
1365+
0
1366+
(1 row)
1367+
1368+
SELECT round(5.5e131071, -131072); -- rounds up and overflows
1369+
ERROR: value overflows numeric format
1370+
SELECT round(5.5e131071, -131073); -- loses all digits
1371+
round
1372+
-------
1373+
0
1374+
(1 row)
1375+
1376+
SELECT round(5.5e131071, -1000000); -- loses all digits
1377+
round
1378+
-------
1379+
0
1380+
(1 row)
1381+
1382+
-- Check limits of rounding after the decimal point
1383+
SELECT round(5e-16383, 1000000) = 5e-16383;
1384+
?column?
1385+
----------
1386+
t
1387+
(1 row)
1388+
1389+
SELECT round(5e-16383, 16383) = 5e-16383;
1390+
?column?
1391+
----------
1392+
t
1393+
(1 row)
1394+
1395+
SELECT round(5e-16383, 16382) = 1e-16382;
1396+
?column?
1397+
----------
1398+
t
1399+
(1 row)
1400+
1401+
SELECT round(5e-16383, 16381) = 0;
1402+
?column?
1403+
----------
1404+
t
1405+
(1 row)
1406+
1407+
-- Check limits of trunc() before the decimal point
1408+
SELECT trunc(9.9e131071, -131071) = 9e131071;
1409+
?column?
1410+
----------
1411+
t
1412+
(1 row)
1413+
1414+
SELECT trunc(9.9e131071, -131072); -- loses all digits
1415+
trunc
1416+
-------
1417+
0
1418+
(1 row)
1419+
1420+
SELECT trunc(9.9e131071, -131073); -- loses all digits
1421+
trunc
1422+
-------
1423+
0
1424+
(1 row)
1425+
1426+
SELECT trunc(9.9e131071, -1000000); -- loses all digits
1427+
trunc
1428+
-------
1429+
0
1430+
(1 row)
1431+
1432+
-- Check limits of trunc() after the decimal point
1433+
SELECT trunc(5e-16383, 1000000) = 5e-16383;
1434+
?column?
1435+
----------
1436+
t
1437+
(1 row)
1438+
1439+
SELECT trunc(5e-16383, 16383) = 5e-16383;
1440+
?column?
1441+
----------
1442+
t
1443+
(1 row)
1444+
1445+
SELECT trunc(5e-16383, 16382) = 0;
1446+
?column?
1447+
----------
1448+
t
1449+
(1 row)
1450+
13491451
-- Testing for width_bucket(). For convenience, we test both the
13501452
-- numeric and float8 versions of the function in this file.
13511453
-- errors

‎src/test/regress/sql/numeric.sql

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -833,6 +833,31 @@ SELECT i as pow,
833833
round((2.5 * 10 ^ i)::numeric, -i)
834834
FROM generate_series(-5,5) AS t(i);
835835

836+
-- Check limits of rounding before the decimal point
837+
SELECT round(4.4e131071, -131071) = 4e131071;
838+
SELECT round(4.5e131071, -131071) = 5e131071;
839+
SELECT round(4.5e131071, -131072); -- loses all digits
840+
SELECT round(5.5e131071, -131072); -- rounds up and overflows
841+
SELECT round(5.5e131071, -131073); -- loses all digits
842+
SELECT round(5.5e131071, -1000000); -- loses all digits
843+
844+
-- Check limits of rounding after the decimal point
845+
SELECT round(5e-16383, 1000000) = 5e-16383;
846+
SELECT round(5e-16383, 16383) = 5e-16383;
847+
SELECT round(5e-16383, 16382) = 1e-16382;
848+
SELECT round(5e-16383, 16381) = 0;
849+
850+
-- Check limits of trunc() before the decimal point
851+
SELECT trunc(9.9e131071, -131071) = 9e131071;
852+
SELECT trunc(9.9e131071, -131072); -- loses all digits
853+
SELECT trunc(9.9e131071, -131073); -- loses all digits
854+
SELECT trunc(9.9e131071, -1000000); -- loses all digits
855+
856+
-- Check limits of trunc() after the decimal point
857+
SELECT trunc(5e-16383, 1000000) = 5e-16383;
858+
SELECT trunc(5e-16383, 16383) = 5e-16383;
859+
SELECT trunc(5e-16383, 16382) = 0;
860+
836861
-- Testing for width_bucket(). For convenience, we test both the
837862
-- numeric and float8 versions of the function in this file.
838863

0 commit comments

Comments
 (0)