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 - 16:17:28 EST