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

Hi, I already tried to log all, but it seems not to help a lot.

In the StackOverflow question I originally linked in this bug report, I
wrote the following:

   >  log_statement =3D 'all' gives a weirdER result:
   >
   >  UPDATE "TABLE" SET ... "WEIRD_FIELD"=3D$8 ... WHERE ...
   >
   >  DETAIL:  parameters: $1 =3D '7', $2 =3D '7', $3 =3D '18', $4 =3D '18'=
, $5 =3D
'V03', $6 =3D 'Hz',
   >                       $7 =3D 'Hz', $8 =3D '0.00', $9 =3D '0', $10 =3D =
'2', $11
=3D '0'
   >
   >  The parameter for the weird field is printed as zero (0.00), but
clearly it is not...

As you told, Postgres probably accept and store a non-100%-legal value,
rather than throwing an exception,
but it seems difficult to understand something from the plain-text log.

If I could any help you, please tell me.







*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.

*  Save a tree! Don't print this e-mail unless you really need to.*

2014-11-27 21:48 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:

> Tommaso Sala <tommaso.sala@cla-it.eu> writes:
> > We found that it is probably a bug in the data adapter (supplied by
> Devart
> > http://www.devart.com/).
>
> > But, once that weird value is written in DB by Devart adapter, there mu=
st
> > be some kind of strange behavior also in Postgres.
> > In fact, reading that value causes strange results:
>
> > postgres=3D# select "WEIRD_FIELD" from "TABLE";
> >  WEIRD_FIELD
> > ----------------
> >            0.00
> > (1 row)
>
>
> > postgres=3D# select "WEIRD_FIELD" + 0.01 from "TABLE";
> >  ?column?
> > ----------
> >      0.01
> > (1 row)
>
>
> > postgres=3D# select "WEIRD_FIELD" * 2 from "TABLE";
> >  ?column?
> > ----------
> >      0.00
> > (1 row)
>
>
> > postgres=3D# select "WEIRD_FIELD" + 0.001 from "TABLE";
> >  ?column?
> > ----------
> >     0.002
> > (1 row)
>
>
> > postgres=3D# select 1 / "WEIRD_FIELD" from "TABLE";
> >        ?column?
> > -----------------------
> >  1000.0000000000000000
> > (1 row)
>
>
> > postgres=3D# select TO_CHAR("WEIRD_FIELD", 'FM99999999D99999999') from
> > "TABLE";
> >  to_char
> > ---------
> >  .001
> > (1 row)
>
> > It's always 0.001 but if selected or multiplicated it becomes 0.00.
>
> That's pretty weird, but it's hard to debug without access to
> Devart's adapter.  Can you trace what the adapter is doing when
> it inserts this value?  (log_statement =3D all would help.)
>
> An idea that comes to mind is that maybe Devart is using binary
> parameters and transmitting a value that isn't 100% legal; but
> that's only a guess at this stage.  (It does look like numeric_recv
> is pretty trusting about the input weight and dscale fields;
> I'm not sure if a bad dscale would explain these symptoms, but
> it seems possible.)
>
>                         regards, tom lane
>