Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: neutral (athena.apache.org: 209.85.214.171 is neither permitted
 nor denied by domain of mailinglist@nesluop.dk)
MIME-Version: 1.0
In-Reply-To: <4DF777E8.5090004@holos.pt>
References: <4DE77FDF.2060009@holos.pt>
	<C5E0CCC4-8969-4A22-A135-E6891354AC93@objectstyle.org>
	<5A3831C2-65A5-4ED3-9CC1-D0ED1D54D453@objectstyle.org>
	<4DE78A5B.1040604@holos.pt>
	<FF2A3221-1631-4428-BD34-12FEBE9E0848@objectstyle.org>
	<4DE79064.7080703@holos.pt>
	<4DF777E8.5090004@holos.pt>
Date: Tue, 14 Jun 2011 18:55:25 +0200
Message-ID: <BANLkTi=5K4XWyyGEa43W+c0VV5QizTOz7A@mail.gmail.com>
Subject: Re: Limits & Joins on Oracle
From: Chris Poulsen <mailinglist@nesluop.dk>
To: user@cayenne.apache.org
Content-Type: multipart/alternative; boundary=000e0cd3023c09404204a5aee763

--000e0cd3023c09404204a5aee763
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

We have been using the same pagination pattern on Oracle as Cayenne uses
without problems for quite some time (years). So I can confirm that it look=
s
sane.

Anyway looking at the query in CAY-1266, one thing sticks out - The inner
query returns both t0.docid and t1.docid, IIRC oracle will "translate" the
latter into "docid_1" if the inner sql is executed without the wrapper
clauses.

It just doesn't make sense to return "docid" twice from the select clause a=
s
you can't really distinguish them in the surrounding sql anyway (but the
inner query is able to execute as valid sql due to the naming trick and as
it is the join column it doesn't matter if we're getting the t0 or t1
version here).

The solutions I can come up with at the moment would be to alias the column=
s
in a way that ensures unique column names: select ...., t0.docid t0_docid,
..., t1.docid t1_docid FROM ... (That would make Oracle happy)

- And then let the mapper figure out what goes where (if possible?) i.e.
mapping t1_docid to the T1Object.docid and t0_docid to the T0Object.docid  =
-
Exactly what Brunos colleague suggests.

Alternatively a quick partial solution is to leave out the t1.<column_name>=
,
if: 1) it is a join column and 2) it has same name as t0.<column_name> -
duplicated column names that are NOT joined on, will still give errors with
this, but the mapping code would not need to adapt to the aliased column
names.

Aliasing the columns (first suggestion) will be the most robust/correct
solution as queries returning tables with equivalently named non-join
columns would return incorrect results. Although the quick hack could be
used to figure out if the above analysis is correct ;)

I hope this helps you guys figure out a solution to Oracle/fetch limit
thing.

--=20
Regards Chris


On Tue, Jun 14, 2011 at 5:02 PM, Bruno Ren=E9 Santos <brunorene@holos.pt>wr=
ote:

> I've just found the issue https://issues.apache.org/**jira/browse/CAY-126=
6<https://issues.apache.org/jira/browse/CAY-1266>that reveals the same situ=
ation I have. Any news about fixing this? A
> coleague of mine told me it was a matter of assigning an different alias =
for
> each column (table alias + name maybe?) and that way oracle would be able=
 to
> distinguish all columns. Is that feasible? How could I do this change if
> anybody else do not have the time?
>
> Thanx a lot
> Bruno
>
>
> Em 02-06-2011 14:30, Bruno Ren=E9 Santos escreveu:
>
>  Yes a regular SelectQuery, with some Expressions possibly
>>
>> Bruno
>>
>> Em 02-06-2011 14:23, Andrus Adamchik escreveu:
>>
>>> On Jun 2, 2011, at 4:04 PM, Bruno Ren=E9 Santos wrote:
>>>
>>>        query.setFetchLimit(count);
>>>>
>>> Just to clarify - which type of Query? SelectQuery?
>>>
>>> Andrus
>>>
>>>
>>
>>
>
> --
> Bruno Ren=E9 Santos | brunorene@holos.pt <mailto:brunorene@holos.pt> |
> Gestor de Projectos | Analista | Programador | Investigador
>
> Holos - Solu=E7=F5es Avan=E7adas em Tecnologias de Informa=E7=E3o S.A.
> Parque de Ci=EAncia e Tecnologia de Almada/Set=FAbal . Edif=EDcio Madan P=
arque
> Rua dos Inventores . Quinta da Torre . 2825 - 182 Caparica . Portugal
> Phone: +351 210 438 686 . Fax: +351 210 438 687 . Web: www.holos.pt
>
>
> This email and any files transmitted with it are confidential and intende=
d
> solely for the use of the individual or entity to whom they are addressed=
.
> If you are not the intended recipient or the person responsible for
> delivering the email to the intended recipient, be advised that you have
> received this email in error and that any use, dissemination, forwarding,
> printing, or copying of this email is strictly prohibited. If you have
> received this email in error please notify Bruno Ren=E9 Santos by telepho=
ne on
> +351 210 438 686
>
>

--000e0cd3023c09404204a5aee763--