Rafal, I don't use Oracle so I cannot say whether or not your solution does what it is meant to do, however here are a few comments.
1. It's excellent that you are looking at this. We discussed supporting these for Oracle a couple of months ago but never reached any conclusion. 2. Please follow the turbine coding standards: http://jakarta.apache.org/turbine/common/code-standards.html 3. Please submit your patch in unidif format (cvs diff -u). 4. Test cases are more than welcome. 5. Posting then issue into Scarab (scarab.werken.com/issues) will ensure that it does not get lost on the mailing list. I haven't had a chance to have a good look at the code that resets the limit and offset, but I think I can see the problem you describe (it reminds me of the TemplateLink.toString() method which clears the path info and query data - i.e. an unexpected side-effect). It should be pretty easy to put together a test case to demonstrate whether or not this is a problem. Cheers, Scott -- Scott Eade Backstage Technologies Pty. Ltd. http://www.backstagetech.com.au > From: "Rafal Maczewski" <[EMAIL PROTECTED]> > Reply-To: "Turbine Torque Developers List" > <[EMAIL PROTECTED]> > Date: Mon, 23 Sep 2002 12:03:43 +0200 > To: <[EMAIL PROTECTED]> > Subject: [patch] limit and offset on Oracle databases > > Hello, > > There is a problem when using Criteria limit and offset with Oracle > database. For example: > Criteria criteria = new Criteria(); > criteria.addAscendingOrderByColumn( Tab1Peer.COL2 ); > criteria.setLimit( 10 ); > List list = Tab1Peer.doSelect( criteria ); > > Now list does not contain correct results. This is caused by the way SQL > query is created and by the way Oracle uses the rownum pseudocolumn. > The query created by Torque lookes like this: > > SELECT tab1.col1, tab2.col2 FROM tab1 WHERE rownum <= 10 ORDER BY tab1.col2 > > Now Oracle gets 10 rows from the database in any order and then sorts them > using the ORDER BY column. > > Things get even worse when using limit and offset at the same time. > Torque uses rownum for limiting number of rows and manually skips "offset" > results. So on Oracle using: > criteria.setLimit( 10 ); > criteria.setOffset( 100 ); > will not return any rows. > > What I suggest to solve this problem is to rewrite the original query (using > Oracle rownum pseudocolumn and subqueries) so that the results returned > would be correct and we would not have to scroll through the results when > using criteria.setOffset(); > > So instead of the following query: > > SELECT tab1.col1, tab1.col2 FROM tab1 ORDER BY tab1.col2 > > we can build query: > > SELECT B.* FROM ( > SELECT A.*, rownum as TORQUE$ROWNUM FROM ( > SELECT tab1.col1, tab1.col2 FROM tab1 ORDER BY tab1.col2 > ) A > ) B WHERE B.TORQUE$ROWNUM > offset > AND B.TORQUE$ROWNUM <= offset + limit > > This query correctly sorts the results and solves the "rownum problem" and > it runs much faster with large offsets, since the rows that are skipped are > processed on the server and are not sent to the client. > > I have attached a patch for BasePeer.java that creates the above query. > Also, I think that something should be done about the way the function > BasePeer.createQueryString works. It takes criteria object and creates query > string for that object. The problem is that when database supports native > limit and/or offset, this function will modify passed criteria object and > reset limit and/or offset to default value. So the criteria object passed to > the doSelect function is modified inside that function. If you do two > selects using the same function you may get different results. And even > worse, if you call criteria.toString(), the BasePeer.createQueryString > function is called, which resets the limit and offset for the criteria > object. > > > Regards, > > Rafal > > > > -- > To unsubscribe, e-mail: > <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: > <mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
