Details
-
Bug
-
Resolution: Won't Do
-
Major
-
JBossAS-4.0.1 SP1
-
None
Description
Hello,
The IS EMPTY does not work. I'm asking give me all object where there are no childs.
<ejb-ql><![CDATA[SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE c.tasks IS EMPTY]]></ejb-ql>
Executing SQL: SELECT DISTINCT t0_c.seq FROM contract t0_c, task t2_t WHERE ((t1_c_tasks.seq IS NULL AND t0_c.seq=t1_c_tasks.contract)) AND t0_c.seq=t2_t.contract
java.sql.SQLException: Base table or view not found message from server: "Unknown table 't1_c_tasks' in where clause"
Also with the NOT operator it is "not" working.
<ejb-ql><![CDATA[SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE c.tasks IS NOT EMPTY]]></ejb-ql>
Executing SQL: SELECT DISTINCT t0_c.seq FROM contract t0_c, task t2_t WHERE ((t1_c_tasks.seq IS NOT NULL AND t0_c.seq=t1_c_tasks.contract)) AND t0_c.seq=t2_t.contract
java.sql.SQLException: Base table or view not found message from server: "Unknown table 't1_c_tasks' in where clause"
==========Descriptors=============
ejb-jar.xml
<ejb-relation>
<ejb-relation-name>Contract-Task</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>contract-has-tasks</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Contract</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>tasks</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>task-belongs-to-contract</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>Task</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>
jbosscmp-jdbc.xml
<ejb-relation>
<ejb-relation-name>Contract-Task</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>contract-has-tasks</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>key</field-name>
<column-name>contract</column-name>
</key-field>
</key-fields>
<read-ahead>
<strategy>on-find</strategy>
<page-size>16</page-size>
<eager-load-group>all</eager-load-group>
</read-ahead>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>task-belongs-to-contract</ejb-relationship-role-name>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>
==========End of Descriptors=============
My original question was:
Give me all objects with no childs OR with childs conform to some criteria.
<ejb-ql><![CDATA[SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE (c.tasks IS EMPTY) OR (c.tasks IS NOT EMPTY AND t.type = 0 AND t.completed IS NULL)]]></ejb-ql>
Executing SQL: SELECT DISTINCT t0_c.seq FROM contract t0_c, task t2_t WHERE (((t1_c_tasks.seq IS NULL) AND t0_c.seq=t1_c_tasks.contract) OR ((t1_c_tasks.seq IS NOT NULL AND t2_t.type = 0 AND t2_t.completed IS NULL) AND t0_c.seq=t1_c_tasks.contract)) AND t0_c.seq=t2_t.contract
java.sql.SQLException: Base table or view not found message from server: "Unknown table 't1_c_tasks' in where clause"
I hope that this can be fixed in the next release because now I have to loop in the EJB through all returned objects and examine the collection.
I would parse <ejb-ql><![CDATA[SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE c.tasks IS EMPTY]]></ejb-ql> to :
SELECT DISTINCT t0_c.seq FROM contract t0_c, task t1_t WHERE (t1_t.contract <> t0_c.seq)
but that's my opinion, maybe there are smarter solutions.
T.I.A.
Johan