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