HSQLDB的奇怪行为和分组依据 [英] Strange behavior of HSQLDB and group by

问题描述

我正在使用HSQLDB编写Junits，而我的查询是这样的：

  String queryStr = from ManualUrlBatchModel，其中状态为IN （：status）按batchGroup分组，用户按creationTime排序； 
查询查询= getSession（requestType）.createQuery（queryStr）; 
 query.setParameterList（ status，status）; 
  

我正在以给定状态（取决于创建时间FIFO顺序）为每个用户检索一批。 / p>

在端到端测试中运行良好，但在编写Junit时失败。

异常说：

 原因：java.sql.SQLException：不在聚合函数或group by子句中：org.hsqldb.Expression@164f8d4在语句[选择manualurlb0_ .manual_url_batch_id如manual1_7_，manualurlb0_.creation_time如creation2_7_，manualurlb0_.modification_time如modifica3_7_，manualurlb0_.attribute_list如attribute4_7_，manualurlb0_.batch_name如batch5_7_，manualurlb0_.batch_user如batch6_7_，manualurlb0_.input_s3_key如input7_7_，manualurlb0_.locale如locale7_，manualurlb0_.notify_when_complete作为notify9_7_，manualurlb0_.output_s3_key作为output10_7_，manualurlb0_.processed_url_count处理为11_7_，manualurlb0_.s3_bucket作为s12_7_，manualurlb0_.status作为status7_，manualurlb0_.submitted_url_count作为Submitted1 4_7_，manualurlb0_.total_url_count总计为15_7_，来自csi_manual_url_batch manualurlb0_，其中manualurlb0_.status位于（？ ，？）按manualurlb0_.batch_user分组，由manualurlb0_.creation_time按url。
 [junit]在org.hsqldb.jdbc.Util.throwError（Unknown Source）
 [junit]在org.hsqldb.jdbc.jdbcPreparedStatement 。< init>（未知来源），位于org.hsqldb.jdbc.jdbcConnection.prepareStatement的
 [junit]（未知来源），位于org.hibernate.jdbc.AbstractBatcher.getPreparedStatement（AbstractBatcher。 java：534）org.hibernate.jdbc.AbstractBatcher.getPreparedStatement（AbstractBatcher.java:452）
 [junit] org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement（AbstractBatcher.java： 161）org.hibernate.loader.Loader.prepareQueryStatement（Loader.java:1573）中的
 [junit] org.hibernate.loader.Loader.doQuery（Loader.java:696）中的
 [junit] 
 [junit]位于org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections（Loader.java:259）
 [junit]位于org.hibernate.loader.Loader.doList（Loader.java:2228）
  

我在网上发现了几件事：

如果$ group $不是基于字符串，则

1. GROUP BY在HSQLDB中不起作用。


2. GROUP BY不能以我使用的方式工作（选择* ....按COL1分组）。

我敢肯定人们会更早遇到这个问题，你们当时做了什么（除了不写Junit :)）？
任何帮助将不胜感激。

解决方案

在标准SQL中，group by子句必须包含每个选定的值，但

 从表组中选择a，b，c，d，sum（e），按- 
无效，从表组中按a，b选择a，b，c，d，sum（e）-invalid 
从a组成的表组中选择a，b，c，d，sum（e） ，b，c-无效
通过a，b，c，d从表组中选择a，b，c，d，sum（e）-无效
  

因此，由Hibernate生成的查询无效，并且AFAIK（通过HQL查询进行分组的唯一可能方法）是列出您想要的每个标量列明确选择（请参见 https://hibernate.onjira.com/browse/HHH-1615 ）：

 从ManualUrlBatchModel中选择m.foo，m.bar，m.creationTime，m.batchUser 
 m 
其中m .status IN（：status）
由m.foo，m.bar，m.crea分组tionTime，m.batchUser 
由m.creationTime 
  

如果您的原始查询适用于MySQL ，这是因为MySQL不遵守SQL标准，并且允许 group by 的查询不列出每个选定的列。我建议不要依赖此功能，在测试和生产中使用相同的数据库，并使用PostgreSQL而不是MySQL。

I am using HSQLDB for writing junits and my query is like this:

String queryStr = "from ManualUrlBatchModel where status IN(:status) group by batchUser order by creationTime";
        Query query = getSession(requestType).createQuery(queryStr);
        query.setParameterList("status", status);

I am retrieving one batch per user in the given status (depending on creation time FIFO order).

It runs fine for end to end testing but fails while writing junits.

Exception says:

Caused by: java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@164f8d4 in statement [select manualurlb0_.manual_url_batch_id as manual1_7_, manualurlb0_.creation_time as creation2_7_, manualurlb0_.modification_time as modifica3_7_, manualurlb0_.attribute_list as attribute4_7_, manualurlb0_.batch_name as batch5_7_, manualurlb0_.batch_user as batch6_7_, manualurlb0_.input_s3_key as input7_7_, manualurlb0_.locale as locale7_, manualurlb0_.notify_when_complete as notify9_7_, manualurlb0_.output_s3_key as output10_7_, manualurlb0_.processed_url_count as processed11_7_, manualurlb0_.s3_bucket as s12_7_, manualurlb0_.status as status7_, manualurlb0_.submitted_url_count as submitted14_7_, manualurlb0_.total_url_count as total15_7_ from csi_manual_url_batch manualurlb0_ where manualurlb0_.status in (? , ?) group by manualurlb0_.batch_user order by manualurlb0_.creation_time]
    [junit]     at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    [junit]     at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    [junit]     at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    [junit]     at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
    [junit]     at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
    [junit]     at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
    [junit]     at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1573)
    [junit]     at org.hibernate.loader.Loader.doQuery(Loader.java:696)
    [junit]     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    [junit]     at org.hibernate.loader.Loader.doList(Loader.java:2228)

I found couple of things on net:

1. GROUP BY doesn't work in HSQLDB if group by isnot on the basis of string.
2. GROUP BY doesn't work the way I have used (select * .... group by COL1).

I am sure people would have faced this issue earlier, what did you guys do then (apart from not writing the junits :))? Any help would be appreciated.

解决方案

In standard SQL, the group by clause must contain every selected value except the ones which are aggregate functions.

select a, b, c, d, sum(e) from table group by a -- INVALID
select a, b, c, d, sum(e) from table group by a, b  -- INVALID
select a, b, c, d, sum(e) from table group by a, b, c  -- INVALID
select a, b, c, d, sum(e) from table group by a, b, c, d -- VALID

The query generated by Hibernate is thus invalid, and AFAIK, the only possible way to make a group by HQL query work is to list every scalar column you want to select explicitely (see https://hibernate.onjira.com/browse/HHH-1615):

 select m.foo, m.bar, m.creationTime, m.batchUser  
 from ManualUrlBatchModel m 
 where m.status IN(:status) 
 group by m.foo, m.bar, m.creationTime, m.batchUser 
 order by m.creationTime

If your original query works with MySQL, it's because MySQL doesn't respect the SQL standard, and allows queries with group by not listing every selected column. I would suggest not relying on this "feature", using the same database in tests and in production, and using PostgreSQL instead of MySQL.

这篇关于HSQLDB的奇怪行为和分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！