When using limit queries with the DB2 dialect, the SQL generated looks something like this:
select * from ( select
distinct rownumber() over ( ) as row_ from $fromClause where $whereClause ) where row_ between...
this unfortunately does not produce the desired result, as the inclusion of the rownumber() function in the result set makes every row distinct anyway.
Proposed fix for the DB2Dialect:
Code:
public String getLimitString(String sql, boolean hasOffset)
{
StringBuffer pagingSelect = new StringBuffer(sql.length() + 100).append(
"select * from(select temp_.*, rownumber()over(order by 1)as row_ from(")
.append(sql)
.append(" ) as temp_) as temp2_ where row_ ");
if (hasOffset)
{
pagingSelect.append("between ?+1 and ?");
} else
{
pagingSelect.append("<= ?");
}
return pagingSelect.toString();
}
which will generate select * from ( select *, rownumber() over () as row_ from (select distinct $selectClause FROM $fromClause WHERE $whereclause)...).
This uses an additional subselect, however.
Max
Hibernate version: 2.1.6
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:
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
Login
FAQ
Search
