You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by Ognjen Blagojevic <og...@etf.bg.ac.yu> on 2008/09/03 10:46:27 UTC
Bug in HAVING clause (JPQL)
Hi devs,
It seems that these two (valid?) queries are throwing an exception:
select m.idPublisher, max(m.datePublished)
from Magazine m
group by m.idPublisher
having max(m.datePublished) is null
select m.idPublisher, max(m.datePublished)
from Magazine m
group by m.idPublisher
having max(m.datePublished) = current_date
The exception thrown is:
<openjpa-1.3.0-SNAPSHOT-runknown nonfatal user error>
org.apache.openjpa.persistence.ArgumentException: Encountered "max ( m .
datePublished ) is" at character 90, 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:9501)
at org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9378)
...
For test case, one could use these classes:
/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Magazine.java
/openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Publisher.java
Configuration:
- OpenJPA 1.2.0 / OpenJPA 1.3.0 SNAPSHOT
- Enhancing at Build Time
- MySQL 5.0.22
Regards,
Ognjen
Re: Bug in HAVING clause (JPQL)
Posted by Kevin Sutter <kw...@gmail.com>.
Ognjen,
Unfortunately, I found this quite easy to reproduce myself. I have opened
openjpa-712 (https://issues.apache.org/jira/browse/OPENJPA-712) to track
this problem, but I do not have time to immediately resolve it. Maybe
somebody else will be able to pick it up. I've been able to determine that
our parser does not seem to be setup to properly detect the aggregate
functions within the "having" clause. Once the parser gets cleaned up, then
we would need to update our query generation code to perform the
corresponding functions based on the dictionary. For me, this is not a
trivial task. For others with more background in the sql generation, this
may be relatively easy...
Kevin
On Wed, Sep 3, 2008 at 3:46 AM, Ognjen Blagojevic <og...@etf.bg.ac.yu>wrote:
> Hi devs,
>
> It seems that these two (valid?) queries are throwing an exception:
>
> select m.idPublisher, max(m.datePublished)
> from Magazine m
> group by m.idPublisher
> having max(m.datePublished) is null
>
> select m.idPublisher, max(m.datePublished)
> from Magazine m
> group by m.idPublisher
> having max(m.datePublished) = current_date
>
> The exception thrown is:
>
> <openjpa-1.3.0-SNAPSHOT-runknown nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Encountered "max ( m .
> datePublished ) is" at character 90, 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:9501)
> at
> org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9378)
> ...
>
> For test case, one could use these classes:
>
>
> /openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Magazine.java
>
> /openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Publisher.java
>
>
> Configuration:
> - OpenJPA 1.2.0 / OpenJPA 1.3.0 SNAPSHOT
> - Enhancing at Build Time
> - MySQL 5.0.22
>
> Regards,
> Ognjen
>
Re: Bug in HAVING clause (JPQL)
Posted by Fay Wang <fy...@yahoo.com>.
The manual says "The HAVING clause must specify search conditions over the grouping items or aggregate functions that apply to grouping items." Therefore, the two JPQL should be valid:
select m.idPublisher, max(m.datePublished)
from Magazine m
group by m.idPublisher
having max(m.datePublished) is null
select m.idPublisher, max(m.datePublished)
from Magazine m
group by m.idPublisher
having max(m.datePublished) = current_date
You might want to open a JIRA to address this problem.
--- On Wed, 9/3/08, Ognjen Blagojevic <og...@etf.bg.ac.yu> wrote:
> From: Ognjen Blagojevic <og...@etf.bg.ac.yu>
> Subject: Bug in HAVING clause (JPQL)
> To: "OpenJPA developer list" <de...@openjpa.apache.org>
> Date: Wednesday, September 3, 2008, 1:46 AM
> Hi devs,
>
> It seems that these two (valid?) queries are throwing an
> exception:
>
> select m.idPublisher, max(m.datePublished)
> from Magazine m
> group by m.idPublisher
> having max(m.datePublished) is null
>
> select m.idPublisher, max(m.datePublished)
> from Magazine m
> group by m.idPublisher
> having max(m.datePublished) = current_date
>
> The exception thrown is:
>
> <openjpa-1.3.0-SNAPSHOT-runknown nonfatal user error>
>
> org.apache.openjpa.persistence.ArgumentException:
> Encountered "max ( m .
> datePublished ) is" at character 90, 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:9501)
> at
> org.apache.openjpa.kernel.jpql.JPQL.jj_consume_token(JPQL.java:9378)
> ...
>
> For test case, one could use these classes:
>
> /openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Magazine.java
> /openjpa/trunk/openjpa-persistence-jdbc/src/test/java/org/apache/openjpa/persistence/query/Publisher.java
>
>
> Configuration:
> - OpenJPA 1.2.0 / OpenJPA 1.3.0 SNAPSHOT
> - Enhancing at Build Time
> - MySQL 5.0.22
>
> Regards,
> Ognjen