openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ulfherge <ulf.he...@capgemini.com>
Subject Re: Problems using select in select to exclude results
Date Thu, 13 Aug 2009 08:15:46 GMT

Hi!

Sorry for not replying until now, have been on 4 weeks of vacation. 

I'm glad to see that it was actually a bug because I was starting to think
that I was doing something terribly wrong :) If i remember everything
correct I also tried the latest version back then (1.2.1) and the outcome
was the same but since im not 100% sure I will try it again asap and report
back.

Thanks for helping!

/ Ulf


Michael Dick wrote:
> 
> Hi Ulf,
> 
> This is a bug. Some of these bugs have been addressed in
> OPENJPA-681<http://issues.apache.org/jira/browse/OPENJPA-681>(fixed in
> 1.2.1) but there are some that still exist.
> 
> Catalina & Fay (on the dev team) have done a lot of work to improve how
> OpenJPA handles subqueries. I'm taking a look at their approach now and
> seeing whether it can be merged into earlier releases (like 1.2.2).
> 
> Could you try running with 1.2.1 and see that helps? If you're hitting a
> scenario that isn't covered by OPENJPA-681 that would be a good data point
> for determining whether we need to merge Catalina & Fay's changes.
> 
> Hope this helps,
> -mike
> 
> On Fri, Jul 17, 2009 at 1:08 AM, ulfherge <ulf.herge@capgemini.com> wrote:
> 
>>
>> Hi everyone I have this quite simple thing that I want to do but it
>> doesnt
>> seem to work the way I want, actually it looks like a bug to me.
>>
>> Here is my original query, allt it does is simply to select all ArendeEO
>> and
>> exlude some of them which does not satisfy the inner select clause
>>
>> Query q = em.createQuery("SELECT arende from ArendeEO arende JOIN
>> arende.diarieNr diarieNr WHERE arende.arID NOT IN(SELECT arende.arID from
>> ArendeEO arende WHERE arende.status.id = 3 AND arende.arendeTyp.id = 10)
>> AND
>> diarieNr.diarieNr LIKE '%09' ");
>>
>> This does NOT work, it just throws me an exception like this (shortened
>> down
>> a bit but the essentials are there)
>>
>> openjpa-1.2.0-r422266:683325 nonfatal general error>
>> org.apache.openjpa.persistence.PersistenceException: ORA-00904:
>> "T1"."ID":
>> invalid identifier
>>  {prepstmnt 32778033 SELECT t0.arID, t0.arbetsstegDatum, t4.id,
>> t4.arbetsuppgift, t4.namn, t4.procStodVSys FROM T_SU_ARENDE t0,
>> T_SU_ARENDE_DIARIENR t3, T_SU_ARENDE_ARBETSSTEG t4, T_SU_ARENDETYP t5,
>> T_SU_ARENDE_DIARIENR t6, T_SU_GALDENAR t7, T_SU_GALDENAR_ANSTFORH t8,
>> T_SU_GALDENAR_BOSTADSFORH t9, T_SU_BUDGSKULDRADG t10, T_SU_OMBUD t11,
>> T_SU_GALDENAR_SKALIGHET t12, T_SU_GALDENAR_UTBILDNING t13,
>> T_SU_HANDLINGSDATA t14, T_SU_OMPROV_BORGENARDATA t15,
>> T_SU_ARENDESTATISTIK
>> t16, T_SU_ARENDE_STATUS t17 WHERE (NOT (t0.arID IN (SELECT t2.arID FROM
>> T_SU_ARENDE_DIARIENR t1, T_SU_ARENDE t2 WHERE (t2.statusId = ? AND
>> t2.arendeTypId = ?))) AND t3.diarieNr LIKE ? ESCAPE '\') AND t0.DIARIEID
>> =
>> t1.id AND t0.arbetsstegId = t4.id(+) AND t0.arendeTypId = t5.id(+) AND
>> t0.DIARIEID = t6.id(+) AND t0.gldID = t7.gldID(+) AND t0.handlingsDataId
>> =
>> t14.id(+) AND t0.arID = t15.ARID(+) AND t0.arID = t16.ARID(+) AND
>> t0.statusId = t17.id(+) AND t7.gldAnfoID = t8.gldAnfoID(+) AND
>> t7.gldBofoID
>> = t9.gldBofoID(+) AND t7.buskid = t10.buskID(+) AND t7.ombid =
>> t11.ombID(+)
>> AND t7.gldID = t12.GLDID(+) AND t7.gldutbldID = t13.gldUtbildningID(+)
>> [params=(long) 3, (long) 10, (String) %09]} [code=904, state=42000]
>>        at
>> org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4238)
>>        at
>>
>> org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4203)
>>        at
>> org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:102)
>> ....
>>
>> When looking at the code I understand it doesnt work properly because
>> becase
>> T1 is never declared so how can it be used? A little odd and it looks
>> like
>> openJPA builds a non functional SELECT clause.
>>
>>
>> When trying with a native query like this it works just fine:
>>
>> Query q2 = em.createNativeQuery("SELECT * FROM T_SU_ARENDE where arID NOT
>> IN(SELECT arID FROM T_SU_ARENDE WHERE arendetypid = 10 AND statusid =
>> 3)");
>>
>>
>> This is fine and i could use the native query solution but the problem
>> here
>> is that we are using a class to dynamically build our search queries and
>> the
>> native queries would require us to rewrite a lot of code and it would be
>> ALOT  more complex.
>>
>> Is it a bug or am I just doing it the wrong way? I tried a couple of
>> different ways but they all end up with the same exception.
>>
>> / Ulf
>> --
>> View this message in context:
>> http://n2.nabble.com/Problems-using-select-in-select-to-exclude-results-tp3270337p3270337.html
>> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>>
> 
> 
-- 
View this message in context: http://n2.nabble.com/Problems-using-select-in-select-to-exclude-results-tp3270337p3436305.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Mime
View raw message