[eclipselink-users] Off topic, but perhaps relevant: setting doubles, n

[Laird Nelson <ljnelson@xxxxxxxxx>]

This is a JDBC issue, but which will affect the way we do mappings in EclipseLink, so I wanted to hear from the community, and especially the maintainers of the JPA reference implementation.

I know that floating point /operations/ are inherently imprecise.  But I always thought that if I did this:

somePreparedStatement.setDouble(0, 1.3d);

...then when I looked in the database (in a column declared as, say, DOUBLE, or some equally enormous floating point data type) I'd find 1.3 in there exactly.  Note that I've simply used a double literal.  No adding, multiplying, etc., so, I would think, no loss of precision.

Instead, I'm noticing that on PostgreSQL if I do this I see 1.3 exactly (using PostgreSQL tools to look at it), but on just about anything else I see 1.2999963723 etc. (using Informix, SQL Server, Oracle, H2...).

My first question is: can someone point me to some paper somewhere on how a database driver should transfer a Java double over the wire such that when it hits the database and is destined for a column of SQL type DOUBLE no precision is lost?  It would appear that PostgreSQL's approach differs from all others'.  This might be a bug of colossal proportions on PostgreSQL's part, or (less likely) on everyone else's part.  I would expect that Double#toLongBits() would be required.

My slightly tangential question from that is: how come PostgreSQL stores what I expect?  (I can't say it's "right" or "wrong", since I don't know whether it's rounding, which would be "wrong", or simply correctly not losing any precision, which would be "right"; my point is that to my eyes what I put in is exactly what I see once it's in there.)

Finally, in a hopeless attempt to make this relevant and on topic, if I have a float field in my JPA object, and I initialize it to a floating point number that I declare to have exactly one digit to the right of the decimal point, can I be guaranteed that it will retain that precision across trips through the JPA layer?

And finally, the background.

We're seeing some odd behavior where a grade point calculation is being thrown off because the user enters "1.3" in the Java application, but by the time this value gets converted to a Float (via String.valueOf()) and shipped off to our legacy Informix database, it is 1.299999 etc.  The Informix column is declared as a FLOAT.  Meanwhile, if the same operation is attempted from the sibling C application, Informix stores the value as 1.3 exactly.  Both the C and the Java application are used in production next to each other, so the values stuffed in the database by one have to be exactly equal to the values stuffed in by the other.  We cannot change the column in the database; the bad decision to use floating point datatypes to store precise numbers was made 30 years ago.

We only recently discovered this problem, as a lot of our Java deployments are to PostgreSQL which shows no such problem.

When we discovered the problem, we immediately tested across a wide range of database drivers.  Oddly, or perhaps not, the PostgreSQL database was the only one where when we looked at it with native database tools the value was reported to be exactly 1.3.  All the others reported 1.2999999 under the hood.

(Obviously I know that the proper answer to all this is use BigDecimal on the Java side and DECIMAL or NUMERIC on the SQL side.)

Thanks for the indulgence; I'm happy to carry on further correspondence about this via email.  If the list is silent, I'll take that as a gentle admonishment to move along and stop asking dumb questions.  :-)

Thanks,
Laird