2

The following query fails in hsqldb 2.3.2 with sql.syntax_ora=true.
Works fine in Oracle 11.
It's a hibernate named query, used in a TestNG test going to in-memory hsqldb instead of the real Oracle. Error message is: java.sql.SQLSyntaxErrorException: unexpected token: ORDER required: ) : line: 16, referring to the row_number() over (order by lastUpdateTime desc) area: 

select  
        id         as id1_9_0_,  
        version    as version2_9_0_,  
        lastUpdateTime as lastUpda7_9_0_,  
        eventType    as eventTyp3_9_0_,  
        clientJid    as clientJi4_9_0_,  
        topicName as topicNam5_9_0_,  
        attributes as attribut6_9_0_  
    from (select logEvents.id as id,  
                 logEvents.version as version,  
                 logEvents.lastUpdateTime as lastUpdateTime,  
                 logEvents.eventType as eventType,  
                 logEvents.clientJid as clientJid,  
                 logEvents.topicName as topicName,  
                 logEvents.attributes as attributes,  
                 row_number() over (order by lastUpdateTime desc) rn    from xyz_logEvent logEvents)  
    where rn between 1 and 4 order by rn;
fredt
  • 24,044
  • 3
  • 40
  • 61
labrat43
  • 49
  • 1
  • 5

1 Answers1

0

Not all possible SQL syntax is supported by HSQLDB. row_number() over (order by ...) is not supported while row_number() over () is.

An alternative may look like this:

(select logEvents.id as id,
logEvents.version as version,
logEvents.lastUpdateTime as lastUpdateTime,
logEvents.eventType as eventType,
logEvents.clientJid as clientJid,
logEvents.topicName as topicName,
logEvents.attributes as attributes,
row_number() over () rn from xyz_logEvent logEvents order by lastUpdateTime limit 4)
fredt
  • 24,044
  • 3
  • 40
  • 61
  • Thanks. That helped. Although limit and offset cannot be used as setParameter on HIbernate Query (that uses the above sql-query), but the setFirstResult() and setMaxResults() APIs helped. Only unfortunate downside is some queries are thus now different for unit tests than the development code. hsqldb is still more compatible to Oracle than h2. – labrat43 Feb 27 '15 at 23:47
  • Please report any other incompatibilities to the HSQLDB project to be addressed in future versions. – fredt Mar 01 '15 at 02:52