OpenJPA
  1. OpenJPA
  2. OPENJPA-1547

NOT IN with MEMBER OF returns syntax error

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.2, 2.0.0-beta2, 2.0.0
    • Fix Version/s: 2.0.0-beta3
    • Component/s: query
    • Labels:
      None

      Description

      With the following entities:

      @Entity
      public class EntityA

      { @Id int id; int age; String name; }

      @Entity
      public class EntityB

      { @Id int id; int age; @PersistentCollection(fetch=FetchType.LAZY) String[] nickNames; }

      OpenJPA generates wrong SQL for the JPQL:
      String jpql = "SELECT a.name FROM EntityA a WHERE a.id not in " +
      "(select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)";

      SELECT t0.name FROM EntityA t0
      WHERE (0 = (SELECT COUNT FROM
      WHERE (t0.id IN (
      SELECT t1.id
      FROM EntityB t1
      INNER JOIN EntityB_nickNames t2 ON t1.id = t2.ENTITYB_ID
      WHERE (t2.element = ?) )) )) [params=(String) token1]

      Note that if the JPQL is:
      String jpql = "SELECT a.name FROM EntityA a WHERE a.id in " +
      "(select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)";

      The push-down sql is correct:
      SELECT t0.name FROM EntityA t0
      WHERE (t0.id IN
      (SELECT t1.id
      FROM EntityB t1
      INNER JOIN EntityB_nickNames t2 ON t1.id = t2.ENTITYB_ID
      WHERE (t2.element = ?) ));

        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 ]
        Kevin Sutter made changes -
        Fix Version/s 2.0.0 [ 12314019 ]
        Fix Version/s 2.1.0 [ 12314542 ]
        Affects Version/s 2.0.0-beta2 [ 12314802 ]
        Affects Version/s 1.2.2 [ 12313681 ]
        Affects Version/s 2.0.0 [ 12314019 ]
        Component/s query [ 12311309 ]
        Fay Wang made changes -
        Field Original Value New Value
        Attachment OPENJPA-1547.patch [ 12437632 ]
        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