JPA: TypedQuery sometimes returns null instead of NoResultException

Question

Usually I work with NoResultException to return an "empty" object, e.g. an empty error list or new BigInteger("0"), if I get no results from a TypedQuery. Now it turned out that this sometimes doesn't work. Suddenly getSingleResult() returns null instead of causing a NoResultException, and I don't understand why. Look this example: ``` public BigInteger pointsSumByAccountId(long accountId) { try { TypedQuery pointsQuery = entityManager.createNamedQuery(Points.SumByAccountId, BigInteger.class); pointsQuery.setParameter(Points.AccountIdParameter, accountId); return pointsQuery.getSingleResult(); } catch (NoResultException e) { return new BigInteger("0"); } } ``` The important part of the Entity... ``` @NamedQueries({@NamedQuery(name = "Points.sumByAccountId", query = "select sum(p.value) from Points p where p.validFrom <= current_timestamp() and p.validThru >= current_timestamp() and p.account.id = :accountId")}) public class Points { private static final long serialVersionU…

Accepted Answer

It looks like a correct behaviour for me.

`NoResultException` is thrown when no rows are returned, but `sum` returns exactly one row with `null` value in your case. From JPA 2.0 Specification:

If SUM, AVG, MAX, or MIN is used, and there are no values to which the aggregate function can be applied, the result of the aggregate function is NULL.

If you want to get `0` instead of `null`, use `coalesce`:

```
select coalesce(sum(p.value), 0) ...
```

JPA: TypedQuery sometimes returns null instead of NoResultException

Solution

Problem