Hi,

the problem exists with HSQLDB 1.8.0.7. I cannot provide a test case but at least an anonymized stack trace including the failing SQL:

com.xxx.commons.db.sql.DetailedSQLException: errorCode=-11, SQLState=37000, statement='SELECT something_id FROM SomeTable WHERE (xxx_id IS NULL OR trim(xxx_id)='') AND typing=? ORDER BY something_id'
at com.xxx.xxx.commons.db.manager.AbstractDBConnection.prepareStatement(AbstractDBConnection.java:503)
at com.xxx.xxx.commons.db.manager.HSQLDBConnection.prepareStatement(HSQLDBConnection.java:1)
at com.xxx.xxx.xxx.transaction.db.internal.ConnectionAndStatements.getOrCreateFreeStatement(ConnectionAndStatements.java:150)
at com.xxx.xxx.xxx.transaction.Transaction.getStatement(Transaction.java:685)
at com.xxx.xxx.xxx.server.commons.update.task.to2_3.AdjustExtendedsomethingConfigurationUpdateTask.getSomething(AdjustExtendedSomethingConfigurationUpdateTask.java:202)
at com.xxx.xxx.xxx.server.commons.update.task.to2_3.AdjustExtendedsomethingConfigurationUpdateTask.execute(AdjustExtendedSomethingConfigurationUpdateTask.java:108)
... 20 more
Caused by: java.sql.SQLException: Unexpected token in statement [SELECT something_id FROM SomeTable WHERE (xxx_id IS NULL OR trim(xxx_id)='') AND typing=? ORDER BY something_id]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at com.xxx.commons.db.sql.AbstractDebuggableStatement.<init>(AbstractDebuggableStatement.java:215)
at com.xxx.commons.db.sql.jdbc4.DebuggableStatement.<init>(DebuggableStatement.java:39)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.xxx.commons.db.sql.StatementFactory.createPreparedStatementInstance(StatementFactory.java:340)
at com.xxx.commons.db.sql.StatementFactory.prepareStatement(StatementFactory.java:272)
at com.xxx.xxx.commons.db.manager.AbstractDBConnection.prepareStatement(AbstractDBConnection.java:501)
... 25 more

Therefore the following fix in the dialect is required for me:
dialect.getFunctions().put("trim", HSQLDB_TRIM_TEMPLATE);
with
private static final SQLFunctionTemplate HSQLDB_TRIM_TEMPLATE =
new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(both from ?1)");

Best regards, Mattin