pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #15925: Loss of precision converting money to numeric
3+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2019-07-26 b9d2c5c7ac 01e0538e8b ca918f99aa 30bed9f638 13e493cf68 81b29c8711 fa854112fc Fix loss of fractional digits for large values in cash_numeric().
* BUG #15925: Loss of precision converting money to numeric
@ 2019-07-26 07:39 PG Bug reporting form <noreply@postgresql.org>
2019-07-26 15:17 ` Re: BUG #15925: Loss of precision converting money to numeric Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2019-07-26 07:39 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: slawomir.chodnicki@gmail.com
The following bug has been logged on the website:
Bug reference: 15925
Logged by: Slawomir Chodnicki
Email address: slawomir.chodnicki@gmail.com
PostgreSQL version: 11.4
Operating system: Linux
Description:
As per documentation converting a money value to numeric does not lose
precision. In general that is the case:
select '8547758.07'::money, '8547758.07'::money::numeric(30,2);
money |numeric |
-------------|----------|
$8,547,758.07|8547758.07|
During my testing I found unexpected results for the min and max value of
the money type.
select '-92233720368547758.08'::money,
'-92233720368547758.08'::money::numeric(30,2);
money |numeric |
---------------------------|---------------------|
-$92,233,720,368,547,758.08|-92233720368547758.00|
Note that the cent value is gone after converting to numeric.
Same issue for the max value:
money |numeric |
--------------------------|--------------------|
$92,233,720,368,547,758.07|92233720368547758.00|
Best Wishes
Slawo
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #15925: Loss of precision converting money to numeric
2019-07-26 07:39 BUG #15925: Loss of precision converting money to numeric PG Bug reporting form <noreply@postgresql.org>
@ 2019-07-26 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-26 16:15 ` Re: BUG #15925: Loss of precision converting money to numeric Slawomir Chodnicki <slawomir.chodnicki@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2019-07-26 15:17 UTC (permalink / raw)
To: slawomir.chodnicki@gmail.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> During my testing I found unexpected results for the min and max value of
> the money type.
> select '-92233720368547758.08'::money,
> '-92233720368547758.08'::money::numeric(30,2);
> money |numeric |
> ---------------------------|---------------------|
> -$92,233,720,368,547,758.08|-92233720368547758.00|
> Note that the cent value is gone after converting to numeric.
> Same issue for the max value:
> money |numeric |
> --------------------------|--------------------|
> $92,233,720,368,547,758.07|92233720368547758.00|
Hmm, yeah, anything approaching INT64_MAX has a problem.
The issue is that cash_numeric() does the equivalent of
SELECT 9223372036854775807::numeric / 100::numeric;
and if you try that by hand you indeed get
92233720368547758
because select_div_scale() has decided that it need not produce
any fractional digits. We can force its hand by making the input
have the required number of fractional digits *before* dividing,
which is a bit weird on its face but gets the job done, per the
comment therein:
* The result scale of a division isn't specified in any SQL standard. For
* PostgreSQL we select a result scale that will give at least
* NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
* result no less accurate than float8; but use a scale not less than
* either input's display scale.
(NUMERIC_MIN_SIG_DIGITS is 16, whence the problem for a 17-digit result.
Maybe we should consider raising that, but I'm hesitant to consider such
a far-reaching change just to make cash_numeric happy.)
I intend to apply the attached patch.
regards, tom lane
Attachments:
fix-cash_numeric-for-large-values.patch (text/x-diff, 4.0K) download
diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index c92e9d5..800472a 100644
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -1032,13 +1032,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() */
@@ -1046,22 +1041,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);
+ result = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
+
+ /* 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));
- /* forcibly round to exactly the intended number of digits */
- result = DatumGetNumeric(DirectFunctionCall2(numeric_round,
- quotient,
- 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 ab86595..fc71a72 100644
--- 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;
@@ -476,7 +478,7 @@ SELECT (-12345678901234567)::numeric::money;
-$12,345,678,901,234,567.00
(1 row)
--- Cast from money
+-- Cast from money to numeric
SELECT '12345678901234567'::money::numeric;
numeric
----------------------
@@ -489,3 +491,15 @@ SELECT '-12345678901234567'::money::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 37b9ecc..5e74628 100644
--- 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);
@@ -122,6 +124,8 @@ SELECT (-1234567890)::int4::money;
SELECT (-12345678901234567)::int8::money;
SELECT (-12345678901234567)::numeric::money;
--- Cast from money
+-- Cast from money to numeric
SELECT '12345678901234567'::money::numeric;
SELECT '-12345678901234567'::money::numeric;
+SELECT '92233720368547758.07'::money::numeric;
+SELECT '-92233720368547758.08'::money::numeric;
* Re: BUG #15925: Loss of precision converting money to numeric
2019-07-26 07:39 BUG #15925: Loss of precision converting money to numeric PG Bug reporting form <noreply@postgresql.org>
2019-07-26 15:17 ` Re: BUG #15925: Loss of precision converting money to numeric Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-07-26 16:15 ` Slawomir Chodnicki <slawomir.chodnicki@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Slawomir Chodnicki @ 2019-07-26 16:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs@lists.postgresql.org
> Hmm, yeah, anything approaching INT64_MAX has a problem.
> The issue is that cash_numeric() does the equivalent of
>
> SELECT 9223372036854775807::numeric / 100::numeric;
>
> and if you try that by hand you indeed get
>
> 92233720368547758
>
> because select_div_scale() has decided that it need not produce
> any fractional digits. We can force its hand by making the input
> have the required number of fractional digits *before* dividing,
> which is a bit weird on its face but gets the job done, per the
> comment therein:
>
> * The result scale of a division isn't specified in any SQL standard. For
> * PostgreSQL we select a result scale that will give at least
> * NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a
> * result no less accurate than float8; but use a scale not less than
> * either input's display scale.
>
> (NUMERIC_MIN_SIG_DIGITS is 16, whence the problem for a 17-digit result.
> Maybe we should consider raising that, but I'm hesitant to consider such
> a far-reaching change just to make cash_numeric happy.)
>
> I intend to apply the attached patch.
Thanks Tom,
the response is illuminating. And a same-day patch is legendary.
Thank you for your work.
Slawo
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-07-26 16:15 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-26 07:39 BUG #15925: Loss of precision converting money to numeric PG Bug reporting form <noreply@postgresql.org>
2019-07-26 15:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-07-26 16:15 ` Slawomir Chodnicki <slawomir.chodnicki@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox