"is empty" EJB-QL clause causes incorrect SQL generation - C

[soeiro]

Hello JBOSS Gurus

It seems that JBOSS 3.0.4 is not generating correct SQL from EJB-QL when the "is empty" clause is used. Our application is already very large and all other finder methods (they don't have an "is empty" clause) seem to be working fine. You can observe this behavior with the following setup:

JBOSS: 3.0.4 standard (without TOMCAT)
RDBMS: Oracle 8i

Our entity beans (all PKs and FKs are integer types):

Person: id_person (PK) - it has the following CMR methods: Collection getJobs(); Collection getProcesses();

Job: id_job (PK), id_person (FK) - it has the following CMR method: PersonLocal getPerson();

Process: id_process(PK), id_person (FK) - it has the following CMR method: PersonLocal getPerson();

EJBQL: SELECT DISTINCT OBJECT(p) FROM Process p WHERE (p.person.jobs is empty)

SQL: SELECT DISTINCT t0_p.ID_PROCESS FROM PROCESS t0_p WHERE (NOT EXISTS (SELECT t2_p_.ID_JOB FROM JOB t2_p_ WHERE t1_p_.ID_PERSON=t2_p_.ID_PERSON))

If you take a closer look into the generated SQL above you will notice that the temporary alias "t1_p_" is not defined anywhere. This is causing the following error: "Find failed: java.sql.SQLException: ORA-00904: invalid column name."

The same behavior is also found in JBOSS 3.0.3.

Does anybody know if it is really a bug? Is there any workaround?

Thanks in advance for any hints,

Luis JB Soeiro

[dfdiego]

Does anyone know the solution for this problem ? I've the same now with JBoss 3.2.3, but with 3.2.1 version there were no problem.

Thanks

[dfdiego]

Ok I've "resolved" the problem, turning back to 3.2.1 version. Anyway the problem was this:

an ejbql query like this:

SELECT DISTINCT OBJECT(o)
FROM PortatoreConoscenza AS o
WHERE o.portConOrg IS EMPTY

generates a sql query like this:

SELECT DISTINCT t0_o.ID_PORTCON
FROM portatori_conoscenza t0_o
WHERE ((t1_o_portConOrg.ID_PORTCON IS NULL AND t1_o_portConOrg.ID_ORG IS NULL))

which is obviously incorrect (t1_o_portConOrg is not in the FROM clause).

Can I suppose this is a bug in 3.2.3 version of JBoss ? I've not tested 3.2.2 version.