[
https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Fay Wang updated OPENJPA-1536:
------------------------------
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]
was:
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 = 3 OR t10.EMP_TYPEL IS NULL) AND
t0.id = ? optimize for 1 row [params=(int) 3, (int) 3, (int) 3, (int) 1]
> SQL with outer join cannot handle null columns when inheritance is involved
> ---------------------------------------------------------------------------
>
> Key: OPENJPA-1536
> URL: https://issues.apache.org/jira/browse/OPENJPA-1536
> Project: OpenJPA
> Issue Type: Bug
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.1.0
>
> Attachments: OPENJPA-1536.patch
>
>
> 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]
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
