HSQLDialect uses wrong trim() function when running HSQLDB v1.8


The default dialect behavior assumes the TRIM function has the form "TRIM(?1 ?2 ?3 ?4)", whereas HSQLDB v1.8 specifies: "TRIM([{LEADING | TRAILING | BOTH}] FROM <string expression>)" (http://www.hsqldb.org/doc/1.8/guide/ch09.html#stored-section).

Simply fixed in the HSQLDialect class constructor by overriding the function registered in the parent Dialect class:

if ( hsqldbVersion < 20 ) { // maybe == 18? not sure best practice here...
registerFunction("trim", new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(both from ?1)"));

Simple test case (assuming HSQLDialect + HSQL JDBC driver):

sessionFactory.getCurrentSession().createQuery("from User where trim(email) = 'foo@bar.com'").list();


Maven: hibernate-core-3.6.0.Final.jar, hsqldb-


Steve Ebersole
April 10, 2014, 2:59 PM

Thanks for the re-clarification Fred.

Martin, this is not even close to enough information. Your stack trace shows no Hibernate info, so I have no idea where this comes from. As I said before, this is rejected until someone can produce a test case that reproduces this problem. Heck even a simple sample query that you think will reproduce it...

Steve Ebersole
April 10, 2014, 3:01 PM

In fact, looking at the stack trace that is there... it actually looks like its your code that is preparing the SQL here, not Hibernate...

Martin Heitz
April 10, 2014, 4:31 PM

Partly right, I agree that the provided stack trace is not very helpful here, sorry.

The trim-function is built using the following methods:
public static String buildTrimStringExpression(final Connection connection, final String varName)
throws SQLException
final SQLFunction sqlFunction = getSqlFunction(connection, "trim");

return sqlFunction.render(StandardBasicTypes.STRING, Arrays.asList(varName), null);

private static SQLFunction getSqlFunction(final Connection connection, final String functionName)
throws SQLException
final Dialect dialect = resolveDialect(connection);
final SQLFunction sqlFunction = dialect.getFunctions().get(functionName);
if (sqlFunction == null) {
throw new SQLException("Failed to find '" + functionName + "' function for dialect " + dialect);

return sqlFunction;

I hope now that you believe me, that the resolveDialect-method correctly returns the HSQLDialect. In fact within this resolveDialect method I'm extending the dialect with the different template:
final Dialect dialect = standardDialectResolver.resolveDialect(metaData);
if (dialect instanceof HSQLDialect) {
// TODO (mah 14.07.2011): patch for issue https://hibernate.onjira.com/browse/HHH-6122
// HSQLDB needs the "both from" keywords in trim function
dialect.getFunctions().put("trim", HSQLDB_TRIM_TEMPLATE);

Was I able to explain the problem now?

Best regards, Mattin

Steve Ebersole
April 10, 2014, 4:46 PM

Right, but this is a error in your code. As I have said already, when Hibernate calls the TRIM SqlFunction it always passes 4 arguments. You are not. Simply fix your code, this is NOT a bug in Hibernate; it is simply your code misusing a Hibernate class.

Martin Heitz
April 11, 2014, 9:10 AM

Thanks for your work on this. As I am not the reporter of the issue and experienced the same problem I have not suspected that it could be my fault.
Sorry for causing trouble.

All the best, Mattin




Matthew Pies

Fix versions






Suitable for new contributors


Requires Release Note


Pull Request





Affects versions