[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5902) In Oracle Dialect Paging query always contains "rownum" pseudo-column when the first result > 0

In Oracle Dialect Paging query always contains "rownum" pseudo-column when the first result > 0 ----------------------------------------------------------------------------------------------- Key: HHH-5902 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5902 Project: Hibernate Core Issue Type: Bug Components: core, query-sql Affects Versions: 3.3.2 Environment: Java 1.5, Java 1.6... Reporter: Andremoniy Priority: Critical The issue is about paging query. For example, we have simple SQL Query: "SELECT p.ID FROM SomeTable p ORDER BY p.ID". SQLQuery testQuery = HibernateUtil.currentSession().createSQLQuery("SELECT p.ID FROM SomeTable p ORDER BY p.ID"); When we set: testQuery.setFirstResult(0); testQuery.setMaxResults(10); List objs = testQuery.list(); "objs" will contain 10 simple objects of String type (for example). But, when se set: testQuery.setFirstResult(10); // here, any value > 0 testQuery.setMaxResults(10); List objs = testQuery.list(); we will receive list of Object[2] objects: ID ROWNUM 3212 11 5212 12 5435 13 ... It is absolutely clear, that the core of the problem is in this construction: Oracle9iDialect.class, public String getLimitString(String sql, boolean hasOffset) { ... if (hasOffset) { pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( "); } The resulting query will be: select * from ( select row_.*, rownum rownum_ from ( SELECT p.ID FROM SomeTable p ORDER BY p.ID ) row_ where rownum <=20) where rownum_ > 10 Why this is a bug? 1. Because I don't want to check: if my query will return simple Object types or Object[] depends on "First Result value". 2. Because I don't need to have second pseudo-column with "rownum" value. It must be optional parameter. Simple solution for this example could be (see on >>> ID <<<): select >>> ID <<< from ( select row_.*, rownum rownum_ from ( SELECT p.ID FROM SomeTable p ORDER BY p.ID ) row_ where rownum <=20) where rownum_ > 10 But, of course, in this case this first part of "select" query must be retranslated from inner source query (so, it will be not "SELECT p.ID..." but "SELECT ID" and so on).