Bugs item #1072634, was opened at 2004-11-24 09:44 Message generated for change (Comment added) made by starksm You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1072634&group_id=22866
Category: JBossCMP Group: v4.0 >Status: Closed Resolution: None Priority: 5 Submitted By: Christopher G. Stach II (cstach) Assigned to: Nobody/Anonymous (nobody) Summary: EJB-QL produces incorrect SQL Initial Comment: 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) ---------------------------------------------------------------------- Comment By: Scott M Stark (starksm) Date: 2004-12-29 12:51 Message: Logged In: YES user_id=175228 All issues have been moved to http://jira.jboss.com. Existing issues have been moved. New issues will be closed with this canned reponse. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1072634&group_id=22866 ------------------------------------------------------- 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://productguide.itmanagersjournal.com/ _______________________________________________ JBoss-Development mailing list JBoss-Development@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jboss-development