0

Due to some of the restrictions of JPQL (no ordering in subqueries, can't check array equality), I'm having to do some workarounds. Namely, I'm concatenating some numbers (and commas) into a string and checking if they're in an array parameter. (I wish to know which item has exactly a particular set of associated entries in a crossreference table. "For each item, is the set of other_id/value pairs referencing item equal to this set I'm passing in as a parameter?") However, the number-to-string conversion tends to go a little weird at high decimal places - I think JPQL (or possibly postgres) is converting the numbers into strings slightly differently than Java is. For instance, JPQL vs Java might give me

1.20991849899292 vs
1.2099184989929199

or maybe the other way around, and new BigDecimal(value).toString() gives me something again slightly different.

How can I get them to agree? Consider that there's string A, returned from a query, and I wish it to match string B, calculated in Java from the same number. One way I thought of was to reduce the precision of the number before storing it, so it just wouldn't run into the issue. This worked, but only once there were only, say, 10 bits of mantissa left, which was more than I wanted to remove. Another way I thought of was to obtain string B by running, like,

entityManager.createQuery("SELECT CAST(:val as text)", String.class)
  .setParameter("val", 0.14520927387582)
  .getSingleResult();

...but JPQL refuses to do queries that don't have a FROM clause. I could make a junk table, always containing exactly one row, but that's really hacky.

Is there a way to tell JPQL how to format a number when it turns it into a string (without knowing which underlying database you're using)? Is there a way to get the SELECT CAST method to work without being more than a little hacky? Any other, better ideas?

Erhannis
  • 4,256
  • 4
  • 34
  • 48
  • 1
    [What Every Computer Scientist Should Know About Floating-Point Arithmetic](https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html) and [Is Floating Point Broken?](https://stackoverflow.com/q/588004/18157). In other words, depending on floating point "equality" is a non-starter. You have to know details of floating point implementation on each platform and truncate/round to the smallest number of significant digits. – Jim Garrison Mar 07 '18 at 23:11
  • I suspect what is happening here is that Java is producing the shortest string (fewest number of digits) need to uniquely identify the floating-point value (meaning that when you convert the decimal back to floating-point, you get the original number) while JPQL is producing 17 decimal digits. A solution to this would be to convert both numbers using the same format. For example, use Java’s formatting features to request exactly 17 digits. You would need to ensure that both use exactly the same format in all details, including fixed point or scientific ("E”) notation. – Eric Postpischil Mar 07 '18 at 23:35
  • Your question asks whether JPQL has a way to specify a format for the conversion from floating point to a decimal numeral in a string. I do not know about JPQL, but Java certainly does. Why did you not ask about specifying how Java formats the number? That is certainly possible. – Eric Postpischil Mar 08 '18 at 00:32
  • @JimGarrison: It is incorrect to say that depending on equality is a non-starter, and there is no reason to put “equality” in quotes. Equality is a well-defined concept both mathematically and in floating-point arithmetic. As stated, the problem is to test two numbers for equality, without any issue of them being affected by rounding errors from previous calculations. So they will not differ because of any inaccuracies in calculations. The problem is merely to bring the values together from different pieces of software so they can be compared. – Eric Postpischil Mar 08 '18 at 01:26
  • Suppose my conjecture about the formatting issue is correct, then another solution is to convert the string from JPQL to a Java `Double`, as with `Double(string)`. Since JPQL is producing enough digits to uniquely identify the floating-point value, this will reproduce the original value from JPQL in the Java `Double`. Then you can directly compare them for equality in Java. – Eric Postpischil Mar 08 '18 at 01:29
  • @EricPostpischil That is an interesting possibility, except that I'm doing the comparison in JPQL - I have to select the set of things, a pair of whose numbers is in an array passed in from java. I could export the entire table into java and do the filtering there, but that's pretty inefficient. – Erhannis Mar 08 '18 at 16:20

0 Answers0