Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of garyjarrel@gmail.com
 designates 209.85.215.171 as permitted sender)
MIME-Version: 1.0
In-Reply-To: 
 <CALRDka80nYvLD=e=Ke160+enqhbk3JBc7k4Fs33d5bc6T9Wt1Q@mail.gmail.com>
References: 
 <CALRDka8qi9qLHTEkcWKJiFXYkw=MsYp4-+T9K6OkBbDichJ7qQ@mail.gmail.com>
	<9D47CA9F-5628-48CC-A6E1-DDDE71A13A22@objectstyle.org>
	<CALRDka-19OpysoLjLmk2foQv7SBomoKZWZZ3+7T-CjhCc_cN2g@mail.gmail.com>
	<DCF67694-503C-41A4-AC69-D39643C4EFBF@objectstyle.org>
	<CALRDka80nYvLD=e=Ke160+enqhbk3JBc7k4Fs33d5bc6T9Wt1Q@mail.gmail.com>
Date: Tue, 26 Jul 2011 21:25:06 +1000
Message-ID: 
 <CALRDka8DO=iBVj6GTEaCcksi38kpM-jG-ZeMs=1tK6ziDKnFvg@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

Should I still lodge a Jira?

Gary

On Mon, Jul 25, 2011 at 8:02 PM, Gary Jarrel <garyjarrel@gmail.com> wrote:
> I am not sure if this would fix it. I've just checked out the 3.0
> source code, applied the patch, built Cayenne and and ran my code
> against it, and am having the same problem.
>
> Could it perhaps be because SQL Server 2008 does not have the offset
> feature? Well at least not until SQL Server 2011 (based on
> http://stackoverflow.com/questions/187998/row-offset-in-sql-server) or
> should Cayenne be falling back to in memory limits and offsets?
>
> Gary
>
> On Sun, Jul 24, 2011 at 8:26 PM, Andrus Adamchik <andrus@objectstyle.org>=
 wrote:
>> I just found the Jira that I had in mind when answering your email:
>>
>> =A0https://issues.apache.org/jira/browse/CAY-1539
>>
>> I just committed the patch to trunk. Still suspect your case may be rela=
ted somehow.
>>
>> Andrus
>>
>> On Jul 23, 2011, at 10:56 AM, Gary Jarrel wrote:
>>
>>> 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.or=
g> wrote:
>>>> 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 thi=
s, as there is some in-memory processing involved). Could you please open a=
 Jira 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,=
 true);
>>>>> 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 cod=
e
>>>>> 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 afte=
rwards.
>>>>>
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>
>>
>