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/24 23:57:27 UTC
[jira] Created: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
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
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.
[jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ 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 = 3 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 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]
> 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 = 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.
[jira] Resolved: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ 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.
[jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1536:
----------------------------------
Component/s: sql
Affects Version/s: 2.0.0-beta2
2.0.0-beta
Fix Version/s: (was: 2.1.0)
2.0.0
> 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.
[jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Donald Woods (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Donald Woods updated OPENJPA-1536:
----------------------------------
Patch Info: [Patch Available]
> 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.
[jira] Closed: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang closed OPENJPA-1536.
-----------------------------
> 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.
[jira] Commented: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "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.
[jira] Updated: (OPENJPA-1536) SQL with outer join cannot handle
null columns when inheritance is involved
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1536?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-1536:
------------------------------
Attachment: OPENJPA-1536.patch
> 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.