> 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).
I have cleaned up the patch. I hope it's ok now (I am new to Turbine coding, sorry...) > 4. Test cases are more than welcome. I have attached a simple one. It does two queries one with setLimit and one with setLimit and setOffset. They both produce incorrect results. By the way, while creating the test case I discovered that: Tab1Peer.doDelete( new Criteria() ); does not delete any rows. To remove all rows from the table you have to add a condition that is always true. I don't know if this is a feature or a bug, but it seemed like a bug to me. > 5. Posting then issue into Scarab (scarab.werken.com/issues) will ensure > that it does not get lost on the mailing list. I'll try that. I am new to Scarab too... > 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. I noticed that while switching from torque-3.0-b2 to torque-3.0-b4. I had the following code: Criteria criteria = new Criteria(); criteria.add(...); criteria.setLimit( ... ); criteria.setOffset( ... ); log.debug( criteria.toString() ); List l = Tab1Peer.doSelect( criteria ); In torque-3.0-b4 the criteria.toString() resets the limit and offset. -- Rafal > 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 > > > > >
patch
Description: Binary data
oracle_limit_test_case.zip
Description: Zip compressed data
-- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
