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

Commitd761fe2

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 parent2dd510e commitd761fe2

File tree

4 files changed

+75
-8
lines changed

4 files changed

+75
-8
lines changed

‎doc/src/sgml/datatype.sgml

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

985985
<para>
986+
Division of a <type>money</type> value by an integer value is performed
987+
with truncation of the fractional part towards zero. To get a rounded
988+
result, divide by a floating-point value, or cast the <type>money</type>
989+
value to <type>numeric</> before dividing and back to <type>money</type>
990+
afterwards. (The latter is preferable to avoid risking precision loss.)
986991
When a <type>money</type> value is divided by another <type>money</type>
987992
value, the result is <type>double precision</type> (i.e., a pure number,
988993
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
@@ -667,7 +667,7 @@ cash_mul_flt8(PG_FUNCTION_ARGS)
667667
float8f=PG_GETARG_FLOAT8(1);
668668
Cashresult;
669669

670-
result=c*f;
670+
result=rint(c*f);
671671
PG_RETURN_CASH(result);
672672
}
673673

@@ -682,7 +682,7 @@ flt8_mul_cash(PG_FUNCTION_ARGS)
682682
Cashc=PG_GETARG_CASH(1);
683683
Cashresult;
684684

685-
result=f*c;
685+
result=rint(f*c);
686686
PG_RETURN_CASH(result);
687687
}
688688

@@ -717,7 +717,7 @@ cash_mul_flt4(PG_FUNCTION_ARGS)
717717
float4f=PG_GETARG_FLOAT4(1);
718718
Cashresult;
719719

720-
result=c*f;
720+
result=rint(c*(float8)f);
721721
PG_RETURN_CASH(result);
722722
}
723723

@@ -732,7 +732,7 @@ flt4_mul_cash(PG_FUNCTION_ARGS)
732732
Cashc=PG_GETARG_CASH(1);
733733
Cashresult;
734734

735-
result=f*c;
735+
result=rint((float8)f*c);
736736
PG_RETURN_CASH(result);
737737
}
738738

@@ -753,7 +753,7 @@ cash_div_flt4(PG_FUNCTION_ARGS)
753753
(errcode(ERRCODE_DIVISION_BY_ZERO),
754754
errmsg("division by zero")));
755755

756-
result=rint(c /f);
756+
result=rint(c /(float8)f);
757757
PG_RETURN_CASH(result);
758758
}
759759

@@ -802,7 +802,7 @@ cash_div_int8(PG_FUNCTION_ARGS)
802802
(errcode(ERRCODE_DIVISION_BY_ZERO),
803803
errmsg("division by zero")));
804804

805-
result=rint(c /i);
805+
result=c /i;
806806

807807
PG_RETURN_CASH(result);
808808
}
@@ -854,7 +854,7 @@ cash_div_int4(PG_FUNCTION_ARGS)
854854
(errcode(ERRCODE_DIVISION_BY_ZERO),
855855
errmsg("division by zero")));
856856

857-
result=rint(c /i);
857+
result=c /i;
858858

859859
PG_RETURN_CASH(result);
860860
}
@@ -904,7 +904,7 @@ cash_div_int2(PG_FUNCTION_ARGS)
904904
(errcode(ERRCODE_DIVISION_BY_ZERO),
905905
errmsg("division by zero")));
906906

907-
result=rint(c /s);
907+
result=c /s;
908908
PG_RETURN_CASH(result);
909909
}
910910

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

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -359,6 +359,56 @@ SELECT '92233720368547758.075'::money;
359359
ERROR: value "92233720368547758.075" is out of range for type money
360360
LINE 1: SELECT '92233720368547758.075'::money;
361361
^
362+
-- rounding vs. truncation in division
363+
SELECT '878.08'::money / 11::float8;
364+
?column?
365+
----------
366+
$79.83
367+
(1 row)
368+
369+
SELECT '878.08'::money / 11::float4;
370+
?column?
371+
----------
372+
$79.83
373+
(1 row)
374+
375+
SELECT '878.08'::money / 11::bigint;
376+
?column?
377+
----------
378+
$79.82
379+
(1 row)
380+
381+
SELECT '878.08'::money / 11::int;
382+
?column?
383+
----------
384+
$79.82
385+
(1 row)
386+
387+
SELECT '878.08'::money / 11::smallint;
388+
?column?
389+
----------
390+
$79.82
391+
(1 row)
392+
393+
-- check for precision loss in division
394+
SELECT '90000000000000099.00'::money / 10::bigint;
395+
?column?
396+
---------------------------
397+
$9,000,000,000,000,009.90
398+
(1 row)
399+
400+
SELECT '90000000000000099.00'::money / 10::int;
401+
?column?
402+
---------------------------
403+
$9,000,000,000,000,009.90
404+
(1 row)
405+
406+
SELECT '90000000000000099.00'::money / 10::smallint;
407+
?column?
408+
---------------------------
409+
$9,000,000,000,000,009.90
410+
(1 row)
411+
362412
-- Cast int4/int8/numeric to money
363413
SELECT 1234567890::money;
364414
money

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -97,6 +97,18 @@ SELECT '92233720368547758.08'::money;
9797
SELECT'-92233720368547758.085'::money;
9898
SELECT'92233720368547758.075'::money;
9999

100+
-- rounding vs. truncation in division
101+
SELECT'878.08'::money/11::float8;
102+
SELECT'878.08'::money/11::float4;
103+
SELECT'878.08'::money/11::bigint;
104+
SELECT'878.08'::money/11::int;
105+
SELECT'878.08'::money/11::smallint;
106+
107+
-- check for precision loss in division
108+
SELECT'90000000000000099.00'::money/10::bigint;
109+
SELECT'90000000000000099.00'::money/10::int;
110+
SELECT'90000000000000099.00'::money/10::smallint;
111+
100112
-- Cast int4/int8/numeric to money
101113
SELECT1234567890::money;
102114
SELECT12345678901234567::money;

0 commit comments

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