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 >>> >> >> >
