NamedSqmFunctionDescriptor of return type Boolean throws semantic exception

Hibernate ORM
1

Hi,
I’m trying to add PostgreSQL jsonb_exists_any function support to hibernate 6.3.1.Final. However it seems to have issues understanding its return type is Boolean, with this error:

Cannot compare left expression of type ‘java.lang.Object’ with right expression of type ‘java.lang.Boolean’

I defined it as follows:

public class JsonbExistsAnyFunction extends NamedSqmFunctionDescriptor {
    public static final JsonbExistsAnyFunction INSTANCE = new JsonbExistsAnyFunction();
    public JsonbExistsAnyFunction() {
        super("jsonb_exists_any", true,
                StandardArgumentsValidators.min(2),
                FunctionProviderUtils.basicTypeToFunctionTypeResolver(StandardBasicTypes.BOOLEAN));
    }

    @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> sqlAstArguments, SqlAstTranslator<?> translator) {
        sqlAppender.appendSql("jsonb_exists_any(");
        translator.render(sqlAstArguments.get(0), SqlAstNodeRenderingMode.DEFAULT);
        sqlAppender.appendSql(", ARRAY[");
        var serviceIdArguments = sqlAstArguments.subList(1, sqlAstArguments.size());
        for (int i = 0; i < serviceIdArguments.size(); i++) {
            translator.render(serviceIdArguments.get(i), SqlAstNodeRenderingMode.DEFAULT);
            if (i < serviceIdArguments.size() - 1) {
                sqlAppender.appendSql(",");
            }
        }
        sqlAppender.appendSql("]");
    }
}

Defined this util to define the FuctionReturnType:

public class FunctionProviderUtils {
    public static FunctionReturnTypeResolver basicTypeToFunctionTypeResolver(BasicTypeReference<?> type) {
        return new FunctionReturnTypeResolverInternal(type);
    }

    public static class FunctionReturnTypeResolverInternal implements FunctionReturnTypeResolver {
        private BasicTypeReference<?> basicTypeReference;
        public FunctionReturnTypeResolverInternal(BasicTypeReference<?> type) {
            basicTypeReference = type;
        }

        @Override
        public String getReturnType() {
            return basicTypeReference.getName();
        }

        @Override
        public ReturnableType<?> resolveFunctionReturnType(ReturnableType<?> returnableType, List<? extends SqmTypedNode<?>> list, TypeConfiguration typeConfiguration) {
            return basicTypeReference == null ? null : typeConfiguration.getBasicTypeRegistry().resolve(basicTypeReference);
        }

        @Override
        public BasicValuedMapping resolveFunctionReturnType(Supplier<BasicValuedMapping> supplier, List<? extends SqlAstNode> list) {
            return basicTypeReference != null && supplier != null ? (BasicValuedMapping)supplier.get() : null;
        }
    }
}

And added it to an ExtendedDialect:

public class ExtendedDialect extends PostgreSQLDialect {
    public ExtendedDialect() {
        super();
    }

    @Override
    public void contributeFunctions(FunctionContributions functionContributions) {
        super.contributeFunctions(functionContributions);
        var registry = functionContributions.getFunctionRegistry();
        registry.register("jsonb_exists_any", JsonbExistsAnyFunction.INSTANCE);
    }
}

Which is then added to JPA properties like this:

properties.setProperty("hibernate.dialect", "com.example.ExtendedDialect");

And this is the repository method:

    @Query("SELECT i.id FROM Indicator i WHERE jsonb_exists_any(i.serviceIds, :serviceIds) = true")
    List<Long> findAllMatchingServiceId(@Param("serviceIds") List<String> serviceIds);

for this entity:

@Entity
@Table(name = "indicator")
public class Indicator {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    protected Long id;

    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "service_ids", nullable = false, columnDefinition = "jsonb default '[]'::jsonb")
    private List<String> serviceIds = List.of();
}

And I invoke it like this:

