Skip to content

Commit 81b29c8

Browse files
tglsfdctglsfdc
committed
Fix loss of fractional digits for large values in cash_numeric().
Money values exceeding about 18 digits (depending on lc_monetary) could be inaccurately converted to numeric, due to select_div_scale() deciding it didn't need to compute any fractional digits. Force its hand by setting the dscale of one division input to equal the number of fractional digits we need. In passing, rearrange the logic to not do useless work in locales where money values are considered integral. Per bug #15925 from Slawomir Chodnicki. Back-patch to all supported branches. Discussion: https://postgr.es/m/15925-da9953e2674bb5c8@postgresql.org
1 parent 7ea91ae commit 81b29c8

File tree

3 files changed

+89
-22
lines changed

3 files changed

+89
-22
lines changed

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

Lines changed: 38 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -987,36 +987,54 @@ Datum
987987
cash_numeric(PG_FUNCTION_ARGS)
988988
{
989989
Cash money = PG_GETARG_CASH(0);
990-
Numeric result;
990+
Datum result;
991991
int fpoint;
992-
int64 scale;
993-
int i;
994-
Datum amount;
995-
Datum numeric_scale;
996-
Datum quotient;
997992
struct lconv *lconvert = PGLC_localeconv();
998993

999994
/* see comments about frac_digits in cash_in() */
1000995
fpoint = lconvert->frac_digits;
1001996
if (fpoint < 0 || fpoint > 10)
1002997
fpoint = 2;
1003998

1004-
/* compute required scale factor */
1005-
scale = 1;
1006-
for (i = 0; i < fpoint; i++)
1007-
scale *= 10;
1008-
1009-
/* form the result as money / scale */
1010-
amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
1011-
numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
1012-
quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale);
999+
/* convert the integral money value to numeric */
1000+
result = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
10131001

1014-
/* forcibly round to exactly the intended number of digits */
1015-
result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
1016-
quotient,
1017-
Int32GetDatum(fpoint)));
1002+
/* scale appropriately, if needed */
1003+
if (fpoint > 0)
1004+
{
1005+
int64 scale;
1006+
int i;
1007+
Datum numeric_scale;
1008+
Datum quotient;
1009+
1010+
/* compute required scale factor */
1011+
scale = 1;
1012+
for (i = 0; i < fpoint; i++)
1013+
scale *= 10;
1014+
numeric_scale = DirectFunctionCall1(int8_numeric,
1015+
Int64GetDatum(scale));
1016+
1017+
/*
1018+
* Given integral inputs approaching INT64_MAX, select_div_scale()
1019+
* might choose a result scale of zero, causing loss of fractional
1020+
* digits in the quotient. We can ensure an exact result by setting
1021+
* the dscale of either input to be at least as large as the desired
1022+
* result scale. numeric_round() will do that for us.
1023+
*/
1024+
numeric_scale = DirectFunctionCall2(numeric_round,
1025+
numeric_scale,
1026+
Int32GetDatum(fpoint));
1027+
1028+
/* Now we can safely divide ... */
1029+
quotient = DirectFunctionCall2(numeric_div, result, numeric_scale);
1030+
1031+
/* ... and forcibly round to exactly the intended number of digits */
1032+
result = DirectFunctionCall2(numeric_round,
1033+
quotient,
1034+
Int32GetDatum(fpoint));
1035+
}
10181036

1019-
PG_RETURN_NUMERIC(result);
1037+
PG_RETURN_DATUM(result);
10201038
}
10211039

10221040
/* numeric_cash()

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

Lines changed: 40 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,8 @@
11
--
22
-- MONEY
33
--
4+
-- Note that we assume lc_monetary has been set to C.
5+
--
46
CREATE TABLE money_data (m money);
57
INSERT INTO money_data VALUES ('123');
68
SELECT * FROM money_data;
@@ -223,7 +225,7 @@ SELECT '90000000000000099.00'::money / 10::smallint;
223225
$9,000,000,000,000,009.90
224226
(1 row)
225227

226-
-- Cast int4/int8 to money
228+
-- Cast int4/int8/numeric to money
227229
SELECT 1234567890::money;
228230
money
229231
-------------------
@@ -274,6 +276,12 @@ SELECT 12345678901234567::int8::money;
274276
$12,345,678,901,234,567.00
275277
(1 row)
276278

279+
SELECT 12345678901234567::numeric::money;
280+
money
281+
----------------------------
282+
$12,345,678,901,234,567.00
283+
(1 row)
284+
277285
SELECT (-1234567890)::int4::money;
278286
money
279287
--------------------
@@ -286,3 +294,34 @@ SELECT (-12345678901234567)::int8::money;
286294
-$12,345,678,901,234,567.00
287295
(1 row)
288296

297+
SELECT (-12345678901234567)::numeric::money;
298+
money
299+
-----------------------------
300+
-$12,345,678,901,234,567.00
301+
(1 row)
302+
303+
-- Cast from money to numeric
304+
SELECT '12345678901234567'::money::numeric;
305+
numeric
306+
----------------------
307+
12345678901234567.00
308+
(1 row)
309+
310+
SELECT '-12345678901234567'::money::numeric;
311+
numeric
312+
-----------------------
313+
-12345678901234567.00
314+
(1 row)
315+
316+
SELECT '92233720368547758.07'::money::numeric;
317+
numeric
318+
----------------------
319+
92233720368547758.07
320+
(1 row)
321+
322+
SELECT '-92233720368547758.08'::money::numeric;
323+
numeric
324+
-----------------------
325+
-92233720368547758.08
326+
(1 row)
327+

‎src/test/regress/sql/money.sql

Lines changed: 11 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,8 @@
11
--
22
-- MONEY
33
--
4+
-- Note that we assume lc_monetary has been set to C.
5+
--
46

57
CREATE TABLE money_data (m money);
68

@@ -67,7 +69,7 @@ SELECT '878.08'::money / 11::smallint;
6769
SELECT '90000000000000099.00'::money / 10::int;
6870
SELECT '90000000000000099.00'::money / 10::smallint;
6971

70-
-- Cast int4/int8 to money
72+
-- Cast int4/int8/numeric to money
7173
SELECT 1234567890::money;
7274
SELECT 12345678901234567::money;
7375
SELECT 123456789012345678::money;
@@ -79,5 +81,13 @@ SELECT (-123456789012345678)::money;
7981
SELECT (-9223372036854775808)::money;
8082
SELECT 1234567890::int4::money;
8183
SELECT 12345678901234567::int8::money;
84+
SELECT 12345678901234567::numeric::money;
8285
SELECT (-1234567890)::int4::money;
8386
SELECT (-12345678901234567)::int8::money;
87+
SELECT (-12345678901234567)::numeric::money;
88+
89+
-- Cast from money to numeric
90+
SELECT '12345678901234567'::money::numeric;
91+
SELECT '-12345678901234567'::money::numeric;
92+
SELECT '92233720368547758.07'::money::numeric;
93+
SELECT '-92233720368547758.08'::money::numeric;

0 commit comments

Comments
 (0)