Using DECODE in Hibernate 6.4.4

Hibernate ORM
1

Hi everyone.

We’ve been working with Hibernate for a lot of years. Recently we upgraded our product to Hibernate 6.2.13.Final, fixed all the queries and the product works great.

Yesterday I upgraded to Wildfly 31.0.1.Final for testing, and thus, upgraded to Hibernate 6.4.4.Final. Now I am receiving a lot of errors due to the DECODE function similar to these:

Cannot compare left expression of type ‘java.lang.Long’ with right expression of type ‘java.lang.Object’
Cannot compare left expression of type ‘java.lang.String’ with right expression of type ‘java.lang.Object’
Operand of ‘like’ is of type ‘java.lang.Object’ which is not a string (its JDBC type code is not string-like)

Our queries are like:

SELECT ds FROM DiagnosisSystemImpl ds WHERE ds.key = DECODE(:key, NULL, ds.key, :key)

SELECT COUNT(ffd) FROM FormFieldDefImpl ffd WHERE DECODE( :description, '', '1', ffd.description ) LIKE DECODE( :description, '', '1', CONCAT('%',CONCAT(:description,'%'))) AND (facility.sid = :facility_sid OR ffd.facility is NULL)

Most of these queries were done in this way to make namedQueries instead of dynamic queries. I guess there could be another way to do that, but we have many DECODEs in our product and changing them would take quite some time.

We are using our own Oracle and SQL Server dialects, extending the existing ones. I’ve tried both not declaring the function DECODE, or declaring it as follows:

functionRegistry.register("decode", new SqlServerDecodeFunction());

Where SqlServerDecodeFunction is declared as:

public class SqlServerDecodeFunction extends AbstractSqmSelfRenderingFunctionDescriptor {

	public SqlServerDecodeFunction() {
		super("decode", null, null, null);
	}

I guess I should indicate a FunctionReturnTypeResolver, but DECODE can return an String, Long or Date depending on the data it receives.

This was working on 6.2.13. Any ideas on how to migrate the DECODE function to 6.4.4.?

Thanks and kind regards.
Alex

2

Hello @Faliorn, since Hibernate 6 we introduced early comparison types validation to prevent unnecessary round-trips to the database when we know the expression types being compare are not compatible. That is the reason you started getting this error.

I guess I should indicate a FunctionReturnTypeResolver, but DECODE can return an String, Long or Date depending on the data it receives

That’s exactly right, you should define a FunctionReturnTypeResolver. I suggest using Hibernate’s built in org.hibernate.query.sqm.produce.function.StandardFunctionArgumentTypeResolvers#argumentsOrImplied, with the indices 2 and 3 - the function expression will inherit the types of the result and default value arguments (in the third and the fourth position) of the DECODE function.

Let us know if that works or if you need any more help with your custom function.

3

Great! Thanks for your quick response. This worked like a charm:

	public DecodeFunction() {
		super("decode", null, StandardFunctionReturnTypeResolvers.useArgType(1),
				StandardFunctionArgumentTypeResolvers.argumentsOrImplied(3, 4));
	}

Kind regards!
Alex