HSQLDB 1.8 not able to do order by in derived tables?

Question

Hi,

I have a query where I am doing an Order by inside of a derived table that is being inner joined.

Ex:

SELECT g.*
FROM (
    SELECT ...
    FROM ...
    ORDER BY alias.some_column
    LIMIT 0, 20
) as g
... # other joins

This works fine in MySQL, but it fails in HSQLDB. The reason I put the order by here is that mysql is much faster than putting the ORDER BY in the outside query. It is a pure performance win. However, HSQLDB is complaining.

This is the exception:

WARN JDBCExceptionReporter:100 - SQL Error: -70, SQLState: 37000
ERROR JDBCExceptionReporter:101 - Cannot be in ORDER BY clause in statement [SELECT g.* FROM gallery g    LEFT OUTER JOIN preview p       ON p.id = g.preview_id    INNER JOIN (       SELECT g.id, g.date_created       FROM gallery g       WHERE g.published = true        ORDER BY g.date_created DESC       LIMIT 0, 20    ) as g_ids on g_ids.id = g.id ]

This is a big problem because my tests use HSQLDB, but the production system is MySQL. I could just move these tests as "Exceptions" and run against mysql, but it's a lot slower... and making the production system slower just to get tests to pass defeats the point of tests.

I could just restructure my tests to use mysql for some tests instead of a blanket use hsqldb for everything... and that's a big test framework change for me. So is there a way I can get hsqldb to play nice, or do I have to improve my testing framework?

Thanks!

Answer 1

I would say improve your testing framework. It is always dangerous to write on one platform and deploy on another.

Answer 2

Instead of changing the testing framework, one option is to also consider using a newer version of HSQLDB, or using another Java database. There are Apache Derby (which doesn't support LIMIT unfortunately) and the H2 database engine.