Hibernate version: 3.2.1 and 3.2.6
Oracle version: 10g with Oracle9Dialect.

We seem to have found an error in the way Hibernate processes the
combination of a distinct with a result limit in HQL. The HQL result
will only contain 10 rows, but if you run the equivalent SQL query the
result has the correct number of rows.

Here is a reproduction, UnitTestFactory in our system is just a place
to hang the Hibernate SessionFactory injected by Spring.

public void runHibernateSearch() {

UnitTestFactory utf = UnitTestFactory.getFactory();
SessionFactory sf = utf.getSessionFactory();
Session s = sf.openSession();

int maxResults = 100;

String hql = "select distinct identityAlias.name, identityAlias.id from sailpoint.object.Identity identityAlias";
Query q = s.createQuery(hql);
q.setMaxResults(maxResults);

List rows = q.list();
// there will be 10 rows
println("Result had " + rows.size() + " rows");

String sql = "select distinct identityAlias.name, identityAlias.id from ciq_identity identityAlias";
q = s.createSQLQuery(sql);
q.setMaxResults(maxResults);

rows = q.list();
// there will be 100 rows
println("Result had " + rows.size() + " rows");
}

When show_sql is on, the following statement is logged for the HQL query:

Hibernate: select * from ( select distinct identity0_.name as col_0_0_, identity0_.id as col_1_0_ from ciq_identity identity0_ ) where rownum <= ?

And this is logged for the SQL query:

Hibernate: select * from ( select distinct identityAlias.name, identityAlias.id from ciq_identity identityAlias ) where rownum <= ?


Hibernate trace does not show the value being substituted for the
rownum parameter. When we cut the logged SQL statement and paste it
into a JDBC query tool, substituting 100 for the ? it returns the
correct result. The SQL being generated therefore appears to be
correct, it is the parameter value being used for the rownum comparison
that is incorrect.

What is also interesting is that when maxResults is set to a number
less than 10 then both queries return the same number of rows. It is
only when maxResults is greater than 10 that the queries are different.

If you remove the "distinct" the queries are the same.

If you remove identityAlias.id from the select list and only select
distinct identityAlias.name the queries are the same.

Any suggestions would be greatly appreciated!

Also note that this only happens on Oracle. It works on MySQL.

Here is an even simpler example. We run the
same HQL query twice. The first time the first row
is zero and the second time the first row is 1. The
first query returns 10 rows and the second 100 rows.

Session s = sf.openSession();

int maxResults = 100;

String hql = "select distinct identityAlias.name, identityAlias.id from sailpoint.object.Identity identityAlias";
Query q = s.createQuery(hql);
q.setMaxResults(maxResults);

List rows = q.list();
// there will be 10 rows
println("Result had " + rows.size() + " rows");

q = s.createQuery(hql);
q.setFirstResult(1);
q.setMaxResults(maxResults);

rows = q.list();
// there will be 100 rows
println("Result had " + rows.size() + " rows");

After more investigation this appears to be a JDBC driver
problem. If you prepare a statement that has a one
sided rownum comparison,

select * from ... where rownum <= ?";

The driver we use will only return 10 rows. If you
have both ends of the range it works.

select * from ... where rownum >= ? and rownum <= ?";

Unfortunately when the starting rownum is zero, Hibernate
optimizes out the >= term and we hit the driver bug.

The driver we are using is from Oracle, version 10.2.0.1.0

While this is apparently an Oracle bug, it would be nice if
Hibernate could work around it by adding a rownum >= ? term
with a zero value.

I know you're all just riveted by this conversation I'm having with
myself so here's the latest tidbit.

This does appear to be a driver problem but it is related
to the unusual syntax Hibernate is generating. Here is
the HQL:

select distinct identityAlias.name, identityAlias.id
from sailpoint.object.Identity identityAlias";

With FirstResult = 0 and MaxResults = 100 in the Query.

The SQL that is generated is this:

select * from (
select distinct identity0_.name as col_0_0_,
identity0_.id as col_1_0_
from ciq_identity identity0_ ) where rownum <= ?

The inner query does the work, then we're wrapping it in a funky
select * with the rownum being applied to the outer result. I have
verified that if you try to run this SQL in a PreparedStatement with
100 bound to the first parameter you only get 10 rows back. Clearly a
bug. But if you remove the unusual wrapping:

select distinct identity0_.name as col_0_0_,
identity0_.id as col_1_0_
from ciq_identity identity0_ where rownum <= ?";

it works as expected. So while this is arguably a driver bug
I do think it is something Hibernate needs to address since it
is in control of the syntax.

This BTW is a problem in all versions of the Oracle driver
from 10.2.0.1 through 11.1.0.6.

FYI:

We got around this issue by using our own dialect.



We also found that this only happens (in our case) when you have an order by clause on your query (we built it by Criteria). Weird!

Thanks for such a detailed explanation of this issue. I'm seeing exactly the same thing happening in my code. Has this issue been logged as a bug/enhancement either to oracle or hibernate?

Is your order by column returning unique values? Refer to the bottom part of this article: http://www.oracle.com/technology/oramag ... sktom.html

I have the Same Problem with Oracle 10.2.0.3.0 and JDBC Driver ojdbc6-11.1.0.7.0.jar

The following Implementation solves the Problem.



@jwcarman
Thanks for the right hint

UPDATE: It seems to be fixed in Oracle 11.2.0.1.0