You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by ulfherge <ul...@capgemini.com> on 2009/07/17 08:08:14 UTC
Problems using select in select to exclude results
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.
Re: Problems using select in select to exclude results
Posted by ulfherge <ul...@capgemini.com>.
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 <ul...@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-tp3270337p3436676.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Problems using select in select to exclude results
Posted by ulfherge <ul...@capgemini.com>.
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 <ul...@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.
Re: Problems using select in select to exclude results
Posted by Michael Dick <mi...@gmail.com>.
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 <ul...@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.
>