var matchedIds = indicatorRepository.findAllMatchingServiceId(List.of("a", "b"));
log.info("matchedIds --> {}", matchedIds);

But I get the following error upon starting the application:

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.example.repository.IndicatorRepository.findAllMatchingServiceId(java.util.List)
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:100) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:70) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:60) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:170) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:252) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:95) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.lookupQuery(QueryExecutorMethodInterceptor.java:111) ~[spring-data-commons-3.1.5.jar:3.1.5]
	... 41 common frames omitted
Caused by: java.lang.IllegalArgumentException: org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Boolean'
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:143) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:167) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:173) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:802) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:707) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:132) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
	at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:360) ~[spring-orm-6.0.11.jar:6.0.11]
	at jdk.proxy2/jdk.proxy2.$Proxy90.createQuery(Unknown Source) ~[na:na]
	at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94) ~[spring-data-jpa-3.1.5.jar:3.1.5]
	... 47 common frames omitted
Caused by: org.hibernate.query.SemanticException: Cannot compare left expression of type 'java.lang.Object' with right expression of type 'java.lang.Boolean'
	at org.hibernate.query.sqm.internal.TypecheckUtil.assertComparable(TypecheckUtil.java:338) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:48) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.sqm.tree.predicate.SqmComparisonPredicate.<init>(SqmComparisonPredicate.java:34) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.createComparisonPredicate(SemanticQueryBuilder.java:2447) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:2391) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitComparisonPredicate(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.grammars.hql.HqlParser$ComparisonPredicateContext.accept(HqlParser.java:6071) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:2243) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitWhereClause(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.grammars.hql.HqlParser$WhereClauseContext.accept(HqlParser.java:5822) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuery(SemanticQueryBuilder.java:1158) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:940) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitQuerySpecExpression(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.grammars.hql.HqlParser$QuerySpecExpressionContext.accept(HqlParser.java:1844) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:925) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSimpleQueryGroup(SemanticQueryBuilder.java:268) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.grammars.hql.HqlParser$SimpleQueryGroupContext.accept(HqlParser.java:1718) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitSelectStatement(SemanticQueryBuilder.java:442) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.visitStatement(SemanticQueryBuilder.java:401) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.SemanticQueryBuilder.buildSemanticModel(SemanticQueryBuilder.java:310) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:71) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:165) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:147) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:744) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:794) ~[hibernate-core-6.3.1.Final.jar:6.3.1.Final]
	... 56 common frames omitted

I’m not confident if I defined it correctly, as a lot of solutions I found online are deprecated now.

Many thanks in advance.

2

Did you debug your FunctionReturnType resolver yet? I suppose it’s not working correctly. You can use the StandardFunctionReturnTypeResolvers, but you’d have to pass along the TypeConfiguration to the constructor of your function to resolve the BasicType eagerly.
Apart from this, you should be able to simplify your HQL to ... WHERE jsonb_exists_any(i.serviceIds, :serviceIds) i.e. without the =true.
You also shouldn’t extend a dialect anymore but rather provide a FunctionContributor to register custom functions.

3

Thanks @beikov ,
I cannot debug the return type resolver, because the control doesn’t reach there yet, before it crashes. I wrote this based on what I could find from reading hibernate-core source code. Every other thing I found online were either deprecated now, or did not work.

Can you point me to a working code sample corresponding to your answer regarding StandardFunctionReturnTypeResolvers, or any other solution that may work?

Also can you point me to a working example code of how to define and register a FunctionContributor?

Many thanks in advance

4

Take a look at the hibernate-vector module for example: https://github.com/hibernate/hibernate-orm/blob/main/hibernate-vector/src/main/java/org/hibernate/vector/PGVectorFunctionContributor.java

5

Following the examples in the link I tried 2 solutions, each failed in a different way.

One, I used the patternDescriptorBuilder as follows:

functionRegistry.patternDescriptorBuilder( "jsonb_exists_any", "(?1 ?| ARRAY[ ?2 ])" )
        .setArgumentsValidator(StandardArgumentsValidators.exactly( 2 ))
        .setReturnTypeResolver( StandardFunctionReturnTypeResolvers.invariant( booleanType ) )
        .register();

