[ http://jira.jboss.com/jira/browse/JBAS-1596?page=comments#action_12316248 ] Alexey Loubyansky commented on JBAS-1596: -----------------------------------------
This query SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE c.tasks IS EMPTY doesn't make sense. IN(c.tasks) means return instances of c only if they have tasks. About table aliases, it's a bug. Try using sql92 compiler. > collection_valued_path_expression IS [NOT] EMPTY does not work > -------------------------------------------------------------- > > Key: JBAS-1596 > URL: http://jira.jboss.com/jira/browse/JBAS-1596 > Project: JBoss Application Server > Type: Bug > Components: CMP service > Versions: JBossAS-4.0.1 SP1 > Environment: Windows XP Prof. SP2, 3.06 GHz, 1 GB RAM, MySQL 4.1.9, JBoss > 4.0.1sp1 > Reporter: Johan Borchers > > > 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 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click _______________________________________________ JBoss-Development mailing list JBoss-Development@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jboss-development
