Horton

Thread

Commits

  1. Fix loss of fractional digits for large values in cash_numeric().

  1. BUG #15925: Loss of precision converting money to numeric

    The Post Office <noreply@postgresql.org> — 2019-07-26T07:39:17Z

    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

  2. Re: BUG #15925: Loss of precision converting money to numeric

    Tom Lane <tgl@sss.pgh.pa.us> — 2019-07-26T15:17:34Z

    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

  3. Re: BUG #15925: Loss of precision converting money to numeric

    Slawomir Chodnicki <slawomir.chodnicki@gmail.com> — 2019-07-26T16:15:00Z

    
> 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