EJB-QL produces incorrect SQL

SourceForge Submitter: cstach .
It seems that it's forgetting to put in the relation table...

ejb-jar.xml snippets:

<query>
<description><![CDATA[]]></description>
<query-method>
<method-
name>ejbSelectRemainingThingCount</method-name>
<method-params>
<method-param>java.lang.Long</method-
param>
</method-params>
</query-method>
<ejb-ql><![CDATA[SELECT COUNT(DISTINCT c)
FROM item o, thing c, IN (o.historyList) AS mh WHERE
o.id = ?1 AND c MEMBER OF o.thingList AND c <>
mh.thingInfo.thing]]></ejb-ql>
</query>

[...]

<ejb-relation >
<ejb-relation-name>Thing-Thing Info</ejb-
relation-name>

<ejb-relationship-role >
<ejb-relationship-role-name>thing-info-belongs-
to-thing</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<cascade-delete/>
<relationship-role-source >
<ejb-name>ThingInfo</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>thing</cmr-field-name>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role >
<ejb-relationship-role-name>thing-has-thing-
infos</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source >
<ejb-name>Thing</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>thingInfoList</cmr-field-
name>
<cmr-field-type>java.util.Set</cmr-field-
type>
</cmr-field>
</ejb-relationship-role>

</ejb-relation>

<ejb-relation >
<ejb-relation-name>Thing Info-History</ejb-
relation-name>

<ejb-relationship-role >
<ejb-relationship-role-name>history-belongs-to-
thing-info</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<cascade-delete/>
<relationship-role-source >
<ejb-name>History</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>thingInfo</cmr-field-name>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role >
<ejb-relationship-role-name>thing-info-has-
history</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source >
<ejb-name>ThingInfo</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>historyList</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-
type>
</cmr-field>
</ejb-relationship-role>

</ejb-relation>

<ejb-relation >
<ejb-relation-name>Item-Thing</ejb-relation-
name>

<ejb-relationship-role >
<ejb-relationship-role-name>thing-belongs-to-
items</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source >
<ejb-name>Thing</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>itemList</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-
type>
</cmr-field>
</ejb-relationship-role>

<ejb-relationship-role >
<ejb-relationship-role-name>item-has-
things</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source >
<ejb-name>Item</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>thingList</cmr-field-name>
<cmr-field-type>java.util.Set</cmr-field-
type>
</cmr-field>
</ejb-relationship-role>

</ejb-relation>

jbosscmp-jdbc.xml:

<ejb-relation>
<ejb-relation-name>Thing-Thing Info</ejb-relation-
name>

<ejb-relationship-role>
<ejb-relationship-role-name>thing-info-belongs-
to-thing</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>

<batch-cascade-delete/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>thing-has-thing-
infos</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>thing_id</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>Item-Thing</ejb-relation-name>
<relation-table-mapping>
<table-name>thing_to_item</table-name>
</relation-table-mapping>

<ejb-relationship-role>
<ejb-relationship-role-name>thing-belongs-to-
items</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>thing_id</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>item-has-
things</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>item_id</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>Item-History</ejb-relation-
name>
<relation-table-mapping>
<table-name>mail_history_to_item</table-name>
</relation-table-mapping>

<ejb-relationship-role>
<ejb-relationship-role-name>item-has-
history</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>item_id</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>history-belongs-to-
item</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>mail_history_id</column-
name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>

<ejb-relation>
<ejb-relation-name>Thing Info-History</ejb-relation-
name>

<ejb-relationship-role>
<ejb-relationship-role-name>history-belongs-to-
thing-info</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>

<batch-cascade-delete/>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>thing-info-has-
history</ejb-relationship-role-name>
<key-fields>
<key-field>
<field-name>id</field-name>
<column-name>thing_info_id</column-name>
</key-field>
</key-fields>

</ejb-relationship-role>
</ejb-relation>

Exception and stack trace:

