8000 Fix precision and rounding issues in money multiplication and division. · dinesh372/postgres@b3c536b · GitHub
[go: up one dir, main page]
Skip to content
8000

Commit b3c536b

Browse files
tglsfdctglsfdc
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) but it causes precision loss for values larger than 2^52 or so, because of the forced conversion to float8. On the other hand, the cash_mul_fltX functions neglected to apply rint() to their multiplication results, thus possibly causing off-by-one outputs. Per C standard, arithmetic between any integral value and a float value is performed in float format. Thus, cash_mul_flt4 and cash_div_flt4 produced answers 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 parent 038420a commit b3c536b

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
float8 f = PG_GETARG_FLOAT8(1);
624624
Cash result;
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
Cash c = PG_GETARG_CASH(1);
639639
Cash result;
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
float4 f = PG_GETARG_FLOAT4(1);
674674
Cash result;
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
Cash c = PG_GETARG_CASH(1);
689689
Cash result;
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_data VALUES ('$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
SELECT 1234567890::money;
6272
SELECT 12345678901234567::money;

0 commit comments

Comments
 (0)
0