Re: BUG #12053: Strange behavior for numeric types with unspecified precision-scale

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 must
be some kind of strange behavior also in Postgres.
In fact, reading that value causes strange results:

postgres=# select "WEIRD_FIELD" from "TABLE";
WEIRD_FIELD
----------------
0.00
(1 row)

postgres=# select "WEIRD_FIELD" + 0.01 from "TABLE";
?column?
----------
0.01
(1 row)

postgres=# select "WEIRD_FIELD" * 2 from "TABLE";
?column?
----------
0.00
(1 row)

postgres=# select "WEIRD_FIELD" + 0.001 from "TABLE";
?column?
----------
0.002
(1 row)

postgres=# select 1 / "WEIRD_FIELD" from "TABLE";
?column?
-----------------------
1000.0000000000000000
(1 row)

postgres=# 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.

Repeat the queries after writing the same value via plain SQL:

postgres=# select "WEIRD_FIELD" from "TABLE";
WEIRD_FIELD
----------------
0.001
(1 row)

postgres=# select "WEIRD_FIELD" + 0.01 from "TABLE";
?column?
----------
0.011
(1 row)

postgres=# select "WEIRD_FIELD" + 0.001 from "TABLE";
?column?
----------
0.002
(1 row)

postgres=# select "WEIRD_FIELD" * 2 from "TABLE";
?column?
----------
0.002
(1 row)

postgres=# select 1 / "WEIRD_FIELD" from "TABLE";
?column?
-----------------------
1000.0000000000000000
(1 row)

postgres=# select TO_CHAR("WEIRD_FIELD", 'FM99999999D99999999') from
"TABLE";
to_char
---------
.001
(1 row)

You see it?

*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
è destinato esclusivamente al destinatario sopra indicato, il quale è
l'unico autorizzato ad usarlo, copiarlo e, sotto la propria responsabilità,
diffonderlo. Chiunque ricevesse questo messaggio per errore o comunque lo
leggesse senza esserne legittimato è avvertito che trattenerlo, copiarlo,
divulgarlo, distribuirlo a persone diverse dal destinatario è severamente
proibito, ed è 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-26 16:50 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> tommaso(dot)sala(at)cla-it(dot)eu writes:
> > We found out that PgSql acts weirdly when using "numeric" type with
> > UN-specified precision and scale
> > Writing 0.001 via a .net DataAdapter to a numeric column with unspecified
> > precision-scale results in a weird 0.00 value:
> > if you later
> > SELECT "that value" + 0.001 you get 0.002,
> > but if you
> > SELECT "that value" * 2 you get 0.00
> > and
> > SELECT "that value" * 5 gives a rounded 0.01
> > Value in the database is correct, since if you TO_CHAR it, it gets
> printed
> > correctly as .001, but if you SELECT it, you get 0.00
> > Also, the weirder thing is that 0.000001 doesn't get truncated!
>
> I think you must have some issues with the .Net adapter. Postgres
> itself doesn't give such inconsistent results:
>
> regression=# create table t1 (f1 numeric);
> CREATE TABLE
> regression=# insert into t1 values (0.001);
> INSERT 0 1
> regression=# select * from t1;
> f1
> -------
> 0.001
> (1 row)
>
> regression=# select f1 + 0.001 from t1;
> ?column?
> ----------
> 0.002
> (1 row)
>
> regression=# select f1 * 2 from t1;
> ?column?
> ----------
> 0.002
> (1 row)
>
> regression=# select f1 * 5 from t1;
> ?column?
> ----------
> 0.005
> (1 row)
>
> regards, tom lane
>