Skip to content

Commit 47ca912

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 467d77b commit 47ca912

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
@@ -250,6 +250,13 @@ struct NumericData
250250
| ((n)->choice.n_short.n_header & NUMERIC_SHORT_WEIGHT_MASK)) \
251251
: ((n)->choice.n_long.n_weight))
252252

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

14751482
/*
1476-
* Limit the scale value to avoid possible overflow in calculations
1483+
* Limit the scale value to avoid possible overflow in calculations.
1484+
*
1485+
* These limits are based on the maximum number of digits a Numeric value
1486+
* can have before and after the decimal point, but we must allow for one
1487+
* extra digit before the decimal point, in case the most significant
1488+
* digit rounds up; we must check if that causes Numeric overflow.
14771489
*/
1478-
scale = Max(scale, -NUMERIC_MAX_RESULT_SCALE);
1479-
scale = Min(scale, NUMERIC_MAX_RESULT_SCALE);
1490+
scale = Max(scale, -(NUMERIC_WEIGHT_MAX + 1) * DEC_DIGITS - 1);
1491+
scale = Min(scale, NUMERIC_DSCALE_MAX);
14801492

14811493
/*
14821494
* Unpack the argument and round it at the proper digit position
@@ -1522,10 +1534,13 @@ numeric_trunc(PG_FUNCTION_ARGS)
15221534
PG_RETURN_NUMERIC(duplicate_numeric(num));
15231535

15241536
/*
1525-
* Limit the scale value to avoid possible overflow in calculations
1537+
* Limit the scale value to avoid possible overflow in calculations.
1538+
*
1539+
* These limits are based on the maximum number of digits a Numeric value
1540+
* can have before and after the decimal point.
15261541
*/
1527-
scale = Max(scale, -NUMERIC_MAX_RESULT_SCALE);
1528-
scale = Min(scale, NUMERIC_MAX_RESULT_SCALE);
1542+
scale = Max(scale, -(NUMERIC_WEIGHT_MAX + 1) * DEC_DIGITS);
1543+
scale = Min(scale, NUMERIC_DSCALE_MAX);
15291544

15301545
/*
15311546
* Unpack the argument and truncate it at the proper digit position
@@ -10443,7 +10458,8 @@ power_var(const NumericVar *base, const NumericVar *exp, NumericVar *result)
1044310458
/*
1044410459
* Set the scale for the low-precision calculation, computing ln(base) to
1044510460
* around 8 significant digits. Note that ln_dweight may be as small as
10446-
* -SHRT_MAX, so the scale may exceed NUMERIC_MAX_DISPLAY_SCALE here.
10461+
* -NUMERIC_DSCALE_MAX, so the scale may exceed NUMERIC_MAX_DISPLAY_SCALE
10462+
* here.
1044710463
*/
1044810464
local_rscale = 8 - ln_dweight;
1044910465
local_rscale = Max(local_rscale, NUMERIC_MIN_DISPLAY_SCALE);
@@ -10583,7 +10599,7 @@ power_var_int(const NumericVar *base, int exp, NumericVar *result, int rscale)
1058310599
* Apply crude overflow/underflow tests so we can exit early if the result
1058410600
* certainly will overflow/underflow.
1058510601
*/
10586-
if (f > 3 * SHRT_MAX * DEC_DIGITS)
10602+
if (f > 3 * NUMERIC_WEIGHT_MAX * DEC_DIGITS)
1058710603
ereport(ERROR,
1058810604
(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
1058910605
errmsg("value overflows numeric format")));
@@ -10653,7 +10669,8 @@ power_var_int(const NumericVar *base, int exp, NumericVar *result, int rscale)
1065310669
* int16, the final result is guaranteed to overflow (or underflow, if
1065410670
* exp < 0), so we can give up before wasting too many cycles.
1065510671
*/
10656-
if (base_prod.weight > SHRT_MAX || result->weight > SHRT_MAX)
10672+
if (base_prod.weight > NUMERIC_WEIGHT_MAX ||
10673+
result->weight > NUMERIC_WEIGHT_MAX)
1065710674
{
1065810675
/* overflow, unless neg, in which case result should be 0 */
1065910676
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)