OpenJPA
  1. OpenJPA
  2. OPENJPA-2318

Left outer join is not generated when specifien using Criteria API

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.1.1
    • Fix Version/s: 2.2.1.1, 2.2.3, 2.3.0
    • Component/s: criteria
    • Labels:
      None
    • Environment:
      Windows/Oracle

      Description

      Entities
      @Entity
      @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
      public class DiscoveryObjectImpl {
      @Id
      private String id;
      public String getId()

      { return id; }

      public void setId(String id)

      { this.id = id; }

      @ManyToOne(cascade = CascadeType.ALL)
      private DiscoveryObjectImpl parent;

      public DiscoveryObjectImpl getParent()

      { return parent; }

      public void setParent(DiscoveryObjectImpl parent)

      { this.parent = parent; }

      }

      @Entity
      public class ColumnFormatImpl extends DiscoveryObjectImpl

      { @Basic String formatName; }

      @Entity
      public class ColumnImpl extends DiscoveryObjectImpl

      { @OneToMany(mappedBy="parent") private List<ColumnFormatImpl> formats = new ArrayList<ColumnFormatImpl>(); }

      @Entity
      public class VirtualTableImpl extends DiscoveryObjectImpl

      { @OneToMany(mappedBy="parent") private List<ColumnImpl> columns = new ArrayList<ColumnImpl>(); }

      persistence.xml
      <persistence xmlns="http://java.sun.com/xml/ns/persistence"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
      version="1.0">
      <persistence-unit name="Test" transaction-type="RESOURCE_LOCAL">
      <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
      <class>com.ibm.infosphere.test.model.interfaces.impl.DiscoveryObjectImpl</class>
      <class>com.ibm.infosphere.test.model.interfaces.impl.VirtualTableImpl</class>
      <class>com.ibm.infosphere.test.model.interfaces.impl.ColumnImpl</class>
      <class>com.ibm.infosphere.test.model.interfaces.impl.ColumnFormatImpl</class>
      <exclude-unlisted-classes>true</exclude-unlisted-classes>
      <properties>
      <property name="openjpa.Log" value="DefaultLevel=ERROR, Runtime=ERROR, Tool=ERROR, SQL=TRACE, MetaData=ERROR"/>
      <property name="openjpa.DynamicEnhancementAgent" value="false"/>
      <property name="openjpa.RuntimeUnenhancedClasses" value="supported"/>
      <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(ForeignKeys=true)"/>
      <property name="openjpa.InitializeEagerly" value="true"/>
      <property name="openjpa.jdbc.DBDictionary" value="oracle"/>
      <property name="openjpa.jdbc.DBDictionary" value="MaxTableNameLength=61"/>
      <property name="openjpa.jdbc.DBDictionary" value="useWildCardForCount=true"/>
      <property name="openjpa.jdbc.DBDictionary" value="JoinSyntax=sql92"/>
      <property name="openjpa.ConnectionURL" value="jdbc:oracle:thin:@localhost:1521:orcl"/>

      <property name="openjpa.ConnectionDriverName" value="oracle.jdbc.driver.OracleDriver"/>
      <property name="openjpa.ConnectionUserName" value="lev"/>
      <property name="openjpa.ConnectionPassword" value="lev"/>

      <property name="openjpa.ConnectionFactoryProperties" value="PrettyPrint=true, PrettyPrintLineLength=80, PrintParameters=true"/>
      <property name="openjpa.DataCache" value="true"/>
      <property name="openjpa.RemoteCommitProvider" value="sjvm"/>
      <property name="openjpa.jdbc.QuerySQLCache" value="true(EnableStatistics=true)"/>
      </properties>
      </persistence-unit>
      </persistence>

      Test code:
      public void test()

      { CriteriaQuery<ColumnImpl> cri = cb.createQuery(ColumnImpl.class); Root<VirtualTableImpl> tbl = cri.from(VirtualTableImpl.class); Join<VirtualTableImpl, ColumnImpl> col = tbl.join("columns"); Join<ColumnImpl, ColumnFormatImpl> format = col.join("formats", JoinType.LEFT); cri.where(cb.equal(format.get("formatName"), "ABC")); cri.select(col); em.createQuery(cri).getResultList(); }

      Generated SQL:
      SELECT t1.id, t1.PARENT_ID
      FROM VirtualTableImpl t0 INNER JOIN ColumnImpl t1 ON t0.id = t1.PARENT_ID INNER
      JOIN ColumnFormatImpl t2 ON t1.id = t2.PARENT_ID
      WHERE (t2.formatName = ? AND 1 = 1)

      As you can see the secong JOIN is INNER instead of LEFT OUTER

        Issue Links

          Activity

          Hide
          ASF subversion and git services added a comment -

          Commit 1548238 from Jody Grassel in branch 'openjpa/branches/2.2.x'
          [ https://svn.apache.org/r1548238 ]

          OPENJPA-2318: Left outer join is not generated when specified using Criteria API

          Show
          ASF subversion and git services added a comment - Commit 1548238 from Jody Grassel in branch 'openjpa/branches/2.2.x' [ https://svn.apache.org/r1548238 ] OPENJPA-2318 : Left outer join is not generated when specified using Criteria API
          Hide
          ASF subversion and git services added a comment -

          Commit 1548222 from Jody Grassel in branch 'openjpa/branches/2.2.1.x'
          [ https://svn.apache.org/r1548222 ]

          OPENJPA-2318: Left outer join is not generated when specified using Criteria API

          Show
          ASF subversion and git services added a comment - Commit 1548222 from Jody Grassel in branch 'openjpa/branches/2.2.1.x' [ https://svn.apache.org/r1548222 ] OPENJPA-2318 : Left outer join is not generated when specified using Criteria API
          Hide
          ASF subversion and git services added a comment -

          Commit 1514833 from Pinaki Poddar in branch 'openjpa/trunk'
          [ https://svn.apache.org/r1514833 ]

          OPENJPA-2318: Left outer join is not generated because allowNull was not passed correctly

          Show
          ASF subversion and git services added a comment - Commit 1514833 from Pinaki Poddar in branch 'openjpa/trunk' [ https://svn.apache.org/r1514833 ] OPENJPA-2318 : Left outer join is not generated because allowNull was not passed correctly
          Hide
          Ben added a comment -

          Fix for OPENJPA-2318

          Show
          Ben added a comment - Fix for OPENJPA-2318
          Hide
          Vermeulen added a comment -

          I found the cause of the problem!

          By simply following the call chain of join I found that this test can be made to pass by changing line 210 of org.apache.openjpa.persistence.criteria.Joins from

          path.get(_member.fmd, false);

          to

          path.get(_member.fmd, allowNull);

          This is the code that translates the join into an openJPA kernal expression and somehow fills in false for allowNull while it should be true according to
          allowNull = joinType != JoinType.INNER

          There is also another call to path.get using the literal value of false.

          I believe an OpenJPA committer should be able to quickly fix this issue now. (Note that I have no clue of the consequences of this change, perhaps the literal value of false was deliberate and perhaps it breaks other tests.)

          Show
          Vermeulen added a comment - I found the cause of the problem! By simply following the call chain of join I found that this test can be made to pass by changing line 210 of org.apache.openjpa.persistence.criteria.Joins from path.get(_member.fmd, false); to path.get(_member.fmd, allowNull); This is the code that translates the join into an openJPA kernal expression and somehow fills in false for allowNull while it should be true according to allowNull = joinType != JoinType.INNER There is also another call to path.get using the literal value of false. I believe an OpenJPA committer should be able to quickly fix this issue now. (Note that I have no clue of the consequences of this change, perhaps the literal value of false was deliberate and perhaps it breaks other tests.)
          Hide
          Vermeulen added a comment -

          I created an (almost) official OpenJPA test case that reproduces the issue.
          I took the CriteriaTest TestJPQLSubquery.testSubqueries6 as a starting point to create the test. It reuses the existing structure

          Customer 1..* accounts 1..1 owner

          public void testLeftJoinAfterInnerJoin()

          { String jpql = "SELECT c FROM Customer c INNER JOIN c.accounts a LEFT JOIN a.owner o"; String expectedSQL = "SELECT t0.id, t0.accountNum, t4.id, t4.city, t4.country, t4.county, t4.state, " + "t4.street, t5.userid, t5.DTYPE, t5.age, t5.compName, t5.creditRating, t5.name, t4.zipCode, " + "t0.balanceOwed, t0.creditRating, t0.filledOrderCount, t0.firstName, t0.lastName, t0.name, t0.status " + "FROM CR_CUST t0 " + "INNER JOIN CR_CUST_CR_ACCT t1 ON t0.id = t1.CUSTOMER_ID " + "LEFT OUTER JOIN CR_ADDR t4 ON t0.ADDRESS_ID = t4.id " + "INNER JOIN CR_ACCT t2 ON t1.ACCOUNTS_ID = t2.id " + "LEFT OUTER JOIN CompUser t5 ON t4.id = t5.ADD_ID " + "LEFT OUTER JOIN CR_PSN t3 ON t2.OWNER_ID = t3.id"; executeAndCompareSQL(jpql, expectedSQL); CriteriaQuery<Customer> q = cb.createQuery(Customer.class); Root<Customer> c = q.from(Customer.class); Join<Customer,Account> a = c.join(Customer_.accounts); Join<Account, Person> o = a.join(Account_.owner, JoinType.LEFT); q.select(c); assertEquivalence(q, jpql); }

          Simply copy/paste into TestJPQLSubquery and run.
          You will get a nice assertion error that compares the SQL for the JPQL query with the SQL for the Criteria query and you can see that only the last part of the query differs. JPQL has "LEFT OUTER JOIN" while Criteria has "INNER JOIN".

          Can an OpenJPA committer look into this? I think I have now done the necessary work I can do to pinpoint the issue.

          Show
          Vermeulen added a comment - I created an (almost) official OpenJPA test case that reproduces the issue. I took the CriteriaTest TestJPQLSubquery.testSubqueries6 as a starting point to create the test. It reuses the existing structure Customer 1..* accounts 1..1 owner public void testLeftJoinAfterInnerJoin() { String jpql = "SELECT c FROM Customer c INNER JOIN c.accounts a LEFT JOIN a.owner o"; String expectedSQL = "SELECT t0.id, t0.accountNum, t4.id, t4.city, t4.country, t4.county, t4.state, " + "t4.street, t5.userid, t5.DTYPE, t5.age, t5.compName, t5.creditRating, t5.name, t4.zipCode, " + "t0.balanceOwed, t0.creditRating, t0.filledOrderCount, t0.firstName, t0.lastName, t0.name, t0.status " + "FROM CR_CUST t0 " + "INNER JOIN CR_CUST_CR_ACCT t1 ON t0.id = t1.CUSTOMER_ID " + "LEFT OUTER JOIN CR_ADDR t4 ON t0.ADDRESS_ID = t4.id " + "INNER JOIN CR_ACCT t2 ON t1.ACCOUNTS_ID = t2.id " + "LEFT OUTER JOIN CompUser t5 ON t4.id = t5.ADD_ID " + "LEFT OUTER JOIN CR_PSN t3 ON t2.OWNER_ID = t3.id"; executeAndCompareSQL(jpql, expectedSQL); CriteriaQuery<Customer> q = cb.createQuery(Customer.class); Root<Customer> c = q.from(Customer.class); Join<Customer,Account> a = c.join(Customer_.accounts); Join<Account, Person> o = a.join(Account_.owner, JoinType.LEFT); q.select(c); assertEquivalence(q, jpql); } Simply copy/paste into TestJPQLSubquery and run. You will get a nice assertion error that compares the SQL for the JPQL query with the SQL for the Criteria query and you can see that only the last part of the query differs. JPQL has "LEFT OUTER JOIN" while Criteria has "INNER JOIN". Can an OpenJPA committer look into this? I think I have now done the necessary work I can do to pinpoint the issue.
          Hide
          Vermeulen added a comment -

          I reproduced the problem for another query with 3 entities that have no superclasses (except for @MappedSuperclass). I select from an inner join followed by a left join and get the exact same issue. (A 1..* B *..1 C)

          Show
          Vermeulen added a comment - I reproduced the problem for another query with 3 entities that have no superclasses (except for @MappedSuperclass). I select from an inner join followed by a left join and get the exact same issue. (A 1..* B *..1 C)
          Hide
          Vermeulen added a comment -

          Same issue when leaving out Category and making OrderLine 1 .. 1 Product optional (= 3 tables, 2 joins just like the example of this issue).

          Show
          Vermeulen added a comment - Same issue when leaving out Category and making OrderLine 1 .. 1 Product optional (= 3 tables, 2 joins just like the example of this issue).
          Hide
          Vermeulen added a comment -

          Not 100% sure but this may have to do with inheritance.

          My structure is like Order 1..* OrderLine 1..1 Product *..1 Category. Category is optional so when I select from Order I (should) get inner joins followed by a left join.
          My Order class extends from another abstract entity class using TABLE_PER_CLASS.
          When I leave out Order and select from OrderLine I do get an inner join followed by a left outer join.

          Show
          Vermeulen added a comment - Not 100% sure but this may have to do with inheritance. My structure is like Order 1..* OrderLine 1..1 Product *..1 Category. Category is optional so when I select from Order I (should) get inner joins followed by a left join. My Order class extends from another abstract entity class using TABLE_PER_CLASS. When I leave out Order and select from OrderLine I do get an inner join followed by a left outer join.
          Hide
          Vermeulen added a comment -

          I'm having exacly the same issue. When printing the internally generated "JPQL" using (OpenJPACriteriaQuery) q).toCQL() it does show a LEFT JOIN, but the actual generated SQL has an inner join.
          The manually written equivalent JPQL does generate SQL with "LEFT OUTER JOIN".

          The problem does not happen when the query has only one join.

          Show
          Vermeulen added a comment - I'm having exacly the same issue. When printing the internally generated "JPQL" using (OpenJPACriteriaQuery) q).toCQL() it does show a LEFT JOIN, but the actual generated SQL has an inner join. The manually written equivalent JPQL does generate SQL with "LEFT OUTER JOIN". The problem does not happen when the query has only one join.

            People

            • Assignee:
              Pinaki Poddar
              Reporter:
              Lev
            • Votes:
              2 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development