OpenJPA
  1. OpenJPA
  2. OPENJPA-1814

JPQL fails with Group By and Having aggregate_expression IN (subquery)

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.2, 2.1.0
    • Fix Version/s: 1.2.3, 1.3.0, 2.0.2, 2.1.0
    • Component/s: kernel
    • Labels:
      None
    • Environment:
      Fix committed to trunk at revision #1002419.

      Description

      The following JPQL fail:

      String jpql = "SELECT a.uuid from EntityA a WHERE a.name = 'test' " +
      "GROUP BY a.date1, a.uuid " +

      (1) "HAVING MAX(a.date1) IN (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";
      (2) "HAVING MAX(a.date1) = (SELECT MAX(a1.date2) from EntityA a1 WHERE a1.name = 'test') ";

      org.apache.openjpa.persistence.ArgumentException: Encountered "MAX ( a .date1) IN" at character 168, but expected: ["(", ")", "*", "+", "-", ".", "/", ":", "<", "<=", "<>", "=", ">", ">=", "?", "ABS", "ALL", "AND", "ANY", "AS", "ASC", "AVG", "BETWEEN", "BOTH", "BY", "CONCAT","COUNT", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DELETE", "DESC", "DISTINCT", "EMPTY", "ESCAPE", "EXISTS", "FETCH", "FROM", "GROUP", "HAVING", "IN", "INNER", "IS", "JOIN", "LEADING", "LEFT","LENGTH", "LIKE", "LOCATE", "LOWER", "MAX", "MEMBER", "MIN", "MOD","NEW", "NOT", "NULL", "OBJECT", "OF", "OR", "ORDER", "OUTER", "SELECT",
      "SET", "SIZE", "SOME", "SQRT", "SUBSTRING", "SUM", "TRAILING", "TRIM","UPDATE", "UPPER", "WHERE", <BOOLEAN_LITERAL>, <DECIMAL_LITERAL>,<IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL>].
      at org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:9566)
      at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9443)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1947)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_factor(JPQL.java:1925)
      at org.apache.openjpa.kernel.jpql.JPQL.conditional_term(JPQL.java:1791)

      The fix involves two changes:
      (1) jjt grammar change
      (2) OpenJPA performs a preliminary validation to ensure the expression in the having clause is included in the group-by list. However, this validation checking should be done only on the LHS of the having clause (see expr1 below), not on the RHS (see expr2 below). For example:

      Having expr1 = expr2

      Only expr1 should be in the group-by list

      The current visitor pattern can not tell which node to visit without massive change. The alternative is to disable the checking by OpenJPA and let the backend to determine whether the generated SQL is valid or not.

      1. OPENJPA-1814.patch
        5 kB
        Fay Wang
      2. OPENJPA-1814-1.patch
        4 kB
        Fay Wang
      3. OPENJPA-1814-2.patch
        4 kB
        Catalina Wei
      4. OPENJPA-1814-1.2.x.patch
        4 kB
        Heath Thomann

        Issue Links

          Activity

          Hide
          Heath Thomann added a comment -

          I'm providing a patch (OPENJPA-1814-1.2.x.patch) which is for 1.2.x and it a "backport" of OPENJPA-1814-2.patch.

          Thanks,

          Heath

          Show
          Heath Thomann added a comment - I'm providing a patch ( OPENJPA-1814 -1.2.x.patch) which is for 1.2.x and it a "backport" of OPENJPA-1814 -2.patch. Thanks, Heath
          Hide
          Catalina Wei added a comment -

          OPENJPA-1814-2.patch committed to trunk at revision #1002419.

          Show
          Catalina Wei added a comment - OPENJPA-1814 -2.patch committed to trunk at revision #1002419.
          Hide
          Catalina Wei added a comment -

          In JPA Spec, aggregate expression is not allowed in the IN expression. The EBNF In Section 2.4.9 of JPA spec:

          in_expression ::=

          {state_field_path_expression | type_discriminator}

          [NOT] IN
          { ( in_item

          {, in_item}

          * ) | (subquery) | collection_valued_input_parameter }

          Therefore, the following JPQL results in syntax error:

          SELECT c5.uuid
          FROM pcs.common.fleet.CarLocationMessage c5
          WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false
          GROUP BY c5.sightDate, c5.uuid
          HAVING MAX(c5.sightDate) IN (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false)

          The above JPQL can be rewritten to use EQUAL comparison as below:

          SELECT c5.uuid
          FROM pcs.common.fleet.CarLocationMessage c5
          WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false
          GROUP BY c5.sightDate, c5.uuid
          HAVING MAX(c5.sightDate) = (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false)

          However, it still requires OPENJPA-1814-2.patch to overcome another problem in OpenJPA.
          We have a rather restrictive rules for validating having/grouping clause, the patch is to avoid having/grouping validation if conditional expression involving a subquery.
          In such cases, the having/grouping rules will be enforced by the backend DBMSs. Any violation would result in SQLException and would be considered as an user error.

          Show
          Catalina Wei added a comment - In JPA Spec, aggregate expression is not allowed in the IN expression. The EBNF In Section 2.4.9 of JPA spec: in_expression ::= {state_field_path_expression | type_discriminator} [NOT] IN { ( in_item {, in_item} * ) | (subquery) | collection_valued_input_parameter } Therefore, the following JPQL results in syntax error: SELECT c5.uuid FROM pcs.common.fleet.CarLocationMessage c5 WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false GROUP BY c5.sightDate, c5.uuid HAVING MAX(c5.sightDate) IN (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false) The above JPQL can be rewritten to use EQUAL comparison as below: SELECT c5.uuid FROM pcs.common.fleet.CarLocationMessage c5 WHERE c5.railCarNumber = UPPER(:carNumber) and c5.clmArchived = false GROUP BY c5.sightDate, c5.uuid HAVING MAX(c5.sightDate) = (SELECT MAX(c6.sightDate) from pcs.common.fleet.CarLocationMessage c6 WHERE c6.railCarNumber = UPPER(:carNumber) and c6.clmArchived = false) However, it still requires OPENJPA-1814 -2.patch to overcome another problem in OpenJPA. We have a rather restrictive rules for validating having/grouping clause, the patch is to avoid having/grouping validation if conditional expression involving a subquery. In such cases, the having/grouping rules will be enforced by the backend DBMSs. Any violation would result in SQLException and would be considered as an user error.
          Hide
          Fay Wang added a comment -

          clean up the patch

          Show
          Fay Wang added a comment - clean up the patch

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development