[hibernate-issues] [Hibernate-JIRA] Created: (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).
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira