force hibernate to use offset query when offset is 0

pasha · **Joined:** Sat Nov 27, 2004 4:13 am **Posts:** 137

Hibernate version: Core 3.2.2 ga + Annotations 3.3.0 ga
Name and version of the database you are using: Oracle 10g r2

the following is the query hibernate generates with offset and limit in oracle 10g:

Code:

select * from (
select row_.*, rownum rownum_ from ( /* criteria query */ select this_.id as id0_0_, this_.code as code0_0_, this_.description as descript4_0_0_, this_.priority as priority0_0_, this_.title as title0_0_, this_.fk_lkt as fk1_0_0_ from cmn.tb_lookup this_ where this_.fk_lkt='province' ) row_ where rownum <= ?
) where rownum_ > ?

and when the offset is zero the following query is generated

Code:

select row_.*, rownum rownum_ from ( /* criteria query */ select this_.id as id0_0_, this_.code as code0_0_, this_.description as descript4_0_0_, this_.priority as priority0_0_, this_.title as title0_0_, this_.fk_lkt as fk1_0_0_ from cmn.tb_lookup this_ where this_.fk_lkt='province' ) row_ where rownum <= ?

the result of executing second query, and first query with last parameter equals by zero is different (different rows are returned by oracle); it seems that this is a Oracle's problem, is there any way to force Hibernate to use offset part of query when offset is zero.