Query works in PostgreSQL but fails with HSQL. How can I fix it?

Question

Dear SQL gurus ;-) I have the following query (inherited from legacy) similar to

SELECT bla FROM table
WHERE 
  some.id IN ( 
    SELECT id FROM (

      SELECT some FROM tag WHERE bla
      UNION  
      SELECT some FROM dossierinfo WHERE bla 
      ORDER BY tag LIMIT :limit OFFSET :offset

    ) AS aggregated
    WHERE dossier_type = 'auto'
  ) 
)

The full SQL is at the bottom. The problem is that is executes fine in PostgreSQL 8.2.x. For testing I added an embedded HSQL 1.8.x db, but then the query fails with

07 Sep 2010 13:55:11.914 [WARN] [main] [org.hibernate.util.JDBCExceptionReporter] - SQL Error: -11, SQLState: 37000
07 Sep 2010 13:55:11.914 [ERROR] [main] [org.hibernate.util.JDBCExceptionReporter] - Unexpected token ORDER, requires ) in statement [...]

So I figure out, HSQL does not like the ORDER in the inner join.

• Is this order not proper SQL?
• Is upgrading HSQL to 2.0 worth it? Would it support the order? I quickly tried it, but had some problems.
• Rewriting the query is not an option, I just wanted to add a test for that.

Full Query:

SELECT *, article_count as articlecount FROM tag 
WHERE 
  tag.id IN ( 
    SELECT id FROM (

      SELECT tag.tag AS tag, tag.id as id, tag.article_count as articleCount, 'auto' AS dossier_type FROM tag         
           WHERE tag.tag_count >= :minimumTagCount  AND tag.article_count >= :minimumArticleCount AND
           LENGTH(tag.tag) >= :minimumTagLength  AND 
           tag.tag NOT IN (SELECT dossierinfo.name FROM dossierinfo)  AND tag.is_stopword = :stopword  
      UNION  
      SELECT dossierinfo.name AS tag, dossierinfo.id AS id, dossierinfo.article_count as articleCount,
            'manual' AS dossier_type FROM dossierinfo  
      WHERE dossierinfo.article_count >= :minimumArticleCount  

      ORDER BY tag  LIMIT :limit OFFSET :offset

    ) AS aggregated


    WHERE dossier_type = 'auto'
  ) 
)

PS: My HSQL setup works for all other tests, so it's working and executing.

Answer 1

My guess is that you need to be explicit about to what the ORDER BY refers:

Instead of this:

SELECT some FROM tag WHERE bla
UNION  
SELECT some FROM dossierinfo WHERE bla 
ORDER BY tag LIMIT :limit OFFSET :offset

Try this:

SELECT some FROM (
      SELECT some FROM tag WHERE bla
      UNION  
      SELECT some FROM dossierinfo WHERE bla
) AS union_data 
ORDER BY tag LIMIT :limit OFFSET :offset

I would do this anyway, even in PostgreSQL. The original looks like the ORDER BY goes with the second SELECT in the UNION.

Answer 2

ORDER BY in the subquery with LIMIT and OFFSET is supported by some SQL dialects, but not all.

HSQLDB 2.0 supports this. Download the latest snapshot jars from http://hsqldb.org/support/ which have bug fixes and are more compatible with Postgres and other dialects.

Latest Hibernate 3.5.5 and 3.5.6 include a dialect which is compatible with the snapshot jars as well as HSQLDB 1.8.x.

If you have any problem with SQL queries and HSQLDB 2.0, you can report it and it will be fixed promptly.