Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query

Question

The following criteria query calculates the average of rating of different groups of products.

CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Metamodel metamodel=entityManager.getMetamodel();
EntityType<Product>entityType=metamodel.entity(Product.class);
Root<Product>root=criteriaQuery.from(entityType);
SetJoin<Product, Rating> join = root.join(Product_.ratingSet, JoinType.LEFT);

Expression<Number> quotExpression = criteriaBuilder.quot(criteriaBuilder.sum(join.get(Rating_.ratingNum)), criteriaBuilder.count(join.get(Rating_.ratingNum)));
Expression<Integer> roundExpression = criteriaBuilder.function("round", Integer.class, quotExpression);
Expression<Object> selectExpression = criteriaBuilder.selectCase().when(quotExpression.isNull(), 0).otherwise(roundExpression );

criteriaQuery.select(criteriaBuilder.tuple(root.get(Product_.prodId).alias("prodId"), selectExpression.alias("rating")));
criteriaQuery.groupBy(root.get(Product_.prodId));

criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(roundExpression, 0));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Product_.prodId)));

TypedQuery<Tuple> typedQuery = entityManager.createQuery(criteriaQuery);
List<Tuple> tuples = typedQuery.getResultList();

It generates the following SQL query :

SELECT product0_.prod_id AS col_0_0_, 
       CASE 
         WHEN Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num) IS 
              NULL THEN 
         0 
         ELSE Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num)) 
       END AS col_1_0_ 
FROM   social_networking.product product0_ 
       LEFT OUTER JOIN social_networking.rating ratingset1_ 
                    ON product0_.prod_id = ratingset1_.prod_id 
GROUP  BY product0_.prod_id 
HAVING Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num)) >= 0 
ORDER  BY product0_.prod_id DESC 

The case...when structure replaces null values with 0, if the specified expression in the case clause is evaluated to null.

I need the same case...when construct in the having clause so that the group of rows returned by the group by clause can be filtered by replacing null with 0 in the list of values calculated by the case...when construct, if any.

Accordingly, the having clause should be generated like

HAVING
    (CASE
        WHEN Sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num) IS
             NULL THEN 0 
        ELSE Round(sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num))
    END)>=0

It could be possible, if in the greaterThanOrEqualTo() method, selectExpression instead of roundExpression is given but it is not possible. Doing so, generates a compile-time error indicating type mismatch between Expression<Integer> and Expression<Object>.

So how can I have the same case...when structure in the having clause as in the select clause?

_{I have also tried by removing the generic type parameter Object of the expression like Expression selectExpression but doing so, caused the NullPointerException to be thrown.}

---

Moreover, alias names (prodId, rating) as given in the select clause have no effect in the generated SQL as can be seen. Why columns are not aliased here? Am I missing something?

If columns are aliased then, it should be possible to write the having clause just like follows.

having rating>=0

and having in the criteria query should be as follows,

criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(join.<Integer>get("rating"), 0));

but as columns are not aliased in the select clause, it throws an exception.

java.lang.IllegalArgumentException: Unable to resolve attribute [rating] against path [null]

---

What is the way to get around this situation? Anyway, the rows returned by Group by should be filtered by replacing null with 0 in the list of values produced by case...when in the select clause.

---

I'm using JPA 2.0 provided by Hibernate 4.2.7 final.

---

EDIT:

I have tried with the following expression :

Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase()
                                       .when(quotExpression.isNull(), 0)
                                       .<Integer>otherwise(roundExpression);

but it caused the following exception to be thrown :

Caused by: java.lang.NullPointerException
    at java.lang.Class.isAssignableFrom(Native Method)
    at org.hibernate.ejb.criteria.ValueHandlerFactory.isNumeric(ValueHandlerFactory.java:69)
    at org.hibernate.ejb.criteria.predicate.ComparisonPredicate.<init>(ComparisonPredicate.java:69)
    at org.hibernate.ejb.criteria.CriteriaBuilderImpl.greaterThanOrEqualTo(CriteriaBuilderImpl.java:468)

How can the following expression work then,

Expression<Integer> roundExpression = criteriaBuilder
                              .function("round", Integer.class, quotExpression);

both have the same type?

Is there a way to put the case...when structure in the having clause?

---

EDIT

Changing the expression type to

Expression<Integer> selectExpression = criteriaBuilder
                                       .<Integer>selectCase()
                                       .when(quotExpression.isNull(), 0)
                                       .<Integer>otherwise(roundExpression);

in EclipseLink (2.3.2) works hence, it can be made available in the having clause.

In case of Hibernate provider, it throws the NullPoiterExcpetion, if an attempt is made to change the expression type of selectCase() (which returns Expression<Object> by default).

---

Update :

This issue still persists in Hibernate 5.0.5 final.

Fulk

Answer 1

