In Oracle Dialect Paging query always contains "rownum" pseudo-column when the first result > 0

Description

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

Environment

Java 1.5, Java 1.6...

Activity

Show:
Reid Sommerville
December 8, 2011, 5:41 PM

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.

Brett Meyer
April 7, 2014, 5:42 PM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Brett Meyer
July 8, 2014, 3:12 PM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Assignee

Unassigned

Reporter

AndreyL

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Critical
Configure