We're updating the issue view to help you get more done. 

Grouping by entity does not work for some dialects

Description

Server which does not support columns in the select list if not contained in the "group by" clause, fail when grouping by entity.

For example, this fails on SQL Server 2012 (but succeed in H2):

1 2 3 4 5 6 7 CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<Tuple> cq = cb.createTupleQuery(); Root<Foo> foo = cq.from(Foo.class); Path<Bar> bar = foo.get("bar"); cq.multiselect(bar, cb.count(foo)); cq.groupBy(bar); entityManager.createQuery(cq).getResultList();

The generated query is:

1 2 3 4 5 6 7 8 9 10 11 12 select foo0_.[bar_id] as col_0_0_, count(foo0_.[id]) as col_1_0_, bar1_.[id] as id1_0_, bar1_.[name] as name2_0_ from [dbo].[Foo] foo0_ inner join [dbo].[Bar] bar1_ on foo0_.[bar_id]=bar1_.[id] group by foo0_.[bar_id]

which fails with this error:

Column 'dbo.Bar.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

In H2 the same query doesn't fail, because it supports additional columns in the select list if those columns are known to be "dependent" on the grouped ones.

Environment

SQL Server 2012

Status

Assignee

Unassigned

Reporter

Giovanni Lovato

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.7

Priority

Major