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:
- GROUP BY doesn't work in HSQLDB if group by isnot on the basis of string.
- 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.