You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Fay Wang (JIRA)" <ji...@apache.org> on 2010/02/25 06:08:27 UTC
[jira] Resolved: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
[ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang resolved OPENJPA-1536.
-------------------------------
Resolution: Fixed
> 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
> Components: sql
> Affects Versions: 2.0.0-beta, 2.0.0-beta2
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 2.0.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.