Return-Path: 
 <user-return-7975-apmail-cayenne-user-archive=cayenne.apache.org@cayenne.apache.org>
X-Original-To: apmail-cayenne-user-archive@www.apache.org
Delivered-To: apmail-cayenne-user-archive@www.apache.org
Received: from mail.apache.org (hermes.apache.org [140.211.11.3])
	by minotaur.apache.org (Postfix) with SMTP id 87D8E6373
	for <apmail-cayenne-user-archive@www.apache.org>;
 Sat, 23 Jul 2011 07:56:57 +0000 (UTC)
Received: (qmail 46714 invoked by uid 500); 23 Jul 2011 07:56:56 -0000
Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org
Received: (qmail 46422 invoked by uid 500); 23 Jul 2011 07:56:43 -0000
Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-help@cayenne.apache.org>
List-Unsubscribe: <mailto:user-unsubscribe@cayenne.apache.org>
List-Post: <mailto:user@cayenne.apache.org>
List-Id: <user.cayenne.apache.org>
Reply-To: user@cayenne.apache.org
Delivered-To: mailing list user@cayenne.apache.org
Received: (qmail 46408 invoked by uid 99); 23 Jul 2011 07:56:34 -0000
Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 23 Jul 2011 07:56:34 +0000
X-ASF-Spam-Status: No, hits=-0.7 required=5.0
	tests=FREEMAIL_FROM,RCVD_IN_DNSWL_LOW,SPF_PASS,T_TO_NO_BRKTS_FREEMAIL
X-Spam-Check-By: apache.org
Received-SPF: pass (athena.apache.org: domain of garyjarrel@gmail.com
 designates 209.85.161.41 as permitted sender)
Received: from [209.85.161.41] (HELO mail-fx0-f41.google.com) (209.85.161.41)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 23 Jul 2011 07:56:28 +0000
Received: by fxg9 with SMTP id 9so5888352fxg.14
        for <user@cayenne.apache.org>; Sat, 23 Jul 2011 00:56:07 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=gamma;
        h=mime-version:in-reply-to:references:date:message-id:subject:from:to
         :content-type:content-transfer-encoding;
        bh=BZUENDOxaiCxERKxdCf3vlKiRv7Dgm9L0rMxz8BCLXY=;
        b=SCXdIvFzspYzMlSlI9fRoWPZ1aNahLPbT3IoVGcPA1wl5QSrvC15n101Htgl6g0GFi
         DoGiNejMoDPOPX4w6EkDNB4YNJggBhieewndA+Ux0MWEUvaCGe1PyS5Ka3zx++e3vKNi
         mFPcQEDtWwsr3CPmBELZ/D8bSnKjqkxFNk7LY=
MIME-Version: 1.0
Received: by 10.204.152.18 with SMTP id e18mr761039bkw.58.1311407767205; Sat,
 23 Jul 2011 00:56:07 -0700 (PDT)
Received: by 10.204.39.5 with HTTP; Sat, 23 Jul 2011 00:56:07 -0700 (PDT)
In-Reply-To: <9D47CA9F-5628-48CC-A6E1-DDDE71A13A22@objectstyle.org>
References: 
 <CALRDka8qi9qLHTEkcWKJiFXYkw=MsYp4-+T9K6OkBbDichJ7qQ@mail.gmail.com>
	<9D47CA9F-5628-48CC-A6E1-DDDE71A13A22@objectstyle.org>
Date: Sat, 23 Jul 2011 17:56:07 +1000
Message-ID: 
 <CALRDka-19OpysoLjLmk2foQv7SBomoKZWZZ3+7T-CjhCc_cN2g@mail.gmail.com>
Subject: Re: setFetchOffset & setFetchLimit issue
From: Gary Jarrel <garyjarrel@gmail.com>
To: user@cayenne.apache.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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 <andrus@objectstyle.org> w=
rote:
> 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 Ji=
ra 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 =3D getPrototypeQuery().queryWithParameters(params, tr=
ue);
>> query.setFetchOffset(offset);
>> query.setFetchLimit(limit);
>> List<ConsentForm> result =3D 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 =3D ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> =3D=3D=3D 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 =3D ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> =3D=3D=3D 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 =3D ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> =3D=3D=3D returned 0 rows. - took 20 ms.
>>
>> Basically the first page appears to be working fine, but nothing afterwa=
rds.
>>
>> 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
>>
>
>