cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gary Jarrel (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (CAY-1596) setFetchOffset & setFetchLimit issue under SQL Server 2008 R2 64Bit
Date Thu, 28 Jul 2011 14:48:09 GMT

     [ https://issues.apache.org/jira/browse/CAY-1596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Gary Jarrel updated CAY-1596:
-----------------------------

    Affects Version/s: 3.0

> 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
>              Labels: limit, offset, pagination, query, select, sql
>
> 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

        

Mime
View raw message