[
https://issues.apache.org/jira/browse/OPENJPA-1814?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Catalina Wei updated OPENJPA-1814:
----------------------------------
Attachment: OPENJPA-1814-2.patch
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.
> JPQL fails with Group By and Having aggregate_expression IN (subquery)
> ----------------------------------------------------------------------
>
> Key: OPENJPA-1814
> URL: https://issues.apache.org/jira/browse/OPENJPA-1814
> Project: OpenJPA
> Issue Type: Bug
> Components: kernel
> Affects Versions: 2.1.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Attachments: OPENJPA-1814-1.patch, OPENJPA-1814-2.patch,
> OPENJPA-1814.patch
>
>
> 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.
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
