Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@openjpa.apache.org
Message-ID: <855323431.511331267055968015.JavaMail.jira@brutus.apache.org>
Date: Wed, 24 Feb 2010 23:59:28 +0000 (UTC)
From: "Fay Wang (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
 null columns when inheritance is involved
In-Reply-To: <513180425.508651267052247945.JavaMail.jira@brutus.apache.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


     [ 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.