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

Commitb3c536b

Browse files
committed
Fix precision and rounding issues in money multiplication and division.
The cash_div_intX functions applied rint() to the result of the division.That's not merely useless (because the result is already an integer) butit causes precision loss for values larger than 2^52 or so, because ofthe forced conversion to float8.On the other hand, the cash_mul_fltX functions neglected to apply rint() totheir multiplication results, thus possibly causing off-by-one outputs.Per C standard, arithmetic between any integral value and a float value isperformed in float format. Thus, cash_mul_flt4 and cash_div_flt4 producedanswers good to only about six digits, even when the float value is exact.We can improve matters noticeably by widening the float inputs to double.(It's tempting to consider using "long double" arithmetic if available,but that's probably too much of a stretch for a back-patched fix.)Also, document that cash_div_intX operators truncate rather than round.Per bug #14663 from Richard Pistole. Back-patch to all supported branches.Discussion:https://postgr.es/m/22403.1495223615@sss.pgh.pa.us
1 parent038420a commitb3c536b

File tree

4 files changed

+61
-8
lines changed

4 files changed

+61
-8
lines changed

‎doc/src/sgml/datatype.sgml

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -952,6 +952,11 @@ SELECT '52093.89'::money::numeric::float8;
952952
</para>
953953

954954
<para>
955+
Division of a <type>money</type> value by an integer value is performed
956+
with truncation of the fractional part towards zero. To get a rounded
957+
result, divide by a floating-point value, or cast the <type>money</type>
958+
value to <type>numeric</> before dividing and back to <type>money</type>
959+
afterwards. (The latter is preferable to avoid risking precision loss.)
955960
When a <type>money</type> value is divided by another <type>money</type>
956961
value, the result is <type>double precision</type> (i.e., a pure number,
957962
not money); the currency units cancel each other out in the division.

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

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -623,7 +623,7 @@ cash_mul_flt8(PG_FUNCTION_ARGS)
623623
float8f=PG_GETARG_FLOAT8(1);
624624
Cashresult;
625625

626-
result=c*f;
626+
result=rint(c*f);
627627
PG_RETURN_CASH(result);
628628
}
629629

@@ -638,7 +638,7 @@ flt8_mul_cash(PG_FUNCTION_ARGS)
638638
Cashc=PG_GETARG_CASH(1);
639639
Cashresult;
640640

641-
result=f*c;
641+
result=rint(f*c);
642642
PG_RETURN_CASH(result);
643643
}
644644

@@ -673,7 +673,7 @@ cash_mul_flt4(PG_FUNCTION_ARGS)
673673
float4f=PG_GETARG_FLOAT4(1);
674674
Cashresult;
675675

676-
result=c*f;
676+
result=rint(c*(float8)f);
677677
PG_RETURN_CASH(result);
678678
}
679679

@@ -688,7 +688,7 @@ flt4_mul_cash(PG_FUNCTION_ARGS)
688688
Cashc=PG_GETARG_CASH(1);
689689
Cashresult;
690690

691-
result=f*c;
691+
result=rint((float8)f*c);
692692
PG_RETURN_CASH(result);
693693
}
694694

@@ -709,7 +709,7 @@ cash_div_flt4(PG_FUNCTION_ARGS)
709709
(errcode(ERRCODE_DIVISION_BY_ZERO),
710710
errmsg("division by zero")));
711711

712-
result=rint(c /f);
712+
result=rint(c /(float8)f);
713713
PG_RETURN_CASH(result);
714714
}
715715

@@ -758,7 +758,7 @@ cash_div_int8(PG_FUNCTION_ARGS)
758758
(errcode(ERRCODE_DIVISION_BY_ZERO),
759759
errmsg("division by zero")));
760760

761-
result=rint(c /i);
761+
result=c /i;
762762

763763
PG_RETURN_CASH(result);
764764
}
@@ -810,7 +810,7 @@ cash_div_int4(PG_FUNCTION_ARGS)
810810
(errcode(ERRCODE_DIVISION_BY_ZERO),
811811
errmsg("division by zero")));
812812

813-
result=rint(c /i);
813+
result=c /i;
814814

815815
PG_RETURN_CASH(result);
816816
}
@@ -860,7 +860,7 @@ cash_div_int2(PG_FUNCTION_ARGS)
860860
(errcode(ERRCODE_DIVISION_BY_ZERO),
861861
errmsg("division by zero")));
862862

863-
result=rint(c /s);
863+
result=c /s;
864864
PG_RETURN_CASH(result);
865865
}
866866

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

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -185,6 +185,44 @@ SELECT * FROM money_data;
185185
$123.46
186186
(1 row)
187187

188+
-- rounding vs. truncation in division
189+
SELECT '878.08'::money / 11::float8;
190+
?column?
191+
----------
192+
$79.83
193+
(1 row)
194+
195+
SELECT '878.08'::money / 11::float4;
196+
?column?
197+
----------
198+
$79.83
199+
(1 row)
200+
201+
SELECT '878.08'::money / 11::int;
202+
?column?
203+
----------
204+
$79.82
205+
(1 row)
206+
207+
SELECT '878.08'::money / 11::smallint;
208+
?column?
209+
----------
210+
$79.82
211+
(1 row)
212+
213+
-- check for precision loss in division
214+
SELECT '90000000000000099.00'::money / 10::int;
215+
?column?
216+
---------------------------
217+
$9,000,000,000,000,009.90
218+
(1 row)
219+
220+
SELECT '90000000000000099.00'::money / 10::smallint;
221+
?column?
222+
---------------------------
223+
$9,000,000,000,000,009.90
224+
(1 row)
225+
188226
-- Cast int4/int8 to money
189227
SELECT 1234567890::money;
190228
money

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,16 @@ DELETE FROM money_data;
5757
INSERT INTO money_dataVALUES ('$123.459');
5858
SELECT*FROM money_data;
5959

60+
-- rounding vs. truncation in division
61+
SELECT'878.08'::money/11::float8;
62+
SELECT'878.08'::money/11::float4;
63+
SELECT'878.08'::money/11::int;
64+
SELECT'878.08'::money/11::smallint;
65+
66+
-- check for precision loss in division
67+
SELECT'90000000000000099.00'::money/10::int;
68+
SELECT'90000000000000099.00'::money/10::smallint;
69+
6070
-- Cast int4/int8 to money
6171
SELECT1234567890::money;
6272
SELECT12345678901234567::money;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp