Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit81b29c8

Browse files
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. Forceits hand by setting the dscale of one division input to equal thenumber of fractional digits we need.In passing, rearrange the logic to not do useless work in localeswhere money values are considered integral.Per bug #15925 from Slawomir Chodnicki. Back-patch to all supportedbranches.Discussion:https://postgr.es/m/15925-da9953e2674bb5c8@postgresql.org
1 parent7ea91ae commit81b29c8

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
Cashmoney=PG_GETARG_CASH(0);
990-
Numericresult;
990+
Datumresult;
991991
intfpoint;
992-
int64scale;
993-
inti;
994-
Datumamount;
995-
Datumnumeric_scale;
996-
Datumquotient;
997992
structlconv*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+
int64scale;
1006+
inti;
1007+
Datumnumeric_scale;
1008+
Datumquotient;
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
CREATETABLEmoney_data (mmoney);
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
SELECT1234567890::money;
7274
SELECT12345678901234567::money;
7375
SELECT123456789012345678::money;
@@ -79,5 +81,13 @@ SELECT (-123456789012345678)::money;
7981
SELECT (-9223372036854775808)::money;
8082
SELECT1234567890::int4::money;
8183
SELECT12345678901234567::int8::money;
84+
SELECT12345678901234567::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)
[8]ページ先頭
©2009-2025 Movatter.jp