[OPENJPA-1536] SQL with outer join cannot handle null columns when inheritance is involved

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.0.0-beta, 2.0.0-beta2
• Fix Version/s: 2.0.0-beta3
• Component/s: sql
• Patch Info: Patch Available
• Assignee: Fay Wang
• Reporter: Fay Wang
• Created: 24/Feb/10 22:55
• Updated: 23/Mar/10 18:33
• Resolved: 25/Feb/10 05:08

Description

Have the following entities:

@Entity
@Table(name="CEmployee")
@Inheritance(strategy=InheritanceType.JOINED)
@DiscriminatorColumn(name="EMP_TYPEL", discriminatorType=DiscriminatorType.INTEGER)
public class Employee

{ @Id private int id; private String lastName; private String firstName; private int vacationDays; @ManyToOne private Manager manager; @ManyToOne private Department department; @OneToOne(cascade=CascadeType.ALL) private CommentBlock commentBlock; @Version private long version; ... }

@Entity
@Table(name="CDepartment")
public class Department {
@Id
private int id;

private String departmentName;

@ManyToOne
private Manager departmentManager;

@OneToMany(mappedBy="department")
private List<Employee> employeeList;
...
}

@Entity
@DiscriminatorValue("2")
public class FTEmployee extends Employee {
private double salary;
...

@Entity
@DiscriminatorValue("3")
public class Manager extends FTEmployee {
@OneToMany(mappedBy="manager")
private List<Employee> managesList;
...
}

In the test, three departments, and 11 employees are created - 3 employees are managers, each which own a department. Two of the managers have Manager(id=1) as their manager.
Manager(id=1) is the CEO so it has no manager (this value is set null.)

The problem is that a simple find for Employee(id=1) (which should return Manager(id=1)) returns null, the criteria of the SELECT fails to locate the correct row. The SQL generated by the find is as follows:

SELECT t2.EMP_TYPEL, t2.version, t3.id, t3.version, t3.lastUpdate, t4.id, t7.id,
t7.EMP_TYPEL, t7.version, t7.COMMENTBLOCK_ID, t7.DEPARTMENT_ID, t7.firstName,
t7.lastName, t7.vacationDays, t6.salary, t4.departmentName, t2.firstName,
t2.lastName, t10.id, t10.EMP_TYPEL, t10.version, t10.COMMENTBLOCK_ID,
t10.DEPARTMENT_ID, t10.firstName, t10.lastName, t10.vacationDays, t9.salary,
t2.vacationDays, t1.salary
FROM Manager t0
INNER JOIN FTEmployee t1 ON t0.id = t1.id
INNER JOIN CEmployee t2 ON t1.id = t2.id
LEFT OUTER JOIN CCommentBlock t3 ON t2.COMMENTBLOCK_ID = t3.id
LEFT OUTER JOIN CDepartment t4 ON t2.DEPARTMENT_ID = t4.id
LEFT OUTER JOIN Manager t8 ON t2.MANAGER_ID = t8.id
LEFT OUTER JOIN Manager t5 ON t4.DEPARTMENTMANAGER_ID = t5.id
LEFT OUTER JOIN FTEmployee t9 ON t8.id = t9.id
LEFT OUTER JOIN FTEmployee t6 ON t5.id = t6.id
LEFT OUTER JOIN CEmployee t10 ON t9.id = t10.id
LEFT OUTER JOIN CEmployee t7 ON t6.id = t7.id
WHERE t2.EMP_TYPEL = ? AND
t7.EMP_TYPEL = ? AND
t10.EMP_TYPEL = ? AND
t0.id = ? optimize for 1 row [params=(int) 3, (int) 3, (int) 3, (int) 1]

This 0-result sql is caused by the retrieval of the eager toOne field, Manager.
Note that the LEFT OUTER JOIN betweent t2 and t8 is to retrieve the Manager.
LEFT OUTER JOIN already takes care of possible null manager case. However,
the where clause did not consider the possible null manager case.
The correct clause should be:

WHERE t2.EMP_TYPEL = ? AND
(t7.EMP_TYPEL = ? OR t7.EMP_TYPEL IS NULL) AND
(t10.EMP_TYPEL = ? OR t10.EMP_TYPEL IS NULL) AND
t0.id = ? optimize for 1 row [params=(int) 3, (int) 3, (int) 3, (int) 1]