But I guess it gets confused by the ?| operator and gives error

Unable to build Hibernate SessionFactory; nested exception is java.lang.NumberFormatException: For input string: “”

Second I tried the following NamedSqmFunctionDescriptor:

public class JsonbExistsAnyFunction extends NamedSqmFunctionDescriptor {
    public JsonbExistsAnyFunction(FunctionReturnTypeResolver functionReturnTypeResolver) {
        super("jsonb_exists_any", true,
                StandardArgumentsValidators.min(2),
                functionReturnTypeResolver);
    }

    @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> sqlAstArguments, SqlAstTranslator<?> translator) {
        sqlAppender.appendSql("(");
        translator.render(sqlAstArguments.get(0), SqlAstNodeRenderingMode.DEFAULT);
        sqlAppender.appendSql(" ?| ARRAY[");
        for (int i = 1; i < sqlAstArguments.size(); i++) {
            translator.render(sqlAstArguments.get(i), SqlAstNodeRenderingMode.DEFAULT);
            if (i < sqlAstArguments.size() - 1) {
                sqlAppender.appendSql(",");
            }
        }
        sqlAppender.appendSql("])");
    }
}

Registered in the FunctionContributor like this:

final BasicType<Boolean> booleanType = basicTypeRegistry.resolve( StandardBasicTypes.BOOLEAN );
functionRegistry.register("jsonb_exists_any", new JsonbExistsAnyFunction(StandardFunctionReturnTypeResolvers.invariant( booleanType )));

When called with a List of 2 elements, it produces the query correctly:

[select i1_0.id from indicator i1_0 where (i1_0.service_ids ?| ARRAY[(?,?)])=true]

But produces this error at runtime:

org.hibernate.exception.DataException: JDBC exception executing SQL [select i1_0.id from indicator i1_0 where (i1_0.service_ids ?| ARRAY[(?,?)])=true] [No value specified for parameter 3.]

When I call it with a list of 1 element like this:

var matchedIds = indicatorRepository.findAllMatchingServiceId(List.of("a"));

It argues about the second parameter:

JDBC exception executing SQL [select i1_0.id from indicator i1_0 where (i1_0.service_ids ?| ARRAY[(?)])=true] [No value specified for parameter 2.]
6

Looks like the JDBC driver does not understand that ?| is an operator and not a JDBC parameter marker. According to my quick google search, you can escape that by placing another ? in front. See java - How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC - Stack Overflow

7

Unfortunately escaping ? neither as ?? nor with \\? solved the problem.

But I replaced the ?| operator to the equivalent Postgresql function, like this:

functionRegistry.patternDescriptorBuilder( "jsonb_exists_any", "jsonb_exists_any(?1, ARRAY[ ?2 ])" )
        .setArgumentsValidator(StandardArgumentsValidators.exactly( 2 ))
        .setReturnTypeResolver(StandardFunctionReturnTypeResolvers.invariant(booleanType))
        .register();

Which now gives me this error:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select i1_0.id from indicator i1_0 where jsonb_exists_any(i1_0.service_ids, ARRAY[ (?,?) ])=true] [ERROR: function jsonb_exists_any(jsonb, record[]) does not exist

The problem is second argument must be text[] but hibernate generates record[] when replacing ?2, and it is caused by the parentheses. Is there a way I can change the generated query
from ... ARRAY[ (?,?) ]
to ... ARRAY[ ?,? ]
That is with parentheses removed, when ?2 is replaced?
It seems that hibernate generates parentheses when a list is passed to the repository function as an argument.

I also tried type-casting it like this (... ARRAY[ (?,?) ]::text[]), but even though it becomes syntactically correct, the query doesn’t yield the correct result at runtime.

8

You will have to unwrap the SqlTuple argument in your render implementation and render the individual parameters/components directly if you want to do that.