Strange behavior of HSQLDB and group by

Question

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.

Answer 1

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.

Answer 2

you could also aggregate all select values you dont want to group by as well:

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