I am not very sure if it's a problem, it seems oracle's problem, I have fired an issue on tracker, but I think it maybe not a prolem now.

data base : oracle 8.1.7
dialect: net.sf.hibernate.dialect.Oracle9Dialect
when I use the query with paging like this:
Query query = session.createQuery(hql);
query.setFirstResult(firstResultPosition);
query.setMaxResults(maxResultSize);
query.list(); //...blahblah

the dialect generate the following SQL(formatted for short):
select * from
(select row_.*, rownum rownum_ from
(select omcttermin0_.* from OMCT_TERMINAL omcttermin0_ order by omcttermin0_.ALARM_STATUS) row_
where ROWNUM <= 20)
where rownum_ >= 0

in the table OMCT_TERMINAL, the value of column ALARM_STATUS is int(non-order and maybe 1,2,3 or 4)

the result of the above sql is NOT equal to the top 20 lines of the inner sql : select omcttermin0_.* from OMCT_TERMINAL omcttermin0_ order by omcttermin0_.ALARM_STATUS
because the ROWNUM is changed after the "order by ALARM_STATUS" execution

BTW:
I wrote a dialect (extends the Oracle9Dialect) and change the sql to :
select * from
(select row_.*, rownum rownum_ from
(select omcttermin0_.* from OMCT_TERMINAL omcttermin0_ order by omcttermin0_.ALARM_STATUS) row_
)
where rownum_ >= 0 AND rownum_ <= 20

and found it works well (but maybe have some performance issues).

for more, if I change the inner sql to :
select * from
(select row_.*, rownum rownum_ from
(select omcttermin0_.* from OMCT_TERMINAL omcttermin0_ order by omcttermin0_.ALARM_STATUS, omcttermin0_.ID) row_
where ROWNUM <= 20)
where rownum_ >= 0

(means add the primary key to the order by clause)

the result is ok (whole SQL's result equals to top 20 of inner SQL's)

Glad you found a work around. The dialect is fine the way it is. JIRA has my comments for why the paging is best implemented as is.

I have the same problem. For example, in a query that used the pagination functionality, the second result page contained results that were already contained in the first result page. The cause of the problem is that the two different SQL statements, which are generated by hibernate for the first page resp. all following pages, behave differently (we are using Hibernate 3.0.5 and Oracle 10.2).

For the first page, the gerated SQL looks like:



For the following pages, the gerated SQL looks like this:



In some cases, the ordering of the results is not identical. In our case, all results that were returned by the actual query were equal in respect of the ordering clause of the query and this somehow caused the occurence of the same result in more than one pages. This does not happen if we also use the second kind of statement for the first page.

In my opinion, the query for the first result page should have the same syntax as for the other queries. The performance may be slower, but it is guranteed that the behavior is the same. And it would be better than to always use the workaround mentioned above (but thanx for that!). Or are there other solutions available now?

I tried to find the JIRA issue with the comments, but I did not find it. How can I find it?