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 : Wed Feb 15 2006 - 21:15:34 EST