Wrong number of parameters of TRIM function in Hibernate @Formula

Question

I'm trying to make a formula to select a user-friendly name of the user profile. It selects firstname + ' ' + lastname if at least one of them is not null and not blank (contains non-whitespace characters), otherwise it selects shortname (with the same conditions) and, finally, if shortname is blank or null, it selects id, converted to string.

@Formula("COALESCE(NULLIF(TRIM(BOTH FROM CONCAT(sp.firstname, ' ', sp.lastname)), ''), TRIM(p.shortname), to_char(p.id, 'FM9999999999999999')) " +
   "FROM socialprofile AS sp " +
   "JOIN profile AS p ON sp.id=p.id")
   public String getUserFriendlyName() {
      return super.getUserFriendlyName();
   }

The result is:

org.hibernate.HibernateException: Unexpected number of trim function operands : 10

but there is only 3 arguments: BOTH, FROM and result of CONCAT. Is there any workaround of this issue? DB is PostgreSQL 9.1, Hibernate version is 4.2.1. Just in case, here's the table structure:

Profile
  bigint id
  text shortname

SocialProfile
  bigint id
  text firstname
  text lastname

They are related as One-to-One by id field, in Java code SocialProfile extends Profile. In PgAdmin SQL editor this request works perfectly.

Answer 1

I faced this problem too. It appears that @Formula delegates to Hibernate's internal "where template" parser which doesn't expect another function nested in trim(). This issue was reported as HHH-5970 before, but they didn't do any thing with it.

The work around was straight forward: don't use trim() this way in a @Formula. Instead use e.g. ltrim(rtrim(..)) or even a DB-specific regex function which replaces "^\s+|\s+$" by "", depending on the capabilities of the DB being used.

PostgreSQL supports ltrim() and rtrim(), so this should do for you:

@Formula("COALESCE(NULLIF(LTRIM(RTRIM(BOTH FROM CONCAT(sp.firstname, ' ', sp.lastname))), '') [...]"