False translation from hql to sql

In my (hql) query, I have the following where-clause:

LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '')  ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')

If I issued a query "real" sql, it is "translated" as follows:

lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))

causes an error, for example: "No replace (text) function exists ..."

Why is everything ,(between functional brackets) replaced by ||?

This is the full hql request:

select
    distinct user.dbId,
    user.lastName,
    user.firstName
from
    User user  LEFT join user.permissions
where
    LOWER( f_umlautfree2( replace(user.lastName, ' ', '')||replace(user.firstName, ' ', '')  ) ) LIKE LOWER ('%'||( f_umlautfree2( replace(replace(:name, ',', ''), ' ', '') ) )||'%')
and
    (user.employee IS NOT NULL OR user.lecturer IS NOT NULL OR user.ethRelated IS NOT NULL OR user.pdbGuest IS NOT NULL)
and
    user.valid = :valid  
order by
    user.lastName, user.firstName asc

and this is the full final sql query:

select
     distinct user0_.dbId as col_0_0_,
     user0_.lastName as col_1_0_,
     user0_.firstName as col_2_0_
from
     idmanlight.NETHZ_USER user0_
left outer join
     idmanlight.NETHZ_USER_PERMISSION permission1_
         on user0_.dbId=permission1_.NETHZ_USER_dbId
left outer join
     idmanlight.PERMISSION permission2_
         on permission1_.permissions_dbId=permission2_.dbId
where
     (
         lower(idmanlight.f_umlautfree2((replace(user0_.lastName||' '||'')||replace(user0_.firstName||' '||'')))) like lower(('%'||idmanlight.f_umlautfree2(replace(replace('%Croci%'||','||'')||' '||''))||'%'))
     )
     and (
         user0_.employee is not null
         or user0_.lecturer is not null
         or user0_.ethRelated is not null
         or user0_.pdbGuest is not null
     )
     and user0_.valid=?
 order by
     user0_.lastName,
     user0_.firstName asc
+3
source share
1 answer

HQL does not support the replace function. You must register it with Dialect.registerFunction () .

( here is an example of how to do this)

+2
source

All Articles