OpenJPA
  1. OpenJPA
  2. OPENJPA-1536

SQL with outer join cannot handle null columns when inheritance is involved

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-beta, 2.0.0-beta2
    • Fix Version/s: 2.0.0-beta3
    • Component/s: sql
    • Labels:
      None
    • Patch Info:
      Patch Available

      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]

        Activity

        Donald Woods made changes -
        Fix Version/s 2.0.0-beta3 [ 12314857 ]
        Fix Version/s 2.0.0 [ 12314019 ]
        Fay Wang made changes -
        Status Resolved [ 5 ] Closed [ 6 ]
        Fay Wang made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Fixed [ 1 ]
        Donald Woods made changes -
        Patch Info [Patch Available]
        Donald Woods made changes -
        Fix Version/s 2.0.0 [ 12314019 ]
        Fix Version/s 2.1.0 [ 12314542 ]
        Affects Version/s 2.0.0-beta [ 12314149 ]
        Affects Version/s 2.0.0-beta2 [ 12314802 ]
        Component/s sql [ 12311310 ]
        Fay Wang made changes -
        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]

         





        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]

         





        Fay Wang made changes -
        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 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]

         





        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]

         





        Hide
        Catalina Wei added a comment -

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

        Show
        Catalina Wei added a comment - Fay, I have reviewed your patch, and it looks good to me.
        Fay Wang made changes -
        Field Original Value New Value
        Attachment OPENJPA-1536.patch [ 12436918 ]
        Fay Wang created issue -

          People

          • Assignee:
            Fay Wang
            Reporter:
            Fay Wang
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development