Invalid SQL while Embedding HSQLDB into a Rails App

Question

I am working on porting a Rails app to JRuby and HSQLDB. My goal is to embed a database and the site within a single JAR file for deployment at customer sites. I have the site working quite well from the JAR, with a few notable problems.

When I do the following with a pretty mundane ActiveRecord model:

@total = SessionLog.count(:id)

I get the following exception:

ActiveRecord::StatementInvalid (ActiveRecord::ActiveRecordError: Not in aggregate function or group by clause: org.hsqldb.Expression@7be117eb in statement [SELECT count(session_logs.id) AS count_id FROM session_logs WHERE (created_at >= '2010-02-06' AND created_at <= '2010-03-09' AND session_type = 'tunnel_client') ORDER BY id DESC ]:

SELECT count(session_logs.id) AS count_id FROM session_logs WHERE (created_at >= '2010-02-06' AND created_at <= '2010-03-09' AND session_type = 'tunnel_client') ORDER BY id DESC )

It seems clear to me that the COUNT statement is causing the trouble in HSQLDB, but I'm not sure what the solution is to fix this. SQLite3 and MySQL both process this SQL statement without issue.

I'm open to using a different database other than HSQLDB, but it needs to be embeddable into our application on the JVM. That is the appeal of HSQLDB.

Answer 1

You can try H2 Database, wire it like so. From wikipedia:

The database engine is written by Thomas Mueller. He also developed the Java database engine Hypersonic SQL [1]. In 2001, the Hypersonic SQL was stopped, and the HSQLDB Group was formed to continue work on the Hypersonic SQL code. The name H2 stands for Hypersonic 2, however H2 does not share any code with Hypersonic SQL or HSQLDB. H2 is built from scratch.

Answer 2

You've probably found a bug in the ActiveRecord adapter - activerecord-jdbchsqldb-adapter I assume.

Can you try run the SQL directly in some non-ruby SQL session? Then maybe you can see where it's going wrong and submit a bug or (better), submit a patch.