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

        Hide
        Fay Wang added a comment -

        The patch will process the following JPQL:

        SELECT a.name FROM EntityA a
        WHERE a.id not in
        (select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames)

        and generate the following SQL:

        SELECT t0.name FROM EntityA t0 WHERE
        (NOT (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]

        Show
        Fay Wang added a comment - The patch will process the following JPQL: SELECT a.name FROM EntityA a WHERE a.id not in (select b.id FROM EntityB b where ?1 MEMBER OF b.nickNames) and generate the following SQL: SELECT t0.name FROM EntityA t0 WHERE (NOT (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]
        Hide
        Catalina Wei added a comment -

        The patch looks good to me.

        Show
        Catalina Wei added a comment - The patch looks good to me.

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development