Cayenne
  1. Cayenne
  2. CAY-1596

setFetchOffset & setFetchLimit issue under SQL Server 2008 R2 64Bit

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 3.0, 3.0.2
    • Fix Version/s: 3.0.3, 3.1M3
    • Component/s: Core Library
    • 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)

      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.

        Activity

          People

          • Assignee:
            Dzmitry Kazimirchyk
            Reporter:
            Gary Jarrel
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development