HQL coalesce() to native query changed with Hibernate 6

Hibernate ORM
1

We have recently migrated from Hibernate 5 to 6. We are facing a problem with the Oracle database. We have used the HSQL coalesce() function which takes in three columns and is expected to return the first non-null column value. With Hibernate 5 the coalesce() function with three parameters is converted into two nested nvl() functions in the native oracle query. But after migrating to Hibernate 6 it is converted to the Oracle native coalesce() function. The problem is that the Oracle native coalesce() function expects all the columns provided in the coalesce() function to be of the same data type. In our case, two out of three are varchar2 and one is clob. This ends up in an error ORA-00932: inconsistent datatypes: expected CLOB got CHAR.
My question is that the behavioral change in the query generation is not mentioned in the documentation or migration guide. Is there a possibility to provide some configuration to hibernate to generate the query similar to how was doing with Hibernate 5?

I am providing the select fragment of the HQL and its corresponding native query segment in both Hibernate 5 and 6.

HQL

coalesce(cast(taped.attributeValue as string), cast(taped_def.attributeValue as string), tpe.description)

Here description column is a clob column.

Hibernate 5

nvl(cast(ataskattri10_.attribute_value as varchar2(255 char)),
	nvl(cast(ataskattri11_.attribute_value as varchar2(255 char)),
	ataskpersi0_.description))

Hibernate 6

COALESCE(CAST(a9_0.attribute_value AS varchar2(4000 char)), CAST(a10_0.attribute_value AS varchar2(4000 char)), a1_0.description)
2

As far as I understand, Oracle implicitly casts the second argument to the type of the first argument with nvl(), whereas coalesce() simply expects that the types are the same. Hibernate also expects that the types are the same, but can only go so far with validation.

To solve your problem, you will have to also cast the clob to a String as that is what nvl would have done behind the scenes anyway.

3

Thanks for the reply. This is exactly what we are doing now. But as our project is in a critical state thought of checking whether there is an option to stay with the older behavior of native query generation as with hibernate 5.