Description Hans Harz 2010-11-11 04:07:32 EST

Build Identifier: 2.1.1.v20100817-r8050

If you have a table with a self-referencing foreign key, EclipseLink will generate a wrong SELECT statement on a update all query for a MySQL backend. The created statement contains an additional unconstrained table join, which leads to many results instead of just one. Because of that you will get a 
BatchUpdateException: Duplicate entry '102' for key 'PRIMARY'
Error Code: 1062
when EL tries to create the temporary table.

Reproducible: Always

Steps to Reproduce:
1. Create an entity with a self-referencing foreign key.
   In this example a class TermEjb with the columns ID int, isConcept char, masterTerm TermEjb. 

2. Create a complex update query
final Query query = createQuery("update TermEjb t "
                + "set t.masterTerm = t, t.isConcept = :isConcept "
                + "where t.masterTerm <> t and t.masterTerm.id in   :masterTermIds", false);
        query.setParameter("isConcept", TermEjb.TRUE);
        query.setParameter("masterTermIds", masterTermIds);

3. Execute query on MySQL. EclipseLink will try to create a temporary table with this SQL:
SELECT t1.ID, ?, t0.ID, (t1.VERSION + ?) FROM TERMEJB t0, TERMEJB t2, TERMEJB t1 
WHERE ((NOT ((t1.MASTERTERM_ID = t1.ID))
AND (t2.ID IN (?)))
AND (t2.ID = t1.MASTERTERM_ID)

Please note: The unconstrained JOIN of table t0 makes this query incorrect. If you add a AND t0.ID = t1.ID the query is still quite complex, but at least correct.

4. Because the query shown in step 3 returns more than one result you will get a 
Internal Exception: java.sql.BatchUpdateException: Duplicate entry '102' for key 'PRIMARY'
Error Code: 1062
Query: UpdateAllQuery(referenceClass=TermEjb sql="CREATE TEMPORARY TABLE IF NOT EXISTS TL_TERMEJB (ID INTEGER NOT NULL, VARIANTSCONFIGURATIONS VARCHAR(255), EXPLICITVERSION INTEGER, CONCEPTIDCOLLATIONKEY VARCHAR(255), FREQUENCY BIGINT, EXTERNAL_ID VARCHAR(255), VERSION INTEGER, CREATOR VARCHAR(255), CREATIONDATE DATETIME, CONCEPTUUID VARCHAR(255), CONCEPTID VARCHAR(255), LASTMODIFIER VARCHAR(255), EXTERNALIDCOLLATIONKEY VARCHAR(255), UUID VARCHAR(255), LASTMODIFICATIONDATE DATETIME, ISCONCEPT CHAR(1), MOSSURFACE_ID INTEGER, MASTERTERM_ID INTEGER, topLevelLanguage_id INTEGER, PRIMARY KEY (ID))")



On other database backends which support updates across multiple tables the query looks like this and works fine:
UPDATE TERMEJB SET VERSION = (VERSION + 1), ISCONCEPT = t, MASTERTERM_ID = ID WHERE EXISTS(SELECT t0.ID FROM TERMEJB t0, TERMEJB t1 WHERE ((NOT ((t0.MASTERTERM_ID = t0.ID)) AND (t1.ID IN (1))) AND (t1.ID = t0.MASTERTERM_ID)) AND t0.ID = TERMEJB.ID)