Does TopLink Essentials support the use of aggregate functions (like SUM
and COUNT) in the ORDER BY clause of a JPA query?
I have tried this using both GlassFish v1 and v2 and have not had any luck.
For example, a JPA query like:
SELECT t.name, COUNT(t.id) FROM Tag t GROUP BY t.name ORDER BY
COUNT(t.id) ASC
gives me:
Exception [TOPLINK-8024] (Oracle TopLink Essentials - 2.0 (Build
b52-rc (06/20/2007))):
oracle.toplink.essentials.exceptions.EJBQLException
Exception Description: Syntax error parsing the query
[SELECT t.name, COUNT(t.id) FROM Tag t GROUP BY t.name ORDER BY
COUNT(t.id) ASC], line 1, column 64: syntax error at [COUNT].
Internal Exception: line 1:64: expecting IDENT, found 'COUNT'
at
oracle.toplink.essentials.exceptions.EJBQLException.syntaxErrorAt(EJBQLException.java:379)
at
oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.handleANTLRException(EJBQLParser.java:335)
at
oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.addError(EJBQLParser.java:278)
at
oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.reportError(EJBQLParser.java:378)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.variableAccess(EJBQLParser.java:1441)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.pathExpression(EJBQLParser.java:977)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.stateFieldPathExpression(EJBQLParser.java:1512)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.orderByItem(EJBQLParser.java:4620)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.orderByClause(EJBQLParser.java:618)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.selectStatement(EJBQLParser.java:241)
at
oracle.toplink.essentials.internal.parsing.ejbql.antlr273.EJBQLParser.document(EJBQLParser.java:135)
at
oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.parse(EJBQLParser.java:166)
at
oracle.toplink.essentials.internal.parsing.ejbql.EJBQLParser.buildParseTree(EJBQLParser.java:127)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:215)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:189)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:153)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:114)
at
oracle.toplink.essentials.internal.ejb.cmp3.base.EJBQueryImpl.<init>(EJBQueryImpl.java:99)
at
oracle.toplink.essentials.internal.ejb.cmp3.EJBQueryImpl.<init>(EJBQueryImpl.java:86)
at
oracle.toplink.essentials.internal.ejb.cmp3.EntityManagerImpl.createQuery(EntityManagerImpl.java:204)
... 7 more
I know that my underlying DB (JavaDB) can handle aggregate functions in
the ORDER BY clause, as a native query like this works:
SELECT tag_name, COUNT(id) FROM tag GROUP BY tag_name ORDER BY COUNT(id)
From my reading of the Query Language chapter in the EJB 3.0
specification, I think that my original JPA query should be supported.
And Hibernate documentation
(
http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/queryhql.html)
seems to clearly indicate that they support it in their JPA implementation:
SQL functions and aggregate functions are allowed in the having and
order by clauses, if supported
by the underlying database (eg. not in MySQL). Example:
select cat from Cat cat join cat.kittens kitten group by cat having
avg(kitten.weight) > 100
order by count(kitten) asc, sum(kitten.weight) desc
So am I doing something wrong? Or is this a bug or limitation in
TopLink Essentials?
Thanks,
Jamey