2004-11-24 11:24:23,609 DEBUG
[org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Item#ej
bSelectRemainingThingCount] Executing SQL: SELECT
count(DISTINCT t0_c.id) FROM item t1_o, thing t0_c,
history t5_mh, history_to_item
t8_o_historyList_RELATION_TA, thing_info
t6_mh_thingInfo, thing t4_mh_thingInfo_thing WHERE
((t1_o.id = ? AND
(t3_o_thingList_RELATION_TABLE.item_id IS NOT NULL
AND t0_c.id=t3_o_thingList_RELATION_TABLE.thing_id)
AND ( NOT (t0_c.id=t4_mh_thingInfo_thing.id)) AND
t5_mh.thing_info_id=t6_mh_thingInfo.id AND
t6_mh_thingInfo.thing_id=t4_mh_thingInfo_thing.id))
AND t1_o.id=t8_o_historyList_RELATION_TA.history_id
AND t5_mh.id=t8_o_historyList_RELATION_TA.item_id
2004-11-24 11:24:23,703 ERROR
[org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Item#ej
bSelectRemainingThingCount] Find failed
java.sql.SQLException: Base table or view not found
message from server: "Unknown
table 't3_o_thingList_RELATION_TABLE' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket
(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand
(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect
(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL
(Connection.java:2247)
at
com.mysql.jdbc.PreparedStatement.executeQuery
(PreparedStatement.java:1586)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatem
ent.executeQuery(WrappedPreparedStatement.java:296)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryComma
nd.execute(JDBCAbstractQueryCommand.java:206)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryComma
nd.execute(JDBCAbstractQueryCommand.java:128)
at
org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCSelectorBridge.
execute(JDBCSelectorBridge.java:99)
at
org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCSelectorBridge.i
nvoke(JDBCSelectorBridge.java:70)
at
org.jboss.ejb.plugins.cmp.bridge.EntityBridgeInvocationHa
ndler.invoke(EntityBridgeInvocationHandler.java:91)
at org.jboss.proxy.compiler.Runtime.invoke
(Runtime.java:62)
at
com.xxxxx.xxx.ejb.ItemCMP$Proxy.ejbSelectRemainingThin
gCount(<generated>)
at
com.xxxxx.xxx.ejb.ItemEJB.selectRemainingThingCount
(ItemEJB.java:702)
at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:324)
at org.jboss.invocation.Invocation.performCall
(Invocation.java:345)
at
org.jboss.ejb.EntityContainer$ContainerInterceptor.invoke
(EntityContainer.java:1174)
at
org.jboss.ejb.plugins.cmp.jdbc.JDBCRelationInterceptor.in
voke(JDBCRelationInterceptor.java:72)
at
org.jboss.ejb.plugins.EntitySynchronizationInterceptor.inv
oke(EntitySynchronizationInterceptor.java:273)
at
org.jboss.resource.connectionmanager.CachedConnection
Interceptor.invoke
(CachedConnectionInterceptor.java:185)
at
org.jboss.ejb.plugins.EntityReentranceInterceptor.invoke
(EntityReentranceInterceptor.java:111)
at
org.jboss.ejb.plugins.EntityInstanceInterceptor.invoke
(EntityInstanceInterceptor.java:211)
at
org.jboss.ejb.plugins.EntityLockInterceptor.invoke
(EntityLockInterceptor.java:89)
at
org.jboss.ejb.plugins.EntityCreationInterceptor.invoke
(EntityCreationInterceptor.java:53)
at
org.jboss.ejb.plugins.CallValidationInterceptor.invoke
(CallValidationInterceptor.java:48)
at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
(AbstractTxInterceptor.java:105)
at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransacti
ons(TxInterceptorCMT.java:316)
at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke
(TxInterceptorCMT.java:149)
at
org.jboss.ejb.plugins.SecurityInterceptor.invoke
(SecurityInterceptor.java:128)
at org.jboss.ejb.plugins.LogInterceptor.invoke
(LogInterceptor.java:191)
at
org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invok
e(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.EntityContainer.internalInvoke
(EntityContainer.java:514)
at org.jboss.ejb.Container.invoke
(Container.java:854)
at
org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke
(BaseLocalProxyFactory.java:413)
at org.jboss.ejb.plugins.local.EntityProxy.invoke
(EntityProxy.java:44)
at $Proxy956.selectRemainingThingCount
(Unknown Source)
at
com.xxxxx.xxx.ejb.MDFControllerEJB.makeItemList
(MDFControllerEJB.java:3540)
at
com.xxxxx.xxx.ejb.MDFControllerEJB.getFooItemList
(MDFControllerEJB.java:3027)
at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:324)
at org.jboss.invocation.Invocation.performCall
(Invocation.java:345)
at
org.jboss.ejb.StatelessSessionContainer$ContainerInterce
ptor.invoke(StatelessSessionContainer.java:214)
at
org.jboss.resource.connectionmanager.CachedConnection
Interceptor.invoke
(CachedConnectionInterceptor.java:185)
at
org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor
.invoke(StatelessSessionInstanceInterceptor.java:113)
at
org.jboss.webservice.server.ServiceEndpointInterceptor.i
nvoke(ServiceEndpointInterceptor.java:51)
at
org.jboss.ejb.plugins.CallValidationInterceptor.invoke
(CallValidationInterceptor.java:48)
at
org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext
(AbstractTxInterceptor.java:105)
at
org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransacti
ons(TxInterceptorCMT.java:316)
at
org.jboss.ejb.plugins.TxInterceptorCMT.invoke
(TxInterceptorCMT.java:149)
at
org.jboss.ejb.plugins.SecurityInterceptor.invoke
(SecurityInterceptor.java:128)
at org.jboss.ejb.plugins.LogInterceptor.invoke
(LogInterceptor.java:191)
at
org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invok
e(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.SessionContainer.internalInvoke
(SessionContainer.java:624)
at org.jboss.ejb.Container.invoke
(Container.java:854)
at
sun.reflect.NativeMethodAccessorImpl.invoke0(Native
Method)
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke
(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke
(Method.java:324)
at
org.jboss.mx.interceptor.ReflectedDispatcher.invoke
(ReflectedDispatcher.java:141)
at org.jboss.mx.server.Invocation.dispatch
(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke
(Invocation.java:72)
at
org.jboss.mx.server.AbstractMBeanInvoker.invoke
(AbstractMBeanInvoker.java:242)
at org.jboss.mx.server.MBeanServerImpl.invoke
(MBeanServerImpl.java:642)
at
org.jboss.invocation.local.LocalInvoker$MBeanServerActio
n.invoke(LocalInvoker.java:155)
at
org.jboss.invocation.local.LocalInvoker.invoke
(LocalInvoker.java:104)
at
org.jboss.invocation.MarshallingInvokerInterceptor.invoke
(MarshallingInvokerInterceptor.java:55)
at
org.jboss.proxy.TransactionInterceptor.invoke
(TransactionInterceptor.java:46)
at org.jboss.proxy.SecurityInterceptor.invoke
(SecurityInterceptor.java:55)
at
org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke
(StatelessSessionInterceptor.java:97)
at org.jboss.proxy.ClientContainer.invoke
(ClientContainer.java:86)
at $Proxy961.getFooItemList(Unknown Source)
at
com.xxxxx.xxx.controller.MDFControllerWebImpl.getFooIte
mList(MDFControllerWebImpl.java:1860)
at
com.xxxxx.xxx.controller.actions.ItemList.execute
(ItemList.java:127)
at
org.apache.struts.action.RequestProcessor.processAction
Perform(RequestProcessor.java:421)
at
org.apache.struts.action.RequestProcessor.process
(RequestProcessor.java:226)
at
org.apache.struts.action.ActionServlet.process
(ActionServlet.java:1164)
at org.apache.struts.action.ActionServlet.doGet
(ActionServlet.java:397)
at javax.servlet.http.HttpServlet.service
(HttpServlet.java:697)
at javax.servlet.http.HttpServlet.service
(HttpServlet.java:810)
at
org.apache.catalina.core.ApplicationFilterChain.internalDo
Filter(ApplicationFilterChain.java:237)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:157)
at
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter
(ReplyHeaderFilter.java:75)
at
org.apache.catalina.core.ApplicationFilterChain.internalDo
Filter(ApplicationFilterChain.java:186)
at
org.apache.catalina.core.ApplicationFilterChain.doFilter
(ApplicationFilterChain.java:157)
at
org.apache.catalina.core.StandardWrapperValve.invoke
(StandardWrapperValve.java:214)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
at
org.apache.catalina.core.StandardContextValve.invokeInt
ernal(StandardContextValve.java:198)
at
org.apache.catalina.core.StandardContextValve.invoke
(StandardContextValve.java:152)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:104)
at
org.jboss.web.tomcat.security.CustomPrincipalValve.invok
e(CustomPrincipalValve.java:44)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:102)
at
org.jboss.web.tomcat.security.SecurityAssociationValve.i
nvoke(SecurityAssociationValve.java:169)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:102)
at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
at
org.apache.catalina.core.StandardHostValve.invoke
(StandardHostValve.java:137)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:104)
at
org.apache.catalina.valves.ErrorReportValve.invoke
(ErrorReportValve.java:118)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:102)
at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
at
org.apache.catalina.core.StandardEngineValve.invoke
(StandardEngineValve.java:109)
at
org.apache.catalina.core.StandardValveContext.invokeNe
xt(StandardValveContext.java:104)
at
org.apache.catalina.core.StandardPipeline.invoke
(StandardPipeline.java:520)
at
org.apache.catalina.core.ContainerBase.invoke
(ContainerBase.java:929)
at
org.apache.coyote.tomcat5.CoyoteAdapter.service
(CoyoteAdapter.java:160)
at
org.apache.coyote.http11.Http11Processor.process
(Http11Processor.java:799)
at
org.apache.coyote.http11.Http11Protocol$Http11Connec
tionHandler.processConnection(Http11Protocol.java:705)
at
org.apache.tomcat.util.net.TcpWorkerThread.runIt
(PoolTcpEndpoint.java:577)
at
org.apache.tomcat.util.threads.ThreadPool$ControlRunna
ble.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)