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

[Reid Sommerville (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5902?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44546#comment-44546 ] 

Reid Sommerville commented on HHH-5902:
---------------------------------------

I was just about to report the same issue, a workaround I used is to use a result mapping, eg for my nativequery "select name as name from users where enabled = 1"

I used 
@SqlResultSetMapping(name="userNameMapping",
    columns={ @ColumnResult(name="NAME") }
   )

and specified that on the nativeQuery create.




> 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.
For more information on JIRA, see: http://www.atlassian.com/software/jira