1

Writing 0.001 to Postgres (9.1 to 9.3) via a .net data adapter results in a weird 0.00 value (in pgAdmin) that appears as zero but is not.

In fact, simple queries like SELECT 1/(SELECT "weird_field" ...) FROM ... correcly gives 1000.

More complicated queries (with division) surprisingly result in a division by zero error.

Also, ordering by in Navicat correctly shows those value between 0.0011 and 0.0009

enter image description here

We use Devart libraries to connect to the database, but the culprit seems to be the data adapter (or at least the combo of these two) because a simple direct query, still via Devart drivers, doesn't produce the same result.

Any idea on what's going on?

--

EDIT:

Type on DB is numeric, in the program is represented as double decimal.

pSQL prints this:

--
0.00
(1 row)

--

EDIT 2:

log_statement = 'all' gives a weirdER result:

UPDATE "TABLE" SET ... "WEIRD_FIELD"=$8 ... WHERE ...

DETAIL:  parameters: $1 = '7', $2 = '7', $3 = '18', $4 = '18', $5 = 'V03', $6 = 'Hz',
                     $7 = 'Hz', $8 = '0.00', $9 = '0', $10 = '2', $11 = '0'

The parameter for the weird field is printed as zero (0.00), but clearly it is not...

Note that the value in the DataGridView populated by DataAdapter shows up the correct 0.001.

--

EDIT 3 (Maurice):

The issue with the Devart adapter seems to be fixed. Using the latest version, I no longer see the problem. I think it is related to this specific fix: 7.3.293 20-Nov-14: The bug with precision loss when working with PgSqlType.Numeric via the protocol 3 is fixed I upgraded my software using the latest Devart assemblies and now everything works as expected.

Maurice Makaay
  • 53
  • 1
  • 2
Teejay
  • 7,210
  • 10
  • 45
  • 76
  • Some details of the data-type of the PostgreSQL column, corresponding field in the application and a quick check of what values psql shows might be useful... – Richard Huxton Nov 24 '14 at 16:42
  • Type on the db is not "numeric" - that's not the output format for a field of "numeric". Is it perhaps "numeric(3,2)" or some such? – Richard Huxton Nov 24 '14 at 19:54
  • http://floating-point-gui.de/ – Craig Ringer Nov 25 '14 at 05:04
  • @RichardHuxton The "create script" says only `NUMERIC` – Teejay Nov 25 '14 at 08:46
  • @CraigRinger Yes, I'm aware of the FP arithmetic, but I cannot understand the problem here. I didn't make a sum or other operation, just wrote that value in the DB. And no, this is not a duplicate. Oracle and SQLServer seems to work fine against this. – Teejay Nov 25 '14 at 08:50
  • @Teejay It's not *arithemetic*, but *any representation of a decimal as binary floating point*. If your application stores the value in a `double`, then it's inexact, and subject to the issues described with FP above. If this "DataAdapter" (grr, stupid generic name) is using floating point to store `numeric` input then that's the cause of the issue; it should be using an exact arbitrary precision decimal floating point data type. – Craig Ringer Nov 25 '14 at 08:53
  • @CraigRinger So you'll want to explain me why this gives the correct result in Oracle and SQLServer. – Teejay Nov 25 '14 at 08:55
  • @CraigRinger Moreover, if you read the entire question, you'd be aware that the correct value is stored in the database (`1/value` correctly gives `1000`) but it isn't shown right. – Teejay Nov 25 '14 at 08:56
  • @Teejay As written there's not enough here to tell. A complete test case, with the code, DB definitions, etc would go a long way. – Craig Ringer Nov 25 '14 at 08:57
  • @Teejay So ... what do you think the issue is, then? you clearly have a strong opinion on what it is not. Do you suspect a bug in "DataAdapter"? What does it use to talk to PostgreSQL? nPgSQL? – Craig Ringer Nov 25 '14 at 08:58
  • @CraigRinger No, Devart drivers. It's written in the question. But if not using the data adapter, the Devart driver write a normal value, not the weird one. – Teejay Nov 25 '14 at 09:08
  • If I write `SELECT TO_CHAR("weird_field", 'FM999999999999999999D999999999999999999') FROM ...` it outputs `.001` so the value is correct. Also adding a LOT of 9 to the decimal part of the format string, it outputs the correct value. – Teejay Nov 25 '14 at 09:10
  • Ah, "**still** via devart drivers". Right. It's all a bit hard to follow unless you already know what you're doing, your description kind of assumes the reader already knows what's going on. Either that or I'm just slow. Whatever. If you're interested in investigating a possible dataadapter bug, I suggest putting together a small standalone test-case to submit to them. If you think it's a PostgreSQL issue in some way, please capture the exact queries + parameters (`log_statement = 'all'` in `postgresql.conf`). – Craig Ringer Nov 25 '14 at 09:14
  • @CraigRinger See the weirdER result in the edit 2 – Teejay Nov 25 '14 at 09:39
  • @CraigRinger Note that `numeric` should not be affected by FP arithemetic errors, since it's the Pg datatype for `decimal` (see http://www.postgresql.org/docs/9.3/static/datatype.html). We are now checking what's going on the dataadapter as regards data types. – Teejay Nov 25 '14 at 10:22
  • @CraigRinger We found that the column is correctly `decimal` in the dataadapter (not `double` as I wrote yesterday). So it should be correct at all. – Teejay Nov 25 '14 at 10:26
  • @CraigRinger We also tried to convert the DB column to float8. This way it works fine. But we use those values to convert between units of measure, so we can't use float8, exactly because we don't want FP arithemetic errors. – Teejay Nov 25 '14 at 10:29

1 Answers1

2

We found out that the problem is in the way PgSql represents numerics (decimal) with unspecified precision and scale.

The value in the DB seems to be correct (0.001) but in some operations it is truncated:

"weird_field" + 0.001
---------------------
                0.002

but

"weird_field" * 2
--------------------
                0.00

and

"weird_field" * 5
--------------------
                0.01

This also explains why some queries gives a division by zero error.

The solution is to specify precision and scale, for example we chose numeric(38,28) (that we also use for decimal in Oracle) and all works fine.

--

Looking at PgSql documentation, we didn't find nothing about this behavior, so we believe it's a bug:

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.

Strange thing is also that 0.000001 (et similia) doesn't get truncated, whilst 0.001 does.

Teejay
  • 7,210
  • 10
  • 45
  • 76
  • 1
    Can you produce a test case that demonstrates this, preferably using only SQL? What PostgreSQL version is this exactly? – Craig Ringer Nov 25 '14 at 12:12
  • 1
    This is not possible with plain sql. It's a DataAdapter-related issue. It seems to be a Devart bug, but Postgres, from its corner, stores a strange value. I opened a bug report on http://www.postgresql.org/message-id/20141125111529.15508.34881@wrigleys.postgresql.org. – Teejay Nov 29 '14 at 15:55
  • To summarise the bug thread: DevArt's adapter is sending invalid binary parameters for numeric values, and PostgreSQL isn't being paranoid enough about the input so it's accepting them instead of throwing an error. – Craig Ringer Dec 01 '14 at 02:55
  • Yeah, something like that. I'm investigating this with a person from Postgres. – Teejay Dec 01 '14 at 08:47