0 Replies Latest reply on Feb 23, 2005 4:02 AM by Johan Borchers

    Not unique table/alias with cmr field and read-ahead

    Johan Borchers Newbie

      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