Yes please. This needs to be investigated. On Jul 26, 2011, at 2:25 PM, Gary Jarrel wrote:
> Should I still lodge a Jira? > > Gary > > On Mon, Jul 25, 2011 at 8:02 PM, Gary Jarrel <[email protected]> wrote: >> I am not sure if this would fix it. I've just checked out the 3.0 >> source code, applied the patch, built Cayenne and and ran my code >> against it, and am having the same problem. >> >> Could it perhaps be because SQL Server 2008 does not have the offset >> feature? Well at least not until SQL Server 2011 (based on >> http://stackoverflow.com/questions/187998/row-offset-in-sql-server) or >> should Cayenne be falling back to in memory limits and offsets? >> >> Gary >> >> On Sun, Jul 24, 2011 at 8:26 PM, Andrus Adamchik <[email protected]> >> wrote: >>> I just found the Jira that I had in mind when answering your email: >>> >>> https://issues.apache.org/jira/browse/CAY-1539 >>> >>> I just committed the patch to trunk. Still suspect your case may be related >>> somehow. >>> >>> Andrus >>> >>> On Jul 23, 2011, at 10:56 AM, Gary Jarrel wrote: >>> >>>> I will do some more research to try to narrow down why this is >>>> happening tomorrow and then post a Jira. >>>> >>>> I also removed DISTINCT and any ordering and query parameters and the >>>> result is still the same. >>>> >>>> Gary >>>> >>>> On Thu, Jul 21, 2011 at 9:48 PM, Andrus Adamchik <[email protected]> >>>> wrote: >>>>> Hi Gary, >>>>> >>>>> the use of the API seems correct. Not sure what's the deal here. Could be >>>>> a bug. (wonder if DISTINCT combination with offset/limit is causing this, >>>>> as there is some in-memory processing involved). Could you please open a >>>>> Jira and include the contents of the prototype query. >>>>> >>>>> Andrus >>>>> >>>>> >>>>> On Jul 20, 2011, at 5:52 PM, Gary Jarrel wrote: >>>>>> Hi Guys, >>>>>> >>>>>> I have an issue with using setFetchOffset & setFetchLimit together >>>>>> with SQL Server 2008 R2. >>>>>> >>>>>> Basically the code looks something like this: >>>>>> >>>>>> SelectQuery query = getPrototypeQuery().queryWithParameters(params, >>>>>> true); >>>>>> query.setFetchOffset(offset); >>>>>> query.setFetchLimit(limit); >>>>>> List<ConsentForm> result = getDataContext().performQuery(query); >>>>>> >>>>>> I would have thought that given for example offset 10 and limit 20 I >>>>>> could get a subset of the data in the database. Yet the generated code >>>>>> that I get from the logs is as follows keeping in mind I am using >>>>>> ordering and distinct: >>>>>> >>>>>> Page 1: Offset 0 Limit 25 >>>>>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, >>>>>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, >>>>>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE >>>>>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: >>>>>> 1->CustomerID:8] >>>>>> === returned 25 rows. - took 26 ms >>>>>> >>>>>> The above works as expected however the code appears strange. >>>>>> >>>>>> Page 2: Offset 25 Limit 25 >>>>>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, >>>>>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, >>>>>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE >>>>>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: >>>>>> 1->CustomerID:8] >>>>>> === returned 0 rows. - took 20 ms >>>>>> >>>>>> This already stops working >>>>>> >>>>>> Page: 3: Offset 50 Limit 25 >>>>>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder, >>>>>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress, >>>>>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE >>>>>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind: >>>>>> 1->CustomerID:8] >>>>>> === returned 0 rows. - took 20 ms. >>>>>> >>>>>> Basically the first page appears to be working fine, but nothing >>>>>> afterwards. >>>>>> >>>>>> I am not certain if I am using the combination of setFetchOffset and >>>>>> setFetchLimit in a correct manner! >>>>>> >>>>>> Any assistance would be appreciated. Thank you! >>>>>> >>>>>> Gary >>>>>> >>>>> >>>>> >>>> >>> >>> >> >
