Session session = ((HibernateSession)em).getHibernateSession();
DetachedCriteria criteria = DetachedCriteria.forClass(Student.class);
Criteria c = dc.getExecutableCriteria(session);
c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);
List result = c.list();
List result1 = c.setFirstResult(0).setMaxResults(10).list();

result has 20 students: correct
But result1 has 4 students, not 10.

I am using JBoss 4.0.4RC1. Thanks for help.

A little hard to see whats going on. I think you need to turn on hibernate sql dumping and see whats being generated. That should show you whats happening.

After turning on Show_SQL, and then execute the generated SQL from mysql command line, each record is duplicated in the returned result set.

For example,

(SQL statement) Limit 0, 6

the result set:
record id(s): 1, 1, 2, 2, 3, 3

So Hibernate returns 3 records because of DISTINCT_ROOT_ENTITY.

(note that student record id starts from 1, such as 1, 2, 3, 4, 5, ....)

------------
(SQL statement) Limit 2, 5

the result set:
record id(s): 2, 2, 3, 3, 4
(duplicate records(id=1) skipped)

So Hibernate returns 3 records because of DISTINCT_ROOT_ENTITY.

-------------
The generated SQL statement ends with limit ?, I could not figure out a way to find the value passed to the question mark.

For the following query:

session.createCriteria(Student.class)
.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
.setFirstResult(0).setMaxResults(10).list();

I expect it to return the first 10 distinct records, but only 5 (from record 1 to 5)


For
session.createCriteria(Student.class)
.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
.setFirstResult(10).setMaxResults(10).list();

I expect it to return the next 10 distinct records, starting from record id 11, but returned records from 6 to 10.

It seems that Hibernate applies DISTINCT_ROOT_ENTITY semantics after retriving the results from DB, but it should be the part of SQL statement in order to get correct results.

Is there a solution? Thanks a lot for help.