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

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.publishe开发者_高级运维d = 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!

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

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.