openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From MiƂosz Tylenda <mtyle...@o2.pl>
Subject Re: OpenJPA Beta 3 enforcing group by rules
Date Thu, 08 Apr 2010 19:01:28 GMT
Chris,

What happens if you rewrite the query like this:

SELECT dl.costcentre, sum(dl.bwCount), dl.bwRate,   sum(dl.bwCount  *
dl.bwRate),  sum(dl.clrCount),  dl.clrRate,  sum(dl.clrCount  * dl.clrRate)
FROM EquitracDataLoadLine  as dl where dl.batchId = '00000127C9E533DC' and
dl.contract.uniqueid = '0000012444686ECC' and dl.asset is not null 
group by dl.costcentre, dl.bwRate, dl.clrRate order by dl.costcentre

The result should be identical and you fullfill the requirement of having non-aggregates in
GROUP BY.

The requirement comes from the JPA spec:

"The requirements for the SELECT clause when GROUP BY is used follow those of SQL: namely,
any
item that appears in the SELECT clause (other than as an aggregate function or as an argument
to an
aggregate function) must also appear in the GROUP BY clause."

Cheers,
Milosz

> I have some queries that I have been using in my app for about a year now
> but since I started using Beta 3 they fail validation because OpenJPA wants
> me to include all of my select columns in the group by.  Since most of the
> select items are SUM(blah) I can't really do that and also there is no need
> for me to do it.  Here is my query:
> 
>  
> 
> SELECT dl.costcentre, sum(dl.bwCount), dl.bwRate,   sum(dl.bwCount  *
> dl.bwRate),  sum(dl.clrCount),  dl.clrRate,  sum(dl.clrCount  * dl.clrRate)
> FROM EquitracDataLoadLine  as dl where dl.batchId = '00000127C9E533DC' and
> dl.contract.uniqueid = '0000012444686ECC' and dl.asset is not null 
> 
> group by dl.costcentre order by dl.costcentre
> 
>  
> 
> OpenJPA complains:
> 
>  
> 
>  
> 
> <openjpa-2.0.0-beta3-r422266:926797 nonfatal user error>
> org.apache.openjpa.persistence.ArgumentException: Your query on type "class
> com.trm.dataload.linetypes.
> 
> EquitracDataLoadLine" with filter "SELECT dl.costcentre, sum(dl.bwCount),
> dl.bwRate,   sum(dl.bwCount  * dl.bwRate),  sum(dl.clrCount),  dl.clrRate,
> sum(dl.clrCount  * dl.clrRate) FROM EquitracDataLoadLine  as dl where
> dl.batchId = '00000127C9E533DC' and dl.contract.uniqueid =
> '0000012444686ECC' and dl.asset is not null group by dl.costcentre order by
> dl.costcentre" is invalid.  Your select and having clauses must only include
> aggregates or values that also appear in your grouping clause.        at
> org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor$Va
> lidateGroupingExpressionVisitor.enter(ExpressionStoreQuery.java:533)
> 
>         at org.apache.openjpa.kernel.exps.Val.acceptVisit(Val.java:119)
> 
>         at
> org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor$Va
> lidateGroupingExpressionVisitor.validate(ExpressionStoreQuery.java:514)
> 
>         at
> org.apache.openjpa.kernel.ExpressionStoreQuery$AbstractExpressionExecutor.va
> lidate(ExpressionStoreQuery.java:327)
> 
>         at org.apache.openjpa.kernel.QueryImpl.compile(QueryImpl.java:589)
> 
>         at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm
> pl.java:976)
> 
>         at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm
> pl.java:962)
> 
>         at
> org.apache.openjpa.persistence.EntityManagerImpl.createQuery(EntityManagerIm
> pl.java:98)
> 
>  
> 
> There is no "having" clause, so I'm not sure what it is complaining about.
> Not sure how this is non-fatal either because it's throwing an exception
> which I can catch but of course I can't execute the query, to me that is
> fatal.
> 
>  
> 
> Switching back to OpenJPA 1.2.1 and the error goes away. Back to OpenJPA
> Beta 3 and the error comes back.
> 
>  
> 
>  
> 
> Thanks for any advice.
> 
>  
> 
> Chris
> 
>  
> 
> 

Mime
View raw message