Hibernate-Core 3.3.2.GA
Hibernate-EntityManager 3.4.0.GA
In HSQLDialect getSequenceNextValString is incompatible with getLimitString when used with QueryImpl getSingleResult.
Code:
-- HSQLDialect code snippets
public String getSequenceNextValString(String sequenceName) {
return "call next value for " + sequenceName;
}
public String getLimitString(String sql, boolean hasOffset) {
return new StringBuffer( sql.length() + 10 )
.append( sql )
.insert( sql.toLowerCase().indexOf( "select" ) + 6, hasOffset ? " limit ? ?" : " top ?" )
.toString();
}
The reason being that getNextValString changed (a while ago) from using a "select" to using a "call". The method getLimitString(String, boolean) assumes the word "select" is in the string. When it is not found, the String returned is something like "call top? next value for ..." which is not valid SQL command.
The best way I found to work around this is to use query.getResultList() instead of query.getSingleResult() and get the first element of the List.