SQL Hibernate is not retuning the same results as a direct SQL query

Question

I have a Java app which interfaces with MySQL via Hibernate.

In my code, I have the app executing the SQL command:

final SQLQuery query = sf.getCurrentSession().createSQLQuery(
            "select\n" +
            "   id, null as store_id, case when transaction_type = 'SALE' then 1 else -1 end as sign, payment_type,\n" +
            "   sum(cost_before_tax) as amount_before_tax, sum(tax_amount) as tax_amount, sum(cost) as amount,\n" +
            "   sum(ticket_count) as count\n" +
            "from settlement_collection_initial_settlement\n" +
            "where\n" +
            "   business_date between :start and :end\n" +
            (storeID != null ? "    and store_id = :store\n" : "") +
            "group by transaction_type, payment_type"
        );
        query.addEntity(AmountRow.class);
        query.setDate("start", start);
        query.setDate("end", end != null ? end : start);
        if (storeID != null) {
            query.setString("store", new UUIDType().toSQLString(storeID));
        }
        return query.list();

And query.list() returns:

However, if I run the exact same query in MySqlWorkbench:

select
    id, store_id, case when transaction_type = 'SALE' then 1 else -1 end as sign, payment_type,
    sum(cost_before_tax) as amount_before_tax, sum(tax_amount) as tax_amount, sum(cost) as amount,
    sum(ticket_count) as count
from settlement_collection_initial_settlement
where
    business_date between '2018-07-27' and '2018-07-27'
    and store_id = 'xxxxxx'
group by transaction_type, payment_type

I get the results:

Notice that those results are close, but not the same. Have a look at the two CASH lines, and the direct SQL shows a second CASH line with a different sign and other values. So in essense, the Hibernate executed sql is repeating that CASH line.

To me, it looks like both ways should return the exact same results. Does anyone see why my Hibernate SQL is returning different (wrong) results from when I directly execute the SQL?

UPDATE:

Here is my AmountRow class:

@Entity
public class AmountRow {

    static final int SIGN_SALE_OR_DEBIT = 1, SIGN_REFUND_OR_CREDIT = -1;

    @Id
    private float id;

    @ManyToOne
    @JoinColumn(name = "store_id", nullable = true)
    private Store store;

    @Column(nullable = true)
    @Enumerated(EnumType.STRING)
    private Payment.Type paymentType;

    private int sign;

    // NOTE: "$" is the name of a custom class
    @Column(nullable = false)
    @org.hibernate.annotations.Type(type = "com.mycompany.service.$Type")
    private $ amountBeforeTax, taxAmount, amount;

    @Column(nullable = false)
    private int count;

    // Hibernate constructor
    protected AmountRow() {}

    // NOTE: "$" is the name of a custom class
    AmountRow(final $ amountBeforeTax, final $ taxAmount, final $ amount, final int count, final int sign) {
        Assert.noneNull(amountBeforeTax, "amountBeforeTax", taxAmount, "taxAmount", amount, "amount");
        Assert.notNegative(count, "count");
        assertValidSign(sign);
        this.amountBeforeTax = amountBeforeTax;
        this.taxAmount = taxAmount;
        this.amount = amount;
        this.count = count;
        this.sign = sign;
    }

    // NOTE: "$" is the name of a custom class
    AmountRow(final $ amountBeforeTax, final $ taxAmount, final $ amount, final int count, final int sign, final Payment.Type paymentType) {
        this(amountBeforeTax, taxAmount, amount, count, sign);
        this.paymentType = paymentType;
    }

    static void assertValidSign(final int sign) {
        if (sign != SIGN_SALE_OR_DEBIT && sign != SIGN_REFUND_OR_CREDIT)
            throw new IllegalArgumentException("invalid sign " + sign);
    }

    public String toString() {
        return "AmountRow[paymentType=" + paymentType + ", sign=" + sign + ", amountBeforeTax=" + amountBeforeTax + ", taxAmount=" + taxAmount + ", amount=" + amount + ", count=" + count + "]";
    }

    public Store getStore() {
        return store;
    }

    public Payment.Type getPaymentType() {
        return paymentType;
    }

    public int getSign() {
        return sign;
    }

    public $ getAmountBeforeTax() {
        return amountBeforeTax;
    }

    public $ getTaxAmount() {
        return taxAmount;
    }

    public $ getAmount() {
        return amount;
    }

    public int getCount() {
        return count;
    }

    public $ getAmountBeforeTaxPerCount() {
        return count != 0 ? amountBeforeTax.divide(count) : null;
    }

    public $ getAmountPerCount() {
        return count != 0 ? amount.divide(count) : null;
    }
}

Answer 1

This is caused by different timezone between your application and your DB.

Either:

• Use absolute time like Epoch millisecond to represent time (Work regardless timezone) OR
• Check timezone of your application and sql server, choose and convert your Date to a common timezone

Your HSQL also expose another problem in your code: There's a duplicated result.

Your should post your AmountRow class for further investigation.

Update

After reading your AmountRow class, the problem is usage of float as @Id key would cause wrong equality check, makes Hibernate not load the data from DB. You should never rely on float/double equality.

@df778899 has an excellent explanation for this issue.

Change the @Id field to long/int to solve the duplication.

Answer 2

@Manh is right in his earlier comment about the id as a float. He deserves the answer + bounty - just using an answer to allow room to explain...

The reason is that Hibernate manages objects in its session in a StatefulPersistenceContext. Entities in there are effectively keyed in a HashMap by the @Id value. (The real key class is an org.hibernate.engine.spi.EntityKey, but the equals() method on here will, still indirectly, drill down to Float.equals() against the two auto-boxed float id values).

Those id values have too much precision for a float - the limit is typically about 7 digits:

16842421f -> (float) 1.684242E7
16842423f -> (float) 1.6842424E7
16842419f -> (float) 1.684242E7

Clearly rows 0 and 2 will be treated as having equal id values, and therefore seen as the same entity instance by Hibernate. As @Manh says, an int or a long would cope better.

Answer 3

If you examine the content of the ArrayList (the one holding Hibernate resultset), you will notice the 1st and 3rd row have the exact same address (@7427). This means the same database row is repeated twice. This also indicates that Hibernate is considering that the 3rd read record is identical to the 1st one (which is already present in current Session). So IMHO, you have to investigate why these 2 AmountRow instances are evaluated as equals (> check equalsTo(), compareTo() and hashCode() implementation). NB: To validate my diagnostic, you can return a Set (and not List) and check its size. I expect a size of 2...

Answer 4

try to use HQL

select
id, store_id, case when transaction_type = 'SALE' then 1 else -1 end as sign, payment_type,
sum(cost_before_tax) as amount_before_tax, sum(tax_amount) as tax_amount, sum(cost) as amount,
sum(ticket_count) as count
from settlement_collection_initial_settlement
where
    business_date between :beginDate and :endDate
    and store_id = :storeID
group by transaction_type, payment_type

create a string from the query and then:

Query query = session.createQuery(hql);
query.setParameter("beginDate ", start);
query.setParameter("endDate", end != null ? end : start);
query.setParameter("storeID", storeID);
return query.list();

also another good advice is to always check your connection string, that you are connecting to the same database