JPA2 Criteria API creates invalid SQL when using groupBy

Description

JPA2 with the Criteria API seems to generate invalid SQL for PostgreSQL. For this code:

Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class);
Path<DBObject> object = from.get(DBObjectAccessCounter_.object);
Expression<Long> sum = builder.sumAsLong(from.get(DBObjectAccessCounter_.count));
query.multiselect(object, sum).groupBy(object);

I get the following exception:

ERROR: column "dbobject1_.id" must appear in the GROUP BY
clause or be used in an aggregate function

The generated SQL is

select dbobjectac0_.object_id as col_0_0_,
sum(dbobjectac0_.count) as col_1_0_, dbobject1_.id as id1001_,
dbobject1_.name as name1013_,
dbobject1_.lastChanged as lastChan2_1013_,
dbobject1_.type_id as type3_1013_
from DBObjectAccessCounter dbobjectac0_
inner join DBObject dbobject1_
on dbobjectac0_.object_id=dbobject1_.id
group by dbobjectac0_.object_id

This is also the case for a simplified version:

Root<DBObjectAccessCounter> from = query.from(DBObjectAccessCounter.class);
Path<DBObject> object = from.get(DBObjectAccessCounter_.object);
query.select(object).groupBy(object);

Here the following SQL is generated:

select dbobject1_.id as id924_, dbobject1_.name as name933_,
dbobject1_.lastChanged as lastChan2_933_,
dbobject1_.type_id as type3_933_
from DBObjectAccessCounter dbobjectac0_
inner join DBObject dbobject1_
on dbobjectac0_.object_id=dbobject1_.id
group by dbobjectac0_.object_id

This issue is also discussed on Stackoverflow: http://stackoverflow.com/questions/12403399/

I attached a test case for all three examples (multiselect, simplified, simplified with group by id)

Environment

Hibernate 3.6.8.Final, PostgreSQL 9.1.3

Assignee

Unassigned

Reporter

Stephan Windmüller

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Affects versions

Priority

Major
Configure