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

diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index d329efaa9a8007efaf5edb806740ede75e2ec141..319dfbba4ec59d18f522f99eab9f3dff4886cebe 100644 (file)
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -987,13 +987,8 @@ Datum
 cash_numeric(PG_FUNCTION_ARGS)
 {
    Cash        money = PG_GETARG_CASH(0);
-   Numeric     result;
+   Datum       result;
    int         fpoint;
-   int64       scale;
-   int         i;
-   Datum       amount;
-   Datum       numeric_scale;
-   Datum       quotient;
    struct lconv *lconvert = PGLC_localeconv();
 
    /* see comments about frac_digits in cash_in() */
@@ -1001,22 +996,45 @@ cash_numeric(PG_FUNCTION_ARGS)
    if (fpoint < 0 || fpoint > 10)
        fpoint = 2;
 
-   /* compute required scale factor */
-   scale = 1;
-   for (i = 0; i < fpoint; i++)
-       scale *= 10;
-
-   /* form the result as money / scale */
-   amount = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
-   numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
-   quotient = DirectFunctionCall2(numeric_div, amount, numeric_scale);
+   /* convert the integral money value to numeric */
+   result = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
 
-   /* forcibly round to exactly the intended number of digits */
-   result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
-                                                quotient,
-                                                Int32GetDatum(fpoint)));
+   /* scale appropriately, if needed */
+   if (fpoint > 0)
+   {
+       int64       scale;
+       int         i;
+       Datum       numeric_scale;
+       Datum       quotient;
+
+       /* compute required scale factor */
+       scale = 1;
+       for (i = 0; i < fpoint; i++)
+           scale *= 10;
+       numeric_scale = DirectFunctionCall1(int8_numeric,
+                                           Int64GetDatum(scale));
+
+       /*
+        * Given integral inputs approaching INT64_MAX, select_div_scale()
+        * might choose a result scale of zero, causing loss of fractional
+        * digits in the quotient.  We can ensure an exact result by setting
+        * the dscale of either input to be at least as large as the desired
+        * result scale.  numeric_round() will do that for us.
+        */
+       numeric_scale = DirectFunctionCall2(numeric_round,
+                                           numeric_scale,
+                                           Int32GetDatum(fpoint));
+
+       /* Now we can safely divide ... */
+       quotient = DirectFunctionCall2(numeric_div, result, numeric_scale);
+
+       /* ... and forcibly round to exactly the intended number of digits */
+       result = DirectFunctionCall2(numeric_round,
+                                    quotient,
+                                    Int32GetDatum(fpoint));
+   }
 
-   PG_RETURN_NUMERIC(result);
+   PG_RETURN_DATUM(result);
 }
 
 /* numeric_cash()

diff --git a/src/test/regress/expected/money.out b/src/test/regress/expected/money.out
index efb48f1acd6b3fe6b4237232cd31da148433748d..3911da65a23fb7ecaf4683e00aee9e6eb8090373 100644 (file)
--- a/src/test/regress/expected/money.out
+++ b/src/test/regress/expected/money.out
@@ -1,6 +1,8 @@
 --
 -- MONEY
 --
+-- Note that we assume lc_monetary has been set to C.
+--
 CREATE TABLE money_data (m money);
 INSERT INTO money_data VALUES ('123');
 SELECT * FROM money_data;
@@ -223,7 +225,7 @@ SELECT '90000000000000099.00'::money / 10::smallint;
  $9,000,000,000,000,009.90
 (1 row)
 
--- Cast int4/int8 to money
+-- Cast int4/int8/numeric to money
 SELECT 1234567890::money;
        money       
 -------------------
@@ -274,6 +276,12 @@ SELECT 12345678901234567::int8::money;
  $12,345,678,901,234,567.00
 (1 row)
 
+SELECT 12345678901234567::numeric::money;
+           money            
+----------------------------
+ $12,345,678,901,234,567.00
+(1 row)
+
 SELECT (-1234567890)::int4::money;
        money        
 --------------------
@@ -286,3 +294,34 @@ SELECT (-12345678901234567)::int8::money;
  -$12,345,678,901,234,567.00
 (1 row)
 
+SELECT (-12345678901234567)::numeric::money;
+            money            
+-----------------------------
+ -$12,345,678,901,234,567.00
+(1 row)
+
+-- Cast from money to numeric
+SELECT '12345678901234567'::money::numeric;
+       numeric        
+----------------------
+ 12345678901234567.00
+(1 row)
+
+SELECT '-12345678901234567'::money::numeric;
+        numeric        
+-----------------------
+ -12345678901234567.00
+(1 row)
+
+SELECT '92233720368547758.07'::money::numeric;
+       numeric        
+----------------------
+ 92233720368547758.07
+(1 row)
+
+SELECT '-92233720368547758.08'::money::numeric;
+        numeric        
+-----------------------
+ -92233720368547758.08
+(1 row)
+

diff --git a/src/test/regress/sql/money.sql b/src/test/regress/sql/money.sql
index f0db5fa4322abdd075d82390556ca974eeb993f6..f49db0d21df84728e8532609cb75e3046bf14435 100644 (file)
--- a/src/test/regress/sql/money.sql
+++ b/src/test/regress/sql/money.sql
@@ -1,6 +1,8 @@
 --
 -- MONEY
 --
+-- Note that we assume lc_monetary has been set to C.
+--
 
 CREATE TABLE money_data (m money);
 
@@ -67,7 +69,7 @@ SELECT '878.08'::money / 11::smallint;
 SELECT '90000000000000099.00'::money / 10::int;
 SELECT '90000000000000099.00'::money / 10::smallint;
 
--- Cast int4/int8 to money
+-- Cast int4/int8/numeric to money
 SELECT 1234567890::money;
 SELECT 12345678901234567::money;
 SELECT 123456789012345678::money;
@@ -79,5 +81,13 @@ SELECT (-123456789012345678)::money;
 SELECT (-9223372036854775808)::money;
 SELECT 1234567890::int4::money;
 SELECT 12345678901234567::int8::money;
+SELECT 12345678901234567::numeric::money;
 SELECT (-1234567890)::int4::money;
 SELECT (-12345678901234567)::int8::money;
+SELECT (-12345678901234567)::numeric::money;
+
+-- Cast from money to numeric
+SELECT '12345678901234567'::money::numeric;
+SELECT '-12345678901234567'::money::numeric;
+SELECT '92233720368547758.07'::money::numeric;
+SELECT '-92233720368547758.08'::money::numeric;