SQL query throws "not in aggregate function or group by clause" exception

Question

I'm working on repairing the test suite for a project of ours, which is being tested through Hibernate/DBUnit. There are several test cases which all throw a similar exception from Hibernate, which looks something like this:

java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@109062e in statement [... blah ...]

Through my googling, I am suspicious that this is caused by our use of the aggregate function AVG(), as this is in the exception's message, and all of the queries that throw contain it. However, I discovered several links to people who were getting this error, and were able to fix it by either commenting out an "ORDER BY" or "GROUP BY" clause, or by including the other columns from the SELECT clause in the grouping. I understand why this would fix such an error message, but I'm not sure whether it applies to my situation, because I tried doing the same and it made no difference. Also, we have some test cases throwing exceptions which use ORDER/GROUP, but not all. For example:

ThingerVO myThinger = (ThingerVO)session.createQuery("SELECT new ThingerVO(" +
"r.id, " + "u.id, " + "u.alias, " + "s.id, " +
"s.name, " + "r.URL," + "AVG(v.rating), " +
"r.totalCount, " + "r.isPrivate, " + "a.id, " +
"a.name, " + "r.transactionId, " + "r.size, " +
"u.hasPicture " +
") FROM Thinger r LEFT OUTER JOIN r.votes as v, Table1S s " +
"JOIN s.Table2A AS a, User u " +
"WHERE r.userId = u.id AND " +
"s.id = r.Table1SId AND " +
"r.id = :thingId")    
.setInteger("thingId", thingId)
.uniqueResult();

This query also causes the same exception to be thrown, even though it doesn't use an ORDER/GROUP clause. Also, I cut/pasted the generated HSQL code from Hibernate directly into the MySQL query browser, and it ran without problems. Also, it's worth pointing out that all of this code works fine on our production database, so I'm really confused as to why it throws here.

Some other potentially useful information -- we're using a flat XML database structure with some dummy test data for the test cases, and the MySQL dialect for hibernate. We're using dbunit 2.4.3/hibernate 3.2.6. I tried using the latest hibernate, version 3.3.1, but it behaved the same.

Any pointers or hints would be greatly appreciated.

Answer 1

If you use an aggregate function (e.g. AVG()) in the SELECT part of the SQL query along with other non-aggregate expressions, then you must have a GROUP BY clause which should list all the non-aggregate expressions.

I'm not familiar with java, but looking at the code, it looks like it's going to create and run a query something like this (not quite right, but close enough, I think):

SELECT r.id, 
       u.id,
       u.alias,
       s.id, 
       s.name, 
       r.URL, 
       AVG(v.rating), 
       r.totalCount, 
       r.isPrivate, 
       a.id, 
       a.name, 
       r.transactionId,
       r.size, 
       u.hasPicture 
FROM Thinger r 
LEFT OUTER JOIN r.votes as v, 
                     Table1S s 
JOIN s.Table2A AS a, User u 
WHERE r.userId = u.id 
AND s.id = r.Table1SId 
AND r.id = :thingId

... This has no GROUP BY, but does mix aggregate and non-aggregate expressions in the SELECT clause. The problem is that the SQL is badly formed.

The fix would be to add a GROUP BY to the end of the query.

I can't say why this is working in your production system, but I suspect that there is some subtle difference there. Perhaps something is adding the GROUP BY automatically?

Can you post a printout of the SQL it executes?

Answer 2

How would that group by be formated?

Answer 3

Also, ORDER BY does not work in hsqldb when the order-by field is not a String.

Unfortunately, this results in the Not in aggregate function or group by clause error message, which suggests a grouping problem, hence the confusion...

See: http://markmail.org/message/42vmifme4opz4jgl