Hibernate version: 2.1.4

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using: DB2/NT 8.1.2

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:



Hi all,

I have a parent child relationship where parent has set of children (bi-directional one-to-many).
I am trying retreive parent objects by child attributes using HQL. As relation from parent to child is 1-*
I am getting duplicate parent objects and when I apply distinct key word I am getting expected results.
But as the query results could be big I need to apply setMaxResults on the query but this is causing problem.
It distincts over rownumber() rather than parent Id again I am getting duplicate results.


My HQL looks as below


select distinct c.parent
from com.abc.Child c
where c.attr1 = ?


Without setMaxResults() on Query it works fine but not when it is applied..
generated SQL for DB2 is as below


select * from ( select rownumber() over() as row_,...) ) as temp_ where row_ <= ?



Anyone have any suggestions on how to avoid duplicates.Thanks you all.



Regards,
Surya

sorry generted SQL is as below

select * from ( select distinct rownumber() over() as row_,...) ) as temp_ where row_ <= ?