> Can someone please open a bug report.
Ok, I just did it myself :-)
http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-450
On Feb 15, 2006, at 9:15 PM, Andrus Adamchik wrote:
> Certainly looks like a bug in translator. Can someone please open a
> bug report.
>
> The fix should be pretty straightforward - remove rownum from
> OracleQualifierTranslator and move it to
> OracleSelectTranslator.createSqlString() to follow the syntax
> suggested by Dov.
>
> Andrus
>
>
> On Feb 15, 2006, at 4:17 PM, Bryan Lewis wrote:
>
>> We had a similar surprise today. Previous versions of Cayenne
>> handled the fetchLimit in a different way, probably in code. Now
>> Cayenne adds the rownum qualifier, which seems to be a better way
>> to do it, but... we had a query with both a fetchLimit and an
>> ordering (as you did) because we wanted the five most recent
>> records. Now Oracle fetches the first five records before it does
>> the ordering. I've worked around it by removing the fetchLimit on
>> queries where the ordering affects the result set.
>>
>>
>>
>> Dov Rosenberg wrote:
>>> We are having an issue while trying to run queries that use a
>>> fetch limit in Oracle. The cayenne adapter seems to generate a
>>> SQL query something like
>>>
>>> select *
>>> from CONTENTTEXTPUB
>>> where contentid in (select recordid from content where
>>> CONTENTCHANNELID = '8010149aafb30fe21075891007e80')
>>> and rownum<=5
>>> order by INDEXMASTERIDENTIFIERS
>>>
>>> Which does not return the correct number of rows. In this case it
>>> returns 2 rows instead of 4. By embedding the rownum inside the
>>> main query it looks like Oracle applies it before filtering the
>>> data.
>>>
>>> If we change the query to look something like:
>>>
>>> select * from
>>> ( select *
>>> from CONTENTTEXTPUB
>>> where contentid in (select recordid from content where
>>> CONTENTCHANNELID = '8010149aafb30fe21075891007e80')
>>> order by INDEXMASTERIDENTIFIERS
>>> )
>>> where rownum <= 5 ;
>>>
>>> Then the correct number of rows is returned. Obviously this is
>>> Oracle specific only.
>>>
>>> Question: How can we change the way the fetchlimit is implemented
>>> in Cayenne for Oracle only? Cayenne SQL server seems to apply the
>>> fetch limit during the result set traversal. Our goal would be to
>>> use that same mechanism for Oracle.
>>>
>>>
>>> --
>>> Dov Rosenberg
>>> Inquira Inc
>>> 370 Centerpointe Circle, ste 1178
>>> Altamonte Springs, FL 32701
>>> (407) 339-1177 x 102
>>> (407) 339-6704 (fax)
>>> drosenber..nquira.com
>>> AOL IM: dovrosenberg
This archive was generated by hypermail 2.0.0 : Sat Feb 18 2006 - 17:52:31 EST