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

[Frank Schwarz <fs5@xxxxxxx>]

Not only operations are imprecise but also (most of) the numbers itself.
There is no double which has the value of 1.3d as 0.3d has no terminating
binary representation: 0.3d = 0*0.5 + 1*0.25 + 0*0.125 + 0*0.0625 ... =
0.01001100(1100)b. 

One might argue that if I put 1.3d into a variable I should also get out
"1.3" from that variable. But this is only true if there is some magic in
the double-to-string-function (IMHO this function tries to find the shortest
decimal representation that is equivalent to the binary representation). If
there is some loss of precision on the round trip to the database (for
example double -> float -> double) than the magic will probably not occur
and you will get garbage digits.

HTH
Frank


ljnelson wrote:
> 
> 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
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
> 
> 

-- 
View this message in context: http://old.nabble.com/Off-topic%2C-but-perhaps-relevant%3A-setting-doubles%2C-no-operations%2C-but-precision-loss--tp29537113p29559767.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.