I tried the new 1.2.1 binaries and it actually worked fine which comes as a surprise to me since im quite sure about testing it for a couple of weeks ago :) Anyway I will try this version for a while and if I find any more related problems I will post it here!
Thanks! / Ulf ulfherge wrote: > > 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 <[email protected]> >> 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-tp3270337p3436676.html Sent from the OpenJPA Users mailing list archive at Nabble.com.
