Oracles ROW_NUMBER () OVER (ODER BY ...) in HSQLDB

Question

The project I'm working on has an Oracle 11 database which is accessed like

SELECT *
    FROM (
    SELECT row_number() over (ORDER BY acme_dept.LOG_TABLE.LOG_TIME ASC) rn,
        acme_dept.LOG_TABLE.LOG_TIME,
        acme_dept.LOG_TABLE.LOG_LEVEL,
        acme_dept.LOG_TABLE.MESSAGE,
        acme_dept.LOG_TABLE.CATEGORY,
    FROM acme_dept.LOG_TABLE
    )
    WHERE rn BETWEEN #{first_row} AND #{last_row}
    ORDER BY rn

That is the pattern used to paginate requests efficiently. This works perfectly fine in production.

Now I want introduce HSQLDB for unit tests. (Other in-memory DBMS would do as well. See below.) However, even when I create the schema with Oracle syntax compatibility mode SET DATABASE SQL SYNTAX ORA TRUE;, I get the following error message:

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: unexpected token: ORDER required: ) : line: 4 in statement [SELECT *
    FROM (
    SELECT row_number() over (
        ORDER BY acme_dept.LOG_TABLE.LOG_TIME ASC) rn,
        acme_dept.LOG_TABLE.LOG_TIME,
        acme_dept.LOG_TABLE.LOG_LEVEL,
        acme_dept.LOG_TABLE.MESSAGE,
        acme_dept.LOG_TABLE.CATEGORY,
    FROM acme_dept.LOG_TABLE
    )
    WHERE rn BETWEEN #{first_row} AND #{last_row}
    ORDER BY rn

//// stacktrace ommited ////

Is this some issue with HSQLDB? Just not implemented maybe.
Is there anything I can do about it?
Do any alternatives to HSQLDB have better Oracle SQL support?
Is there another pagination pattern, that is efficient in Oracle -- not only the LOG_TABLE got large over time -- and available in HSQLDB?
Or have I missed some point?

---

Update: Application is running on Oracle 11, unfortunately.

Answer 1

If you are using Oracle 12 you can use the ANSI SQL fetch first x rows only which works with HSQLDB as well:

SELECT acme_dept.LOG_TABLE.LOG_TIME,
       acme_dept.LOG_TABLE.LOG_LEVEL,
       acme_dept.LOG_TABLE.MESSAGE,
       acme_dept.LOG_TABLE.CATEGORY,
FROM acme_dept.LOG_TABLE
ORDER BY acme_dept.LOG_TABLE.LOG_TIME ASC
OFFSET #{first_row}
FETCH FIRST #{num_rows} ROWS ONLY 

There is a difference to your existing code: you need to specify the number of rows to be fetch per page, not the absolute row number for the last row (that's why I changed #{last_row} to #{num_rows} in my example)

---

^{But in general I think it's a bad idea to use a different DBMS for testing then in production.There are too many subtle differences that make the tests futile in my opinion}