Re: BUG #12053: Strange behavior for numeric types with unspecified precision-scale - Mailing list pgsql-bugs

The strange fact is that if you try to write 0.000001, it gets displayed
correctly

I'd agree with your proposed solution, if I only set a dscale of 2.
But I didn't. The column type was set as just numeric.
The documentation for numeric says:

Specifying:

NUMERIC

without any precision or scale creates a column in which numeric values of
any precision and scale can be stored, up to the implementation limit on
precision. A column of this kind will not coerce input values to any
particular scale, whereas numeric columns with a declared scale will coerce
input values to that scale.

So I'd expect a "just numeric" column to contain almost everything!
And this is what I see for almost every value, but 0.001.
As I said before 0.000001 gets displayed correctly and 0.0009895846 do as
well!

*Tommaso Sala*
*Developer*
------------------------------

*Computer Line Associates*
www.cla-it.eu

Via della Viggioletta, 8
29121 Piacenza, Italy
tel. +39 (0523) 1865038



*Nota di riservatezza:* Il presente messaggio, corredato dei relativi
allegati, contiene informazioni da considerarsi strettamente riservate, ed
=C3=A8 destinato esclusivamente al destinatario sopra indicato, il quale =
=C3=A8
l'unico autorizzato ad usarlo, copiarlo e, sotto la propria responsabilit=
=C3=A0,
diffonderlo. Chiunque ricevesse questo messaggio per errore o comunque lo
leggesse senza esserne legittimato =C3=A8 avvertito che trattenerlo, copiar=
lo,
divulgarlo, distribuirlo a persone diverse dal destinatario =C3=A8 severame=
nte
proibito, ed =C3=A8 pregato di rinviarlo immediatamente al mittente
distruggendone l'originale. Grazie.

*Confidentiality notice:* This message, together with its attachments,
contains strictly confidential information and is intended only for the
addressee identified above, who is the sole party authorized to use and
copy it and, assuming any related liability, to forward it to
others. Anyone receiving this message by mistake or reading it without
authorization is hereby notified that storage, reproduction, disclosure
or distribution of the message to persons other than the addressee is
strictly forbidden. They are asked to return the message immediately to the
sender and to erase the original message received. Thank you.

Il giorno 29/nov/2014, alle ore 03:33, Tom Lane <tgl@sss.pgh.pa.us> ha
scritto:

I wrote:

So the question is whether it's worth adding logic to numeric_recv

to guard against bogus dscale values.  I think that detecting this

case would probably add a noticeable number of cycles to numeric_in.

(We can't just count the number of digits received, since (a) trailing

zeroes past dscale are OK, and (b) what we have at this point is

base-10000 digits not base-10 digits...)  I guess we're usually willing

to expend cycles to guard against client error, so maybe we should

do it here too.


I had originally been thinking of throwing an error if the presented
dscale was too small for the number of digits sent, but after some
reflection it seems like it'd be safer to just silently truncate the
extra digits away.  If we throw an error it's likely to break applications
that are dependent on this buggy data adapter, and I'm not sure that the
users will thank us for that.  Truncating the extra digits will make the
value actually match what it would've printed as, and if we grant that the
Devart folk did any testing of their code at all, they probably looked at
what was printed and thought that that was what they intended.  That is,
I'm assuming that dscale =3D 2 means they only want 2 decimal places in
the value.

So I propose the attached patch, which requires only a minimal amount
of new code and is about as fast as we're going to get if we want to
check this issue at all.  (Note: the apparently new error condition
for out-of-range dscale doesn't create a backwards compatibility hazard,
because make_result would've barfed anyway.  This is just a more
to-the-point error message.)

           regards, tom lane

diff --git a/src/backend/utils/adt/numeric.c
b/src/backend/utils/adt/numeric.c index d61af92..c73f9bc 100644 ***
a/src/backend/utils/adt/numeric.c --- b/src/backend/utils/adt/numeric.c
*************** numeric_recv(PG_FUNCTION_ARGS) *** 717,722 **** --- 717,724
---- alloc_var(&value, len); value.weight =3D (int16) pq_getmsgint(buf,
sizeof(int16)); + /* we allow any int16 for weight --- OK? */ + value.sign
=3D (uint16) pq_getmsgint(buf, sizeof(uint16)); if (!(value.sign =3D=3D
NUMERIC_POS || value.sign =3D=3D NUMERIC_NEG || ***************
numeric_recv(PG_FUNCTION_ARGS) *** 726,731 **** --- 728,738 ----
errmsg("invalid sign in external \"numeric\" value"))); value.dscale =3D
(uint16) pq_getmsgint(buf, sizeof(uint16)); + if ((value.dscale &
NUMERIC_DSCALE_MASK) !=3D value.dscale) + ereport(ERROR, +
(errcode(ERRCODE_INVALID_BINARY_REPRESENTATION), + errmsg("invalid scale in
external \"numeric\" value"))); + for (i =3D 0; i < len; i++) { NumericDigi=
t
d =3D pq_getmsgint(buf, sizeof(NumericDigit)); ***************
numeric_recv(PG_FUNCTION_ARGS) *** 737,742 **** --- 744,757 ----
value.digits[i] =3D d; } + /* + * If the given dscale would hide any digits=
,
truncate those digits away. + * We could alternatively throw an error, but
that would take a bunch of + * extra code (about as much as trunc_var
involves), and it might cause + * client compatibility issues. + */ +
trunc_var(&value, value.dscale); + apply_typmod(&value, typmod); res =3D
make_result(&value);