Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Fixed
-
3.0, 3.0.2
-
Database: SQL Server 2008 R2 64 Bit (10.50.1617.0) Windows 2008 Server R2 Enterprise.
Web App: Ubuntu 11.04 64Bit, Java 1.6.0_26 64-Bit Server VM (build 20.1-b02, mixed mode)
Description
As discussed in the thread: http://mail-archives.apache.org/mod_mbox/cayenne-user/201107.mbox/%3C7F90E4A4-5F66-48C4-BF47-C68746E34347@objectstyle.org%3E
The issue occurs when using setFetchOffset and setFetchLimit to do pagination on SQL Server 2008 R2. Any time the offset is set to anything other than 0 no results are returned by the SQL query. Example as follows:
SelectQuery query = getPrototypeQuery().queryWithParameters(params, true);
query.setFetchOffset(offset);
query.setFetchLimit(limit);
List<ConsentForm> result = getDataContext().performQuery(query);
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.