[
https://issues.apache.org/jira/browse/OPENJPA-2572?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14371713#comment-14371713
]
Heath Thomann commented on OPENJPA-2572:
----------------------------------------
HI! I had my colleague, Jody Grassel, review my findings and upon further
review he noted that things are working as expect and that the JPA Spec's Query
Language BNF makes the parentheses illegal on the left hand side of the IN
clause. Let me post Jody's comments exactly as he sent them to me (i.e. let me
give credit where credit is due):
>>>>>>>>>>>>>>>>>>>> START >>>>>>>>>>>>>>>>>>>>>>
I've been looking at your JIRA's query: em.createQuery("select m from MyEntity2
m WHERE (m.id) IN (1,2,5)");
and I got the bug to go look at the JPA spec's Query language BNF:
QL_statement ::= select_statement | update_statement | delete_statement
select_statement ::= select_clause from_clause [where_clause] [groupby_clause]
[having_clause] [orderby_clause]
where_clause ::= WHERE conditional_expression
conditional_expression ::= conditional_term | conditional_expression OR
conditional_term
conditional_term ::= conditional_factor | conditional_term AND
conditional_factor
conditional_factor ::= [ NOT ] conditional_primary
conditional_primary ::= simple_cond_expression | (conditional_expression)
simple_cond_expression ::=
comparison_expression |
between_expression |
in_expression |
like_expression |
null_comparison_expression |
empty_collection_comparison_expression |
collection_member_expression |
exists_expression
in_expression ::=
{state_field_path_expression | type_discriminator} [NOT] IN
{ ( in_item {, in_item}* ) | (subquery) |
collection_valued_input_parameter }
<probably this bnf branch, as the sample doesn't use TYPE()>
state_field_path_expression ::=
general_identification_variable.{single_valued_object_field.}*state_field
general_identification_variable ::=
identification_variable |
KEY(identification_variable) |
VALUE(identification_variable)
<not this one, since the expression isnt' a TYPE(xx) IN (yy).>
type_discriminator ::=
TYPE(identification_variable |
single_valued_object_path_expression |
input_parameter)
JPA 2.0 Spec section 4.4.2 "Identification Variables" addresses what
constitutes as what's valid for that pattern.
The section "Abstract Schema Types and Query Domains" defines state field as:
For every persistent field or get accessor method (for a persistent property)
of the class, there
is a field (“state field”) whose abstract schema type corresponds to that of
the field or the
result type of the accessor method.
So I would assume "state field" is defined as a pattern that makes for a legal
persistent property name.
So in your example, "m.id" matches state_field_path_expression, where m is the
identification_variable and id is the state_field. The BNF grammar doesn't
declare () as elements that can appear in the in_expression at all.
This may suggest that "WHERE (m.id) IN (1,2,5)" usage is illegal here after
all, as you can see the BNF after IN has ()s in its grammer: [NOT] IN { (
in_item {, in_item}* ) | (subquery) | collection_valued_input_parameter }
>>>>>>>>>>>>>>>>>>>> END >>>>>>>>>>>>>>>>>>>>>>
In addition to Jody's comment I would like to add that *some* databases may
actually allow for parenthesis on the left had side of an IN expression. In
other words, if you take OpenJPA out of the picture, and directly execute in
your favorite SQL editor the SQL with parenthesis on the left hand side, the
SQL may work fine. However, this is moot given the JPA Spec's Query Language
BNF doesn't allow it. I apologize for not catching this sooner!
Thanks,
Heath
> Parentheses before an IN operator causes a parser exception.
> ------------------------------------------------------------
>
> Key: OPENJPA-2572
> URL: https://issues.apache.org/jira/browse/OPENJPA-2572
> Project: OpenJPA
> Issue Type: Bug
> Components: sql
> Affects Versions: 2.2.3, 2.4.0
> Reporter: Heath Thomann
> Priority: Critical
>
> I have recreated a simple version of a more complex query which is not
> working. Take these two queries which work fine and as expected:
> em.createQuery("select m from MyEntity2 m WHERE (m.id = 1)");
> em.createQuery("select m from MyEntity2 m WHERE m.id IN (1,2,5)");
> Now take this query and note the parentheses around m.id:
> em.createQuery("select m from MyEntity2 m WHERE (m.id) IN (1,2,5)");
> This yields the following exception:
> Caused by: <openjpa-2.2.3-SNAPSHOT-r422266:1655221M nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Encountered "m . id ) IN"
> at character 34, 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", "TYPE", "UPDATE", "UPPER", "WHERE", <DATE_LITERAL>,
> <DECIMAL_LITERAL>, <IDENTIFIER>, <INTEGER_LITERAL>, <STRING_LITERAL2>,
> <STRING_LITERAL>, <TIMESTAMP_LITERAL>, <TIME_LITERAL>].
> at
> org.apache.openjpa.kernel.jpql.JPQL.generateParseException(JPQL.java:13180)
> at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:13054)
> at
> org.apache.openjpa.kernel.jpql.JPQL.conditional_primary(JPQL.java:1980)
> ........
> Looks like something in our parser doesn't expect this.
> Oh, BTW, the answer is not to simply remove the parentheses......ultimately
> what we want to do is something more like the following (this works when I
> execute from my Oracle SQL editor):
> SELECT * FROM MyEntity2 t0 WHERE (t0.id, t0.num) IN (SELECT t1.id, t1.num
> FROM MyEntity2 t1 GROUP BY t1.id, t1.num);
> I.e. we need the parentheses.
> Thanks,
> Heath Thomann
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
