Issue in retrieving a property mapped with a BigInteger in Hibernate

Question

I encountered an issue regarding retrieval of 10^18 digit number using Hibernate. Although I am not entirely sure if it's an Oracle Database, Grails, Hibernate, or Java issue entirely. Considering this Grails domain class:

class Payment {
    BigInteger id
    BigDecimal amount

    static mapping = {        
        id generator: "assigned"
        version false
    }
}

I encounter the issue through the retrieval:

Payment savePayment() {
    BigInteger id = 201910151421550246D
    BigDecimal amount = 100.00G

    Payment payment = new Payment()

    payment.id = id
    payment.amount amount

    payment.save(flush: true)

    Payment retrievedPayment = Payment.findById(id)

    println "payment.id: " + payment.id
    println "payment.amount: " + payment.amount
    println "retrievedPayment.id: " + retrievedPayment.id
    println "retrievedPayment.amount: " + retrievedPayment.amount

    // payment.id: 201910151421550246
    // payment.amount: 100.00
    // retrievedPayment.id: 201910151421550240
    // retrievedPayment.amount: 100.00

    return retrievedPayment
}

I assigned 201910151421550246 as id before saving and also confirmed it's value if its the same on the database. And it is. Also when I tried to retrieve the said record using that id, I can still retrieve the same Payment record. The problem is why the value is off by six points? I'm currently mapping it to String data type and convert it to BigInteger when I need to perform numeric operations as a temporary solution.

Answer 1

It is not a problem of Oracle database or Java BigInteger or hibernate as they supports more than 201910151421550246 number.

In context of web application, value greater than Number.MAX_SAFE_INTEGER is not considered reliable for browser.

Like when we print console.log(201910151421550246); in javascript, we get 201910151421550240 as output.

So, if you want to use number greater than Number.MAX_SAFE_INTEGER on, then convert it to String and then use it.

Answer 2

The first lines of the gist are the clue. You are defining a Double with the literal "D" suffix, and Double cannot exactly represent the integer you desire. See "Floating-Point Literals". When cast to BigInteger there is a rounding error. Use a String to create the BigInteger, or for literals the dual-duty G suffix which works for both BigInteger and BigDecimal.

groovy:000> i=201910151421550246D
===> 2.0191015142155024E17
groovy:000> i.class
===> class java.lang.Double
groovy:000> j=(BigInteger)i
===> 201910151421550240
groovy:000> j.class
===> class java.math.BigInteger
groovy:000> id = new BigInteger("201910151421550246")
===> 201910151421550246
groovy:000> id.class
===> class java.math.BigInteger
groovy:000> b=201910151421550246G
===> 201910151421550246
groovy:000> b.class
===> class java.math.BigInteger
groovy:000> id==b
===> true
groovy:000> d=100.0G
===> 100.0
groovy:000> d.class
===> class java.math.BigDecimal