[
http://jira.jboss.com/jira/browse/JBAS-1596?page=comments#action_12316250 ]
Johan Borchers commented on JBAS-1596:
--------------------------------------
I did try to use the SQL92 compiler for testing the EJB-QL:
<ejb-ql>SELECT OBJECT(c) FROM Contract AS c, IN(c.tasks) AS t WHERE c.tasks IS
EMPTY</ejb-ql>
First I tried the overall solution changing standardjbosscmp-jdbc.xml
The result is:
Executing SQL: SELECT t0_c.seq, ...a lot more fields...
,t0_c.period_of_notice_period FROM contract t0_c INNER JOIN task t2_t ON
t0_c.seq=t2_t.contract WHERE t1_c_tasks.seq IS NULL
java.sql.SQLException: Base table or view not found message from server:
"Unknown table 't1_c_tasks' in where clause"
Changing to SQL92 gives also a problem with the alias.
But when I did not change standardjbosscmp-jdbc.xml but only on a per <query>
element in jbosscmp-jdbc.xml the query did not change:
Executing SQL: SELECT DISTINCT t0_c.seq, t0_c.version, t0_c.active,
t0_c.organization_number, t0_c.description, t0_c.characteristics,
t0_c.start_date, t0_c.duration, t0_c.ending_date, t0_c.period_of_notice,
t0_c.period_of_notice_ending_date, t0_c.discontinued 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"
It seems that on a per <query> element change to SQL92 does not work.
<query>
<query-method>
<method-name>testQuery</method-name>
<method-params/>
</query-method>
<read-ahead>
<strategy>on-find</strategy>
<eager-load-group>lite</eager-load-group>
</read-ahead>
<ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>
</query>
Johan
> 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
> Assignee: Alexey Loubyansky
>
>
> 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
[email protected]
https://lists.sourceforge.net/lists/listinfo/jboss-development
