Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 12.4 \(3445.104.11\))
Subject: Re: BUG #15925: Loss of precision converting money to numeric
From: Slawomir Chodnicki <slawomir.chodnicki@gmail.com>
In-Reply-To: <12463.1564154254@sss.pgh.pa.us>
Date: Fri, 26 Jul 2019 18:15:00 +0200
Cc: pgsql-bugs@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <A6E2C5F9-7437-41B1-9F18-4E6C35981F6B@gmail.com>
References: <15925-da9953e2674bb5c8@postgresql.org>
 <12463.1564154254@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk


> Hmm, yeah, anything approaching INT64_MAX has a problem.
> The issue is that cash_numeric() does the equivalent of
>=20
> SELECT 9223372036854775807::numeric / 100::numeric;
>=20
> and if you try that by hand you indeed get
>=20
> 92233720368547758
>=20
> 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:
>=20
>     * 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.
>=20
> (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.)
>=20
> 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