Bug 1247316

Description of problem:

Hibernate Envers (Criteria API) automatically adds a clause 'order by' by ID Revision when not is added projection and not is added order. This procedure burdening the query performance and show warning that the I/O limit was exceeded.

Version-Release number of selected component (if applicable):
Hibernate Envers 4.2.12.Final

How reproducible:
Always

Steps to Reproduce:

AuditQuery query = getAuditReader().createQuery().forRevisionsOfEntity(MyEntity.class, false, true);

With 'order by' the query uses 300,000 and without 'order by' 30,000 bytes. It is an audit table with more than 5 million lines.

Customer seeks between 30 or 100 results:

query.setFirstResult (1);
query.setMaxResults (30);

Even without adding a clause 'order by' (query.addOrder (...);), envers does. The generated query looks like this, for example:

select *
  from (select and. *
          from MY_ENTITY e,
               MY_REVISION_ENTITY re,
         where e.ID_REVISAO = re.ID_REVISAO
           and re.ID_COL_EXTRA = 1234
        order by e.ID_REVISAO desc -- PROBLEM!!!
) Where rownum <= 30;

Actual results:
-

Expected results:
-

Additional info:

Customer has noticed that the org.hibernate.envers.query.impl.RevisionsOfEntityQuery class has the following code block in the list() method:

 if (!hasProjection && !hasOrder) {
   String revisionPropertyPath = verEntCfg.getRevisionNumberPath();
   qb.addOrder(revisionPropertyPath, true);
 }