Hello, I'm using JBoss 4.01 and MySQL 4.1.9. I get this message from MySQL : "Not unique table/alias: 't3_c_contractor'" This is happening when you ask : give me all EJB's where "cmr field" = "parent EJB" and you have also the parent EJB in the read-ahead element with a left outer join.
jbosscmp-jdbc | <query> | <query-method> | <method-name>findByContractor</method-name> | <method-params> | <method-param>nl.vda.contractsys.j2ee.ejb.contractor.ContractorLocal</method-param> | </method-params> | </query-method> | <read-ahead> | <strategy>on-find</strategy> | <eager-load-group>lite</eager-load-group> | <left-join cmr-field="type" eager-load-group="all"></left-join> | <left-join cmr-field="subject" eager-load-group="all"></left-join> | <left-join cmr-field="contractor" eager-load-group="all"></left-join> | </read-ahead> | </query> | | [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Contractor#findByPrimaryKey] Executing SQL: SELECT t0_Contractor.seq FROM contractor t0_Contractor WHERE t0_Contractor.seq=? | [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.Contractor#findByPrimaryKey] param: i=1, type=INTEGER, value=1 | [org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMP2xFieldBridge.Contractor#key] result: i=1, type=java.lang.Integer, value=1 | [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Contract#findByContractor] Executing SQL: | SELECT t0_c.seq, t0_c.version,..., t3_c_contractor.remarks, t0_c.organization_number | | FROM contract t0_c, contractor t3_c_contractor | LEFT OUTER JOIN type t1_c_type ON t0_c.type=t1_c_type.seq | LEFT OUTER JOIN subject t2_c_subject ON t0_c.subject=t2_c_subject.seq | LEFT OUTER JOIN contractor t3_c_contractor ON t0_c.contractor=t3_c_contractor.seq | WHERE ((t3_c_contractor.seq=?) AND t0_c.contractor=t3_c_contractor.seq) | ORDER BY t0_c.organization_number ASC | | [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Contract#findByContractor] param: i=1, type=INTEGER, value=1 | [org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLQuery.Contract#findByContractor] Find failed | java.sql.SQLException: Syntax error or access violation message from server: "Not unique table/alias: 't3_c_contractor'" | As you can see in the query there are 2 aliases "t3_c_contractor". One for the left outer join and one for the "other" join asking those records having a contractor parent with key "1". The query would fine if it was like this | LEFT OUTER JOIN type t1_c_type ON t0_c.type=t1_c_type.seq | LEFT OUTER JOIN subject t2_c_subject ON t0_c.subject=t2_c_subject.seq | LEFT OUTER JOIN contractor t3_c_contractor ON t0_c.contractor=t3_c_contractor.seq | WHERE t3_c_contractor.seq=? | It looks like there is a one join too much in this case if a table is already in the read-ahead strategy with left outer joins. The same thing also happens with dynamic QL where I first discovered this. | <query> | <query-method> | <method-name>ejbSelectGeneric</method-name> | <method-params> | <method-param>java.lang.String</method-param> | <method-param>java.lang.Object[]</method-param> | </method-params> | </query-method> | <dynamic-ql/> | <read-ahead> | <strategy>on-find</strategy> | <eager-load-group>lite</eager-load-group> | <left-join cmr-field="type" eager-load-group="all"></left-join> | <left-join cmr-field="subject" eager-load-group="all"></left-join> | <left-join cmr-field="contractor" eager-load-group="all"></left-join> | </read-ahead> | </query> | If the query would be something like this it is OKE | SELECT t0_c.seq, t0_c.version,..., t3_c_contractor.remarks, t0_c.organization_number | FROM contract t0_c | LEFT OUTER JOIN type t1_c_type ON t0_c.type=t1_c_type.seq | LEFT OUTER JOIN subject t2_c_subject ON t0_c.subject=t2_c_subject.seq | LEFT OUTER JOIN contractor t3_c_contractor ON t0_c.contractor=t3_c_contractor.seq | WHERE t3_c_contractor.seq=? | ORDER BY t0_c.organization_number ASC | I think this is a bug in the CMP engine. Johan View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=3867562#3867562 Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=3867562 ------------------------------------------------------- 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-user mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/jboss-user
