EJB2.1 finder for CMP entities with relations (CMR) returns duplicates in EAP 6

Environment

• JBoss Enterprise Application Platform (EAP)
  • 6.x

Issue

• A finder with managed relations (CMR) return duplicate entries after migration from EAP 4/EAP 5 to EAP 6.
• Why is the generated SQL different in EAP 5 and EAP 6?

Resolution

• The finder should include the keyword 'distinct' to avoid such carthesian product as EAP 6 supports only the standard container for CMP/CMR.

• For additional information regarding EJB 2/CMP in EAP 6 and further versions, refer to KCS Is it possible to configure different container for EJB2 Entities in EAP6.

Root Cause

With EAP 4 or EAP 5 it was possible to select different CMP containers which uses different persistence strategies. In this case the cmp2.x_jdbc2 pm container contains several optimizations and use SQL92. With EAP 6 it is not possible to select several containers and the available implementation does not contain such optimization. An example:

• Company has employees and the finder try to find all people for one company and freelancers:

    Collection findEmployeesByCompanyAndFreelancers(java.lang.Integer companyId)
    ejb-ql = "SELECT OBJECT(e) FROM EmployeeBean AS e WHERE e.company.id = ?1 OR e.isFreelancer = 'Y'"
  

• The number of results shown by this ejb-query is higher than a simple select on the database:

    select count(*) from EMPLOYEE where company_Fk = '1' or is_freelancer = 'Y'
  

• The query with optimized cmp2.x_jdbc2_pm container in EAP 5:

    SELECT t0_e.NAME, t0_e.EMPLOYEE_ID, t0_e.IS_ACTIVE, ... FROM EMPLOYEE t0_e LEFT OUTER JOIN COMPANY t1_e_company ON t0_e.COMPANY_FK=t1_e_company.ID WHERE t1_e_company.ID = ? OR t0_e.IS_FREELANCER = 'Y'
  

• Here the resulting SQL is optimized, use a LEFT OUTER JOIN and fetch the results. The query with standard container in EAP 6:

    SELECT t0_e.EMPLOYEE_ID FROM EMPLOYEE t0_e, COMPANY t1_e_company WHERE (t1_e_company.ID = ? AND t0_e.COMPANY_FK=t1_e_company.ID) OR (t0_e.IS_FREELANCER = 'Y')
  

• The standard container use a simple SQL and fetch only the primary key. All the data is fetched on demand if the relation is used.

Diagnostic Steps

1. Check whether the cmp2.x_jdbc2 pm container was configured in former releases.
2. Set org.jboss.as.cmp (EAP 6) or org.jboss.ejb.plugins.cmp (EAP 4/ 5) to debug/trace and check the SQL whether EAP 4/5 use LEFT OUTER JOIN and EAP 6 do not.

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.