You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@cayenne.apache.org by Bruno René Santos <br...@holos.pt> on 2011/06/02 14:19:43 UTC

Fwd: Limits & Joins on Oracle

Here it goes again... Please can anybody help?

Thanx
Bruno

Em 31-05-2011 15:43, Bruno René Santos escreveu:
>  Hello all,
>
>
>  I am having a problem with the setFetchLimit on Oracle. When I use
>  joined tables that have fields with the same name i get the error:
>
>  Error code 918, SQL state 42000: ORA-00918: column ambiguously defined
>
>  with a query similar to the next one:
>
>  select * from ( select tid.*, ROWNUM rnum from (
>  SELECT .... LEFT JOIN
>  ENTIDADE t1 ON (t0.ENTIDADE_ID_ENTIDADE = t1.ID_ENTIDADE) WHERE
>  t0.FISC_ID_FISCALIZACAO = 6103 ORDER BY t0.DT_AVERIG)
>   tid where ROWNUM<=15) where rnum>  0
>
>  Any way around this?
>
>  Thanx
>  Bruno
>
>


-- 
Bruno René Santos | brunorene@holos.pt<ma...@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

Re: Limits & Joins on Oracle

Posted by Chris Poulsen <ma...@nesluop.dk>.

Hi,

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.

-- 
Chris


On Tue, Jun 14, 2011 at 7:18 PM, Bruno René Santos <br...@holos.pt>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 | brunorene@holos.pt <ma...@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
>
>

Re: Limits & Joins on Oracle

Posted by Andrus Adamchik <an...@objectstyle.org>.

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 <br...@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 <ma...@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