cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Limits & Joins on Oracle
Date Wed, 29 Jun 2011 07:32:46 GMT
This is clearly a bug and we'll need to fix it as a part of CAY-1266. I don't have an estimate
on the fix, but I put it on my short list. And Michael mentioned some workarounds.

Andrus

On Jun 22, 2011, at 12:32 AM, Bruno René Santos wrote:
> Hello all,
> 
> Any more ideas on this subject? Andrus or Gentry could you please dissolve
> these doubts about the possibility of using aliases on the column names?
> 
> Thanx a lot
> BRuno
> 
> ---------- Forwarded message ----------
> From: Bruno René Santos <brunorene@holos.pt>
> Date: Tue, Jun 14, 2011 at 6:18 PM
> Subject: Re: Limits & Joins on Oracle
> To: user@cayenne.apache.org
> 
> 
> Hi Chris,
> 
> Your solutions are in consonance with what I said in my mail. But having
> fields with the same name is quite common. For instants all our primary keys
> are named ID and for all our tables we always have columns like
> last_modified_at, created_at, last_modified_by or created_by which we use
> for logging purposes. So it would by critical for us to have this issue
> resolved. I am willing to give it a try if someone could point me on the
> right direction in terms of cayenne inner architecture of objects
> 
> Regards
> Bruno
> 
> Em 14-06-2011 17:55, Chris Poulsen escreveu:
> 
> 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
>> looks
>> 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
>> as
>> 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
>> columns
>> 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.
>> 
>> 
> 
> -- 
> Bruno René Santos | brunorene@holos.pt <mailto:brunorene@holos.pt> | Gestor
> de Projectos | Analista | Programador | Investigador
> 
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> 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 intended
> 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é Santos by telephone on
> +351 210 438 686
> 
> 
> 
> 
> -- 
> Bruno René Santos | brunorene@holos.pt | Gestor de Projectos | Analista |
> Programador | Investigador
> 
> Holos - Soluções Avançadas em Tecnologias de Informação S.A.
> Parque de Ciência e Tecnologia de Almada/Setúbal . Edifício Madan Parque
> 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 intended
> 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é Santos by telephone on
> +351 210 438 686


Mime
View raw message