Grails：“不在聚合函数中” HQL查询的问题

发布于 2024-10-28 05:40:26 字数 486 浏览 1 评论 0原文

在 Grails 中使用 HSQLDB通过 HQL 进行查询

select a from Article a join a.tags t 
where t.name in (:tags) 
group by a 
having count(t)=:tag_count

会产生 SqlException“不在聚合函数中...”。查看生成的 SQL

SELECT a.id, a.title, a.url, ....
..
GROUP BY a.id
HAVING ..

在某些 MySQL 产品中，我听说过这种方法，但显然在 HSQLDB 中不行。我不明白：

为什么Hibernate会生成错误的SQL？或者不是吗？
为什么 HSQLDB 在选择批次时不允许仅对 id 进行分组，我的意思是它毕竟是主键，并且我的 SELECT 不会引入不确定值，例如随机或当前时间

原文

Querying by HQL with

select a from Article a join a.tags t 
where t.name in (:tags) 
group by a 
having count(t)=:tag_count

using HSQLDB in Grails produces an SqlException "Not in aggregate function...". Looking at the generated SQL provides

SELECT a.id, a.title, a.url, ....
..
GROUP BY a.id
HAVING ..

In some MySQL products this works, I have heard, but apparently not in HSQLDB. I don't understand:

Why does Hibernate generate faulty SQL? Or does it not?
Why is HSQLDB not allowing the grouping on just the id while selecting the lot, I mean its the primary key after all and my SELECT does not introduce indeterministic values such as a random or current time

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

Smile简单爱 2024-11-04 05:40:26

刚刚在 Hibernate 问题跟踪器中发现了此错误。看来这是一个已经存在五年的已知问题......令人难以置信。看到看似基本的东西能在这么长的时间内被破坏，总是令人惊讶。

回复收藏 0 原文

远山浅 2024-11-04 05:40:26

生成的 SQL 不太正确，因为我们应该对 SELECT 子句中的所有非聚合属性进行 GROUPBY。例如：

SELECT name,category FROM books
GROUP BY category;

上面的查询将给出错误数据，因为它只会为一个类别提供 1 行（即使在 MySQL 中运行）。相反，应该这样做：

SELECT name,category FROM books
GROUP BY name,category;

编辑：经过更多调查，我意识到 MySQL 是不遵守规则的人。出于某种查询优化的目的，MySQL 不允许不完整的 GROUP BY 查询（默认情况下，有一个选项可以关闭此功能）。如果您在 Oracle 或 HSQLDB 等其他数据库上运行查询，则会出现错误。详细答案可以在这个问题中找到。

EDIT2：要按照您的方式重写查询，除了在 Group By 子句中列出所有非聚合选定属性之外，没有其他方法。来自 Hibernate 文档：

Hibernate 目前还没有
展开分组实体，因此您不能
如果所有属性都写成 group by cat
cat 是非聚合的。你必须
列出所有非聚合属性
明确地。

@Tom：但我认为也许您不需要文章表中的所有字段。如果是这种情况，您介意提供有关域类以及您要查询的内容的更多信息，以便我们可以更清楚地看到问题吗？

The generated SQL is not very right, since we should GROUPBY all non-aggregated properties in SELECT clause. For example:

SELECT name,category FROM books
GROUP BY category;

The above query will give false data, because it will only give 1 rows for a category(even if running in MySQL). Instead, it should be done like this:

SELECT name,category FROM books
GROUP BY name,category;

EDIT: After some more investigating, I realize that MySQL is the one who doesn't follow the rule. For some sake of query optimization, MySQL does permit not full GROUP BY query(by default, there's an option to turn this function off). If you run the query on other databases like Oracle or HSQLDB, you will get errors. The detail answer is found in this question.

EDIT2: To re-write the query by your way, there's no other mean except listing all non-aggregation selected properties in Group By clause. From Hibernate doc:

Hibernate also does not currently
expand a grouped entity, so you cannot
write group by cat if all properties
of cat are non-aggregated. You have to
list all non-aggregated properties
explicitly.

@Tom: But I think that maybe you don't need all the fields in Article table. If that is the case, would you mind provide more information about the domain class and what you are going to query, so we can see the problem clearer?

回复收藏 0 原文