Does the JPQL avg aggregate function work with Integers?

Question

I have a JPA 2 Entity named Surgery. It has a member named transfusionUnits that is an Integer.

There are two entries in the database. Executing this JPQL statement:

Select s.transfusionUnits from Surgery s

produces the expected result:

2
3

The following statement produces the expected answer of 5:

Select sum(s.transfusionUnits) from Surgery s

I expect the answer of the following statement to be 2.5, but it returns 2.0 instead.

Select avg(s.transfusionUnits) from Surgery s

If I execute the statement on a different (Float) member, the result is correct. Any ideas on why this is happening? Do I need to do some sort of cast in JPQL? Is this even possible? Surely I am missing something trivial here.

Answer 1

First, let me summarize what the JPA 2.0 specification says about AVG (see section 4.8.5 Aggregate Functions in the SELECT Clause for the full content)

The AVG function takes a state field path expression as an argument and calculates the average value of the sate field over the group. The state field must be numeric, and the result is returnd as a Double.

So, after a first read, I was expecting the following snippet to pass:

Query q = em.createQuery("select avg(s.transfusionUnits) from Surgery s");
Double actual = (Double) q.getSingleResult();
assertEquals(2.5d, actual.doubleValue());

But it didn't, I was getting 2.0 as result (a Double, but not the expected result).

So I looked at the database level and realized that the SQL query was actually not returning 2.5. And indeed, this is what the documentation of my database says about AVG:

The average (mean) value. If no rows are selected, the result is NULL. Aggregates are only allowed in select statements. The returned value is of the same data type as the parameter.

I was expecting too much. JPA will return the result as a Double, but it won't do any magic. If the query doesn't return a result with the required precision, you won't get it at the Java level.

So without changing the type of transfusionUnits, I had to run this native query to get things working:

Query q = em.createNativeQuery("SELECT AVG(CAST(s.transfusionUnits as double)) from Surgery s");
Double actual = (Double) q.getSingleResult();
assertEquals(2.5d, actual.doubleValue());

---

Update: It appears that some database (e.g. MySQL) do return a value with a decimal part when using AVG on an INT column (which makes sense, regardless of the documented behavior of the database I used here). For other databases, the above cast could be handled in the "dialect" (e.g. see HHH-5173 for Hibernate) to avoid portability issues between database when using JPQL.

Answer 2

Casting the averaged field to double does the trick. Not sure about performance implications, but should not be anything evil:

Select avg(s.transfusionUnits + 0.0) from Surgery s