FunctionArgumentException after hibernate upgrade to v6.6.3

Hibernate ORM
1

After upgrade from 5.4.29 to 6.6.3 I get the following exception when using the lower function on a String field which is mapped to a clob column:

criteriaQuery.where(hcb.like(hcb.lower(root.get("text")), "some text"));

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function 'lower()' has type 'STRING', but argument is of type 'java.lang.String' mapped to 'CLOB'

Why is it not possible to use the lower function?
As I use the lower function together with like, a tried to use the hibernate ilike function.

criteriaQuery.where(hcb.ilike(root.get("text"), "some text"));

This has no problems. I am using a Oracle DB so the created sql uses the lower funktion.

select
    t1_0.id,
    t1_0.text 
from
    Text t1_0 
where
    lower(t1_0.text) like lower(?)

With 7.0.0.Beta3 I do get the same failure.

2

Most databases do not allow using text functions with CLOBs and require VARCHAR like types. Hibernate ORM tries to find the balance here which might not always be perfect, but if you map a persistent attribute as @Lob, you shouldn’t try to invoke functions on it IMO.

Rather use @JdbcTypeCode(SqlTypes.LONG32VARCHAR), which might still use a CLOB DDL type behind the scenes, but will still allow using all text functions.

3

Then I still get an exception, now ‘VARCHAR’ instead of ‘CLOB’

FunctionArgumentException: Parameter 1 of function 'lower()' has type 'STRING', but argument is of type 'java.lang.String' mapped to 'VARCHAR'

But could I use the ilike function? Or will the type checking also be done for ilike function in the future?

4

This might be a bug. Would appreciate if you could create a Jira for that along with some sample code that shows the problem.

Since the like/ilike predicate is one of the few predicates that supports CLOB on almost all databases, I would say this will always work.

5

The requested Jira: Jira

1 Like