SQL查询抛出“not in aggregate function或group by子句”异常

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:

我正在修复我们的一个项目的测试套件，它正在通过Hibernate/DBUnit进行测试。有几个测试用例都对Hibernate抛出了类似的异常，它看起来是这样的:

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

java.sql。SQLException:不属于聚合函数或group by子句:org.hsqldb。Expression@109062e在声明中[…废话……)

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:

通过我的google，我怀疑这是由于我们使用聚合函数AVG()引起的，因为这是在异常消息中，抛出的所有查询都包含它。但是，我发现了一些与此错误相关的人的链接，并且能够通过注释掉“ORDER by”或“GROUP by”子句，或者通过在分组中的SELECT子句中包含其他列来修复它。我理解为什么这会修复这样一个错误消息，但我不确定它是否适用于我的情况，因为我尝试过这样做，但这没有什么区别。此外，我们有一些测试用例抛出异常，这些异常使用ORDER/GROUP，但不是全部。例如:

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.

这个查询还会导致抛出相同的异常，即使它不使用ORDER/GROUP子句。此外，我将从Hibernate生成的HSQL代码直接剪切/粘贴到MySQL查询浏览器中，并且运行时没有问题。另外，值得指出的是，所有这些代码在我们的产品数据库上都运行得很好，所以我真的很困惑为什么会出现在这里。

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.

其他一些可能有用的信息——我们使用的是扁平的XML数据库结构，测试用例使用的是假的测试数据，hibernate使用的是MySQL方言。我们使用的是dbunit 2.4.3/hibernate 3.2.6。我尝试使用最新的hibernate，版本3.3.1，但是它的表现是一样的。

Any pointers or hints would be greatly appreciated.

如有任何建议或提示，将不胜感激。

3 个解决方案

#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.

如果在SQL查询的SELECT部分中使用聚合函数(例如AVG())和其他非聚合表达式，则必须有一个GROUP BY子句，该子句应该列出所有非聚合表达式。

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):

我不熟悉java，但是看看代码，看起来它会创建并运行类似这样的查询(不是很正确，但我认为已经足够接近了):

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.

…它没有GROUP BY，但是在SELECT子句中混合了聚合表达式和非聚合表达式。问题是SQL的格式很糟糕。

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?

你能把它执行的SQL打印出来吗?

#2

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

而且，当ORDER - BY字段不是字符串时，ORDER BY在hsqldb中不起作用。

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

不幸的是，这导致了Not in聚集函数或group by子句错误消息，这提示了一个分组问题，从而导致混乱……

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

参见:http://markmail.org/message/42vmifme4opz4jgl

#3

In some systems (for example TALEND) query doesn't work if there are comment lines example:

在某些系统(例如TALEND)中，如果有注释行，查询将不起作用。

SELECT r.id,   
   u.alias,
   AVG(v.rating), 
   r.totalCount
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
--AND r.name is not null 
GROUP BY r.id, u.alias, r.totalCount

Gives an error for MS SQL queries. Instead of comment line

为MS SQL查询提供错误。而不是注释行

--

- - -

use these symbols for commenting

使用这些符号进行评论

/* AND r.name is not null */

/*和r.name不为空*/

Maybe it will help someone and save some time.

也许它会帮助别人，节省一些时间。

#1