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):
{code:java}
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();
{code}
The generated query is:
{code:sql}
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]
{code}
which fails with this error:
{quote}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.{quote}
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. |
|
