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

[Andremoniy (JIRA)]

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).


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira