OpenJPA
  1. OpenJPA
  2. OPENJPA-1740

Type expression for entites using Joined table strategy is not working properly

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.0
    • Fix Version/s: 2.1.0
    • Component/s: jdbc, kernel
    • Labels:
      None

      Activity

      Hide
      Fay Wang added a comment -

      The patch will generate the correct SQL for the type expression:
      JPQL: SELECT p FROM Person p where TYPE(p) <> Contractor

      (1) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.Dept_No, t0.FTEmpProp1
      FROM WFulltimeEmployee t0
      INNER JOIN WEmployee t2 ON t0.OID = t2.OID
      INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1)

      (2) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.Dept_No, t0.PTEmpProp1
      FROM WParttimeEmployee t0
      INNER JOIN WEmployee t2 ON t0.OID = t2.OID
      INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1)

      JPQL: SELECT p FROM Person p where TYPE(p) = Contractor

      (1) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.ContractorProp1, t0.Dept_No
      FROM WContractor t0
      INNER JOIN WEmployee t2 ON t0.OID = t2.OID
      INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1)

      Show
      Fay Wang added a comment - The patch will generate the correct SQL for the type expression: JPQL: SELECT p FROM Person p where TYPE(p) <> Contractor (1) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.Dept_No, t0.FTEmpProp1 FROM WFulltimeEmployee t0 INNER JOIN WEmployee t2 ON t0.OID = t2.OID INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1) (2) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.Dept_No, t0.PTEmpProp1 FROM WParttimeEmployee t0 INNER JOIN WEmployee t2 ON t0.OID = t2.OID INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1) JPQL: SELECT p FROM Person p where TYPE(p) = Contractor (1) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.ContractorProp1, t0.Dept_No FROM WContractor t0 INNER JOIN WEmployee t2 ON t0.OID = t2.OID INNER JOIN WPerson t1 ON t2.OID = t1.OID WHERE (1=1)
      Hide
      Fay Wang added a comment -

      In a data model, where Employee inherits from Person. and Contractor/FulltimeEmployee/ParttimeEmployee inherit from Employee using Joined table strategy, the following JPQL generates wrong sqls:

      JPQL: SELECT p FROM Person p where TYPE(p) = Contractor

      Push-down SQL:
      (1) SELECT t0.OID, t1.OID, t2.OID, t3.OID, t1.name
      FROM WContractor t0
      LEFT OUTER JOIN WEmployee t2 ON t0.OID = t2.OID
      LEFT OUTER JOIN WPerson t1 ON t2.OID = t1.OID
      LEFT OUTER JOIN WFulltimeEmployee t3 ON t2.OID = t3.OID
      WHERE t2.OID IS NULL AND t0.OID IS NULL AND t3.OID IS NULL

      (2) SELECT t0.OID, t2.OID, t1.OID, t3.OID, t2.name, t1.description
      FROM WContractor t0
      LEFT OUTER JOIN WEmployee t1 ON t0.OID = t1.OID
      INNER JOIN WPerson t2 ON t1.OID = t2.OID
      LEFT OUTER JOIN WFulltimeEmployee t3 ON t1.OID = t3.OID
      WHERE t0.OID IS NULL AND t3.OID IS NULL

      (3) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.ContractorProp1, t0.Dept_No
      FROM WContractor t0
      INNER JOIN WEmployee t2 ON t0.OID = t2.OID
      INNER JOIN WPerson t1 ON t2.OID = t1.OID

      (4) SELECT t0.OID, t1.OID, t2.OID, t1.name, t3.description, t2.Dept_No, t2.FTEmpProp1
      FROM WPerson t1
      INNER JOIN WEmployee t3 ON t1.OID = t3.OID
      INNER JOIN WFulltimeEmployee t2 ON t3.OID = t2.OID,
      WContractor t0

      Show
      Fay Wang added a comment - In a data model, where Employee inherits from Person. and Contractor/FulltimeEmployee/ParttimeEmployee inherit from Employee using Joined table strategy, the following JPQL generates wrong sqls: JPQL: SELECT p FROM Person p where TYPE(p) = Contractor Push-down SQL: (1) SELECT t0.OID, t1.OID, t2.OID, t3.OID, t1.name FROM WContractor t0 LEFT OUTER JOIN WEmployee t2 ON t0.OID = t2.OID LEFT OUTER JOIN WPerson t1 ON t2.OID = t1.OID LEFT OUTER JOIN WFulltimeEmployee t3 ON t2.OID = t3.OID WHERE t2.OID IS NULL AND t0.OID IS NULL AND t3.OID IS NULL (2) SELECT t0.OID, t2.OID, t1.OID, t3.OID, t2.name, t1.description FROM WContractor t0 LEFT OUTER JOIN WEmployee t1 ON t0.OID = t1.OID INNER JOIN WPerson t2 ON t1.OID = t2.OID LEFT OUTER JOIN WFulltimeEmployee t3 ON t1.OID = t3.OID WHERE t0.OID IS NULL AND t3.OID IS NULL (3) SELECT t0.OID, t1.OID, t1.name, t2.description, t0.ContractorProp1, t0.Dept_No FROM WContractor t0 INNER JOIN WEmployee t2 ON t0.OID = t2.OID INNER JOIN WPerson t1 ON t2.OID = t1.OID (4) SELECT t0.OID, t1.OID, t2.OID, t1.name, t3.description, t2.Dept_No, t2.FTEmpProp1 FROM WPerson t1 INNER JOIN WEmployee t3 ON t1.OID = t3.OID INNER JOIN WFulltimeEmployee t2 ON t3.OID = t2.OID, WContractor t0

        People

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

          Dates

          • Created:
            Updated:
            Resolved:

            Development