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