[Hibernate-issues] [Hibernate-JIRA] Commented: (HB-113) Oracle LIMIT Bug (Was HB-88)

[<leg...@at...>]

The following comment has been added to this issue:

     Author: Leonardo Quijano
    Created: Fri, 6 Jun 2003 12:45 PM
       Body:
Anything you need to confirm this? I don't like keeping a self-patched version of Hibernate around... specially in a release for a client ;).

Thanks
---------------------------------------------------------------------
View the issue:

  http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-113


Here is an overview of the issue:
---------------------------------------------------------------------
        Key: HB-113
    Summary: Oracle LIMIT Bug (Was HB-88)
       Type: Bug

     Status: Assigned
   Priority: Major

    Project: Hibernate2
 Components: 
             core

   Assignee: Gavin King
   Reporter: Leonardo Quijano

    Created: Thu, 29 May 2003 7:19 PM
    Updated: Thu, 29 May 2003 8:52 PM
Environment: CVS May 29, 2003

Description:
I'm reopening bug HB-88 (I don't know how to reopen bugs like in Bugzilla.. :( ). It's still wrong. Right now, we have in Oracle9Dialect:

public String getLimitString(String sql) {
	StringBuffer pagingSelect = new StringBuffer(100);
	pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
	pagingSelect.append(sql);
	pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ >= ?");
	return pagingSelect.toString();
}

From Oracle 9i Documentation (http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements6a.htm#27457)

"ROWNUM

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on."

In Hibernate's doc, it says:

"setFirstResult

public Query setFirstResult(int firstResult)

    Set the first row to retrieve. If not set, rows will be retrieved beginnning from row 0. This method is more efficient if the JDBC driver supports scrollable ResultSets.

    Parameters:
        firstResult - a row number, numbered from 0"

So, if I call query.setFirstResult(0) - as documented - it should give me the first result in Oracle (Oracle ROWNUM 1). So maybe it should be:

	pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");

or

	pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ >= (? + 1)");

(don't know if the last one works, though). 

I don't know if the value is increased in some other place... but it's still not working for me. I applied the change and it did work. 

Leonardo





---------------------------------------------------------------------
JIRA INFORMATION:
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/Administrators.jspa

If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira