JPQL Group By not working

Question

This is my simple JPQL:

SELECT s
FROM Site s
GROUP BY s.siteType

siteResult = q.getResultList();
for (Site site : siteResult) {
    // loops all sites
}

This query returns all sites, including sites of the same siteType. I'm using JPA 2.0 Eclipselink. Whats wrong here?

Answer 1

Such a query does not make sense. If you use GROUP BY, other attributes in SELECT should be aggregated. As it is said in JPA specification:

The requirements for the SELECT clause when GROUP BY is used follow those of SQL: namely, any item that appears in the SELECT clause (other than as an aggregate function or as an argument to an aggregate function) must also appear in the GROUP BY clause. In forming the groups, null values are treated as the same for grouping purposes.

If you think SQL counterpart of your query:

SELECT s.attr1, attr2, s.siteType
FROM site s
GROUP BY (s.siteType)

you notice that it is hard to imagine which possible value of attr1 and attr2 should be chosen.

In such a case EclipseLink with derby just drops GROUP BY away from the query, which is of course little bit questionable way to handle invalid JPQL. I like more how Hibernate+MySQL behaves with such a invalid JPQL, it fails with quite clear error message:

java.sql.SQLSyntaxErrorException: The SELECT list of a grouped query contains at least one invalid expression. If a SELECT list has a GROUP BY, the list may only contain valid grouping expressions and valid aggregate expressions.

Answer to comment: One Site contains probably also attributes other than siteType as well. Lets use following example:

public class Site {
    int id; 
    String siteType;
} 

and two instances: (id=1, siteType="same"), (id=2, siteType="same"). Now when type of select is Site itself (or all attributes of it) and you make group by by siteType, it is impossible to define should result have one with id value 1 or 2. Thats why you have to use some aggregate function (like AVG, which gives you average of attribute values) for remaining attributes (id in our case).

Behind this link: ObjectDB GROUP BY you can find some examples with GROUP BY and aggregates.