You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by "Dzmitry Kazimirchyk (JIRA)" <ji...@apache.org> on 2011/08/16 17:21:28 UTC
[jira] [Closed] (CAY-1596) setFetchOffset & setFetchLimit issue
under SQL Server 2008 R2 64Bit
[ https://issues.apache.org/jira/browse/CAY-1596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dzmitry Kazimirchyk closed CAY-1596.
------------------------------------
Resolution: Fixed
Fix Version/s: 3.1M3
3.0.3
Assignee: Dzmitry Kazimirchyk
> setFetchOffset & setFetchLimit issue under SQL Server 2008 R2 64Bit
> -------------------------------------------------------------------
>
> Key: CAY-1596
> URL: https://issues.apache.org/jira/browse/CAY-1596
> Project: Cayenne
> Issue Type: Bug
> Components: Core Library
> Affects Versions: 3.0, 3.0.2
> Environment: 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)
> Reporter: Gary Jarrel
> Assignee: Dzmitry Kazimirchyk
> Labels: limit, offset, pagination, query, select, sql
> Fix For: 3.0.3, 3.1M3
>
>
> 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.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira