Hibernate SELECT DISTINCT behavior

Question

I hope that this question is not duplicate, because in another case I use google badly (shame on me) :-)

So, I can't find exact and clear answer for my question: Is "SELECT DISTINCT ..." in hql applied before setFirstResult() and setMaxResults()? I want to be calm that I will not have problems with pagination in my app.

Solution

To answer the actual question, yes the distinct result set would be determined before the limit would be applied. The setFirstResult() and setMaxResults() methods essentially are equivalent to a LIMIT clause on the query.

If you use both you are requesting that the distinct result set be limited between the indexes given to your two method calls above.

An example:

You have a result set with 100 records. There are 20 distinct records found for column X. You set up the following criteria:

Criteria critiera = getCurrentSession().createCritiera(Foo.class);
criteria.add(Projections.distinct(Projections.property("X")));
criteria.setFirstResult(0);
criteria.setMaxResults(10);
List<Foo> fooList = critiera.list();

fooList would contain 10 results, from the start of the distinct result set of 20 results.

If you changed the above to

criteria.setFirstResult(4);
criteria.setMaxResults(20);

fooList would contain 15 results omitting the first 5 results.

_{It could be ambiguous how the result set would be ordered, so you may not get the same 10 results every time.}