title: Hibernate Criteria: distinct entities and then limit
body: <p>I have a criteria that returns all data the application requires, basically:</p>
  
  <pre><code>Criteria criteria = session.createCriteria(Client.class);
  criteria.createAlias("address", "address");
  criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
  criteria.setFirstResult(init);
  criteria.setMaxResults(max);
  List&lt;Client&gt; clients = criteria.list();
  </code></pre>
  
  <p>The problem is that the relation client / address is bidirectional: on client has one address and one address may belong to more than one client.</p>
  
  <p>I want to retrieve "single" client objects based on their pk of course, some number of clients as they are displayed in a table.</p>
  
  <p>Because the setFirstResult/setMaxResults are executed first I am getting duplicated clients within the already applied limits. After (application level as not group by was used) hibernate gets rids of the duplicate clients so I end up with less clients that the maximum specified in the setMaxResults.</p>
  
  <p>Cannot group by (projection group) as it won't return all columns required in client/addresses, only the group the query is grouping by.</p>
  
  <p>(To sum up, My table has 100 results per page but after discarding duplicates I have 98 results instead of 100...) that is because the limit : LIMIT 0,100 is applied BEFORE hibernate groups when it should be performed AFTER)</p>