Custom Dialect Function with Varying Parameter Counts Fails in Hibernate 6

Hibernate ORM
1

The product I’m upgrading from Hibernate 5 to Hibernate 6 previously made use of hibernate Dialects to take a convert_tz(…) call and translate it accordingly for various DBs (MariaDB, PostgreSQL etc…).

An example of the call:
convert_tz(entity.timestamp, '+00:00', '-04:00', -240, 'UTC', 'America/Anguilla'

The initial HQL query this is within, is generated by some impenetrable code that I’d like to avoid changing at all costs.
The given custom Dialect can then take it and convert it into something friendly for the current DB, see some examples below to see how flexible we need it to be. All of the potentially required data is stored in the initial call, and it’s reorganized accordingly.

MariaDB : convert_tz(entity.timestamp, '+00:00', '-04:00')

PostgreSQL : entity.timestamp AT time zone 'UTC' AT time zone 'America/Anguilla'

The Hibernate 5 way of defining this function in a dialect is no longer supported, it does not compile. (neither of the functions seem to exist anymore)

Here’s the Hibernate 5 way we succesfully had this working with which no longer compiles as of Hibernate 6:

public class CustomMariaDBDialect5 extends MariaDBDialect{
  public CustomMariaDBDialect5 () {
    registerColumnType( Types.TIMESTAMP, "datetime" );
    super.registerFunction("convert_tz", new SQLFunctionTemplate(DateType.INSTANCE, "convert_tz(?1, ?2, ?3) "));
}

And here’s my naive attempt to upgrade this to Hibernate 6

public class CustomMariaDBDialect6 extends MariaDBDialect {
	public CustomMariaDBDialect6() {
		super();
	}

	@Override
	public void contributeFunctions(FunctionContributions functionContributions) {
		super.contributeFunctions(functionContributions);
		functionContributions.getFunctionRegistry().registerPattern("convert_tz", 
		"convert_tz(?1, ?2, ?3) ", functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.DATE));
	}

	@Override
	protected String columnType(int sqlTypeCode) {
		if(sqlTypeCode == Types.TIMESTAMP){
			return "datetime";
		}else{
			return super.columnType(sqlTypeCode);
		}
	}
}

Via debug I can see that contributeFunctions is being called, and when I run my program I get the following error:

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Function convert_tz() has 3 parameters, but 6 arguments given

My initial concern was that the dialect wasn’t being used at all (as then it would be calling directly to the MariaDB convert_tz call which indeed has 3 arguments), but Following these instructions, I registered this as a FunctionContributor, If I alter the dialect pattern by adding another parameter “convert_tz(?1, ?2, ?3, ?4)” the error then changes to say ‘4 parameters’ thus I am certain that the dialect and function are being correctly loaded.

So it seems that the new hibernate 6 way of registering functions is more strict about parameter count. Is it possible that our approach here is simply not supported anymore?

2

First of all, you don’t need to extend the Dialect to contribute functions. In fact, it is very much discouraged to do so. The way to go is to define a custom FunctionContributor which is loaded through the ServiceLoader mechanism.
In that, you can acquire the current dialect and register the functions for the respective dialect, all within one block of code, instead of sprinkling that code across different dialect subclasses.

You can override the arguments validator by registering the function like this:

functionContributions.getFunctionRegistry().patternDescriptorBuilder("convert_tz", "convert_tz(?1, ?2, ?3) ")
    .setInvariantType( functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.DATE) )
    .setArgumentsValidator(StandardArgumentsValidators.NONE)
    .register();
1 Like
3

Aha, ok thank you that’s solved that issue! However I now have a new issue which is another step beyond the original post, hope it’s ok if I continue this here?

When using MariaDB our convert_tz call is actually then wrapped in a dayname() call.

dayname(convert_tz(…))

The error I now get when performing this query is coming from it:

toResponse(): uncaught error message:
java.lang.IllegalArgumentException: org.hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 1 of function ‘dayname()’ has type ‘DATE’, but argument is of type ‘java.lang.Object’

I can only assume this is because I’m not setting the return type correctly. In Hibernate 5 we used the SQLFunctionTemplate + DateType.INSTANCE, my best guess was that the …resolve(StandardBasicTypes.DATE) was the equivalent looking thing that could fit the slot. I wasn’t able to find something in the migration guide that pertained to this, though I get an understanding that’s because this is not currently a common/encouraged use case?

4

If you register the function the way I presented to you this should work just fine. If you think this is a bug, please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.