This is very unlikely to be a bug in Hibernate. There was a technical mistake in fabricating the criteria query given. Taking the same example but in a simpler form.

Let's assume that we are interested in generating the following SQL query.

SELECT
    p.prod_id,
    p.prod_name,
    CASE
        WHEN sum(r.rating_num)/count(DISTINCT r.rating_id) IS NULL THEN 0
        ELSE round(sum(r.rating_num)/count(DISTINCT r.rating_id))
    END AS avg_rating
FROM
    product p
LEFT OUTER JOIN
    rating r
        ON p.prod_id=r.prod_id
GROUP BY
    p.prod_id,
    p.prod_name 
HAVING
    CASE
        WHEN sum(r.rating_num)/count(DISTINCT r.rating_id) IS NULL THEN 0
        ELSE round(sum(r.rating_num)/count(DISTINCT r.rating_id))
    END>=1

Based on the following table in MySQL.

mysql> desc rating;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| rating_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| prod_id     | bigint(20) unsigned | YES  | MUL | NULL    |                |
| rating_num  | int(10) unsigned    | YES  |     | NULL    |                |
| ip_address  | varchar(45)         | YES  |     | NULL    |                |
| row_version | bigint(20) unsigned | NO   |     | 0       |                |
+-------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)

This table rating has an obvious many-to-one relationship with another table product (prod_id is the foreign key referencing the primary key prod_id in the product table).

In this question, we are only interested in the CASE construct in the HAVING clause.

The following criteria query,

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<Product> root = criteriaQuery.from(entityManager.getMetamodel().entity(Product.class));
ListJoin<Product, Rating> prodRatingJoin = root.join(Product_.ratingList, JoinType.LEFT);

List<Expression<?>> expressions = new ArrayList<Expression<?>>();
expressions.add(root.get(Product_.prodId));
expressions.add(root.get(Product_.prodName));

Expression<Integer> sum = criteriaBuilder.sum(prodRatingJoin.get(Rating_.ratingNum));
Expression<Long> count = criteriaBuilder.countDistinct(prodRatingJoin.get(Rating_.ratingId));

Expression<Number> quotExpression = criteriaBuilder.quot(sum, count);
Expression<Integer> roundExpression = criteriaBuilder.function("round", Integer.class, quotExpression);
Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase().when(quotExpression.isNull(), criteriaBuilder.literal(0)).otherwise(roundExpression);
expressions.add(selectExpression);

criteriaQuery.multiselect(expressions.toArray(new Expression[0]));
expressions.remove(expressions.size() - 1);

criteriaQuery.groupBy(expressions.toArray(new Expression[0]));
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, criteriaBuilder.literal(1)));

List<Tuple> list = entityManager.createQuery(criteriaQuery).getResultList();

for (Tuple tuple : list) {
    System.out.println(tuple.get(0) + " : " + tuple.get(1) + " : " + tuple.get(2));
}

Generates the following correct SQL query as expected.

select
    product0_.prod_id as col_0_0_,
    product0_.prod_name as col_1_0_,
    case 
        when sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id) is null then 0 
        else round(sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id)) 
    end as col_2_0_ 
from
    projectdb.product product0_ 
left outer join
    projectdb.rating ratinglist1_ 
        on product0_.prod_id=ratinglist1_.prod_id 
group by
    product0_.prod_id ,
    product0_.prod_name 
having
    case 
        when sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id) is null then 0 
        else round(sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id)) 
    end>=1

For the technical perspective, look at the following line in the above criteria query.

criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, criteriaBuilder.literal(1)));

Its analogous line in the question was written like following.

createQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 1));

See the original expression in the question doing the exact same thing :

Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase()
                                       .when(quotExpression.isNull(), 0)
                                       .<Integer>otherwise(roundExpression);

This expression was attempted to be passed to criteriaBuilder.greaterThanOrEqualTo() as follows.

criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 0));

Pay special attention to the second parameter to greaterThanOrEqualTo() above. It is 0. It should have been criteriaBuilder.literal(0) instead hence, the exception as mentioned in the question.

Thus, always insist upon using CriteriaBuilder#literal(T value) for literal values whenever necessary as done above while using expressions in the CriteriaBuilder#selectCase() construct.

---

_{Tested on Hibernate 4.3.6 final, Hibernate 5.0.5 final alternatively. I will try to run the same query on EclipseLink (2.6.1 final) later on. There should not be a quirk anymore.}

_{EclipseLink has no problem at all with the modified version of the query except that it requires an Object type parameter to the constructor argument (formal parameter), if constructor expressions are used in place of Tuple which this question has nothing to do with after all. This is a long-standing bug in EclipseLink still to be fixed - an analogous example.}

Fulk

@Tinay CriteriaBuilder 's quot() method returns only quotient. How to get fractional part also? – Cockade