[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6122) HSQLDialect uses wrong trim() function when running HSQLDB v1.8

[Fred Toussi (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6122?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=42720#action_42720 ] 

Fred Toussi commented on HHH-6122:
----------------------------------

Both 1.8.x and 2.x support TRIM( {LEADING | TRAILING | BOTH} FROM <string expression> )

Running the Hibernate tests indicates that it adds TRAILING as a string in one of the tests to remove a trailing underscore characters. This works with 2.x and should work with 1.8.x as there is no discrimination on the SQL that is generated. When the suggested patch is used, it generates TRIM ( BOTH FROM TRAILING ) and submits to the engine, which is incorrect and rejected.

In any case, a TRIM function should handle multiple parameters, not just one.

> HSQLDialect uses wrong trim() function when running HSQLDB v1.8
> ---------------------------------------------------------------
>
>                 Key: HHH-6122
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6122
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.6.0
>         Environment: Maven: hibernate-core-3.6.0.Final.jar, hsqldb-1.8.0.10.jar
>            Reporter: Matthew Pies
>
> 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 at bar.com'").list();

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira