FunctionArgumentTypeResolver for datetimefromparts doesnt work as expected

Dialect in Use: org.hibernate.dialect.SQLServerDialect

When I try to run this HQL

SELECT datetimefromparts(2012,1,1,1,1,1,1) as foo

I’m getting the Exception

Index 1 out of bounds for length 1
java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1
	at org.hibernate.query.sqm.produce.function.StandardFunctionArgumentTypeResolvers.lambda$invariant$5(StandardFunctionArgumentTypeResolvers.java:83)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction$FunctionArgumentTypeResolverTypeAccess.get(SelfRenderingSqmFunction.java:295)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction$FunctionArgumentTypeResolverTypeAccess.get(SelfRenderingSqmFunction.java:277)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.resolveInferredType(BaseSqmToSqlAstConverter.java:5519)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.getInferredValueMapping(BaseSqmToSqlAstConverter.java:6097)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitHqlNumericLiteral(BaseSqmToSqlAstConverter.java:5779)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitHqlNumericLiteral(BaseSqmToSqlAstConverter.java:454)
	at org.hibernate.query.sqm.tree.expression.SqmHqlNumericLiteral.accept(SqmHqlNumericLiteral.java:81)

It is caused because org.hibernate.query.sqm.produce.function.StandardFunctionArgumentTypeResolvers.invariant(FunctionParameterType... types):FunctionArgumentTypeResolver is trying to resolve a FunctionParameterType on Index 1 that does not exist (Note: All params for this Function are Integers). See the screenshot below for a better overview

Is this a bug or am I doing something wrong?

I’m not sure if this is the actual cause or if I’m just missing a configuration, but I believe it might be a bug, because the type parameters in org.hibernate.dialect.SQLServerDialect don’t seem to be defined correctly—for example datefromparts

        // original
		functionContributions.getFunctionRegistry().namedDescriptorBuilder( "datefromparts" )
				.setInvariantType( dateType )
				.setExactArgumentCount( 3 )
                // key difference
				.setParameterTypes(INTEGER)
				.register();

I extended the original dialect, and it seems I can let the original functions be defined first and then simply override them by calling the super function first before “overwritting” them.

public class CustomSQLServerDialect extends SQLServerDialect {

  @Override
  public void initializeFunctionRegistry(FunctionContributions functionContributions) {
    super.initializeFunctionRegistry(functionContributions);
    final TypeConfiguration typeConfiguration = functionContributions.getTypeConfiguration();
    final BasicTypeRegistry basicTypeRegistry = typeConfiguration.getBasicTypeRegistry();
    final BasicType<Date> dateType = basicTypeRegistry.resolve(StandardBasicTypes.DATE);

    functionContributions.getFunctionRegistry().namedDescriptorBuilder( "datefromparts" )
        .setInvariantType( dateType )
        .setExactArgumentCount( 3 ) 
         // the key difference
        .setParameterTypes(INTEGER, INTEGER, INTEGER)
        .register();

// ...

According to DATETIMEFROMPARTS (Transact-SQL) - SQL Server | Microsoft Learn, this function requires 7 arguments, but as far as I can see, the function registration misses the other parameter types.

Please create a bug ticket in our issue tracker for this. We’d also very much appreciate if you could create a PR for this with a test and fix.

1 Like

I’ll try to work on this in my limited spare time, but I can’t make any promises. I’m not very experienced with Hibernate, and my understanding of SQL—especially MSSQL—is fairly basic. In our case, most of the MSSQL-specific features were used via native SQL, so I only needed to address three functions: dateFromParts, dateTimeFromParts, and previously getDate(), which were (sadly not anymore) provided by SQLServerDialect by extending AbstractTransactSQLDialect but getDate() was removed in this commit….

I don’t think the fix is that hard so I’m gonna attempt trying to fix it and also add the missing getDate() function as desribed in the Microsoft T-SQL Docs.