oracle语法模式下，内存hsqldb中的oracle查询失败

Question

以下查询在hsqldb 2.3.2中失败，使用sql.syntax_ora=true。
在Oracle 11中运行良好。
这是一个名为hibernate的查询，在TestNG测试中使用，该测试指向内存中的hsqldb，而不是真正的Oracle。错误消息为：java.sql.SQLSyntaxErrorException：意外令牌：需要ORDER：）：行：16，引用了row_number（）over（ORDER by lastUpdateTime desc）区域：

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;  

Answer 1

HSQLDB并不支持所有可能的SQL语法。不支持row_number() over (order by ...)，而row_number() over ()是。

另一种选择可能是这样的：

(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)