[jira] Commented: (OPENJPA-1536) SQL with outer join cannot handle null columns when inheritance is involved

["Catalina Wei (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12838079#action_12838079 ] 

Catalina Wei commented on OPENJPA-1536:
---------------------------------------

Fay,
I have reviewed your patch, and it looks good to me.

> 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 t10.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]
>  

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.