How is this possible, I have to following criteria
Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();
The size of list is now 20. If I add a max results to the criteria,
Criteria criteria = getSession().createCriteria(c);
criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
criteria.setMaxResults(90);
criteria.add(Restrictions.eq("active",true));
List list = criteria.list();
.. now list's size is 18!
I don't understand how the resultsets size can be smaller after defining the max results, as the amount of rows is smaller than the defined max. This sure seems like a bug, or is there again some weird aspects of hibernate that I'm not aware of?
If you're looking for an answer to this question, make sure to read the accepted answer and its comments.
What is happening here can be seen very clearly by turning on SQL debugging in Hibernate and comparing the generated queries.
Using a fairly simple Sale
→ Item
one-to-many mapping (which is hopefully self-explanatory), a Criteria
-based query like this:
Criteria c = sessionFactory.getCurrentSession().createCriteria(Sale.class);
c.createAlias("items", "i");
c.add(Restrictions.eq("i.name", "doll"));
c.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
c.setMaxResults(2);
produces SQL like this:
select top ? this_.saleId as saleId1_1_, ...
from Sale this_
inner join Sale_Item items3_ on this_.saleId=items3_.Sale_saleId
inner join Item items1_ on items3_.items_id=items1_.id
where items1_.name=?
whereas a Query
like this:
Query q = sessionFactory.getCurrentSession().createQuery("select distinct s from Sale s join s.items as i where i.name=:name");
q.setParameter("name", "doll");
q.setMaxResults(2);
produces something like:
select top ? distinct hibernated0_.saleId as saleId1_
from Sale hibernated0_
inner join Sale_Item items1_ on hibernated0_.saleId=items1_.Sale_saleId
inner join Item hibernated2_ on items1_.items_id=hibernated2_.id
where hibernated2_.name=?
Note the difference in the very first line (DISTINCT
). A ResultTransformer
like DISTINCT_ROOT_ENTITY
is a Java class, which processes the results of the SQL rows after the SQL is executed. Therefore, when you specify a maxResults
, that will be applied as a row limit on the SQL; the SQL includes a join onto the elements in the Collection
, so you're limiting your SQL result to 90 sub-elements. Once the DISTINCT_ROOT_ENTITY
transformer is applied, that may result in less than 20 root elements, purely dependent on which root elements happen to come out first in the 90 joined results.
DISTINCT
in HQL behaves very differently, in that that actually uses the SQL DISTINCT
keyword, which is applied before the row limit. Therefore, this behaves as you expect, and explains the difference between the 2.
In theory you should be looking at setProjection
to apply a projection at the SQL level -- something like c.setProjection(Projections.distinct(Projections.rootEntity()))
-- but unfortunately Projections.rootEntity()
doesn't exist, I just made it up. Perhaps it should!