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-2.patch
        4 kB
        Catalina Wei
      2. OPENJPA-1814-1.patch
        4 kB
        Fay Wang
      3. OPENJPA-1814-1.2.x.patch
        4 kB
        Heath Thomann
      4. OPENJPA-1814.patch
        5 kB
        Fay Wang

        Issue Links

          Activity

          Michael Dick made changes -
          Status Open [ 1 ] Closed [ 6 ]
          Resolution Fixed [ 1 ]
          Michael Dick made changes -
          Fix Version/s 1.3.0 [ 12313326 ]
          Fix Version/s 2.0.2 [ 12315257 ]
          Michael Dick made changes -
          Affects Version/s 1.2.2 [ 12313681 ]
          Michael Dick made changes -
          Fix Version/s 1.2.3 [ 12314517 ]
          Heath Thomann made changes -
          Attachment OPENJPA-1814-1.2.x.patch [ 12456024 ]
          Catalina Wei made changes -
          Assignee Fay Wang [ faywang ] Catalina Wei [ fancy ]
          Fix Version/s 2.1.0 [ 12314542 ]
          Environment Fix committed to trunk at revision #1002419.
          Catalina Wei made changes -
          Attachment OPENJPA-1814-2.patch [ 12455865 ]
          Fay Wang made changes -
          Attachment OPENJPA-1814-1.patch [ 12455511 ]
          Fay Wang made changes -
          Attachment OPENJPA-1814.patch [ 12455509 ]
          Fay Wang made changes -
          Field Original Value New Value
          Link This issue relates to OPENJPA-712 [ OPENJPA-712 ]
          Fay Wang created issue -

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development