cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Poulsen <>
Subject Re: Limits & Joins on Oracle
Date Tue, 14 Jun 2011 17:38:35 GMT

I know repeated column names are common ;) - I was just spelling out how
Oracle works with respect to them. - Hoping to shed some light on why it
isn't working currently.

Someone familiar with cayenne internals should chime in on whether / how it
is possible to alias the columns when running against an oracle db.


On Tue, Jun 14, 2011 at 7:18 PM, Bruno René Santos <>wrote:

> 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 | <> |
> 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:
> 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

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message