Criteria API returns a too small resultset

Question

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.

Answer 1

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!

Answer 2

The setMaxResults does not work with outer join SQL queries. Maybe this is your problem: Hibernate does not return distinct results for a query with outer join fetching enabled for a collection (even if I use the distinct keyword)?.