Custom function type cast problem

Hibernate ORM
1

Hi!
Due to Spring Boot update Hibernate was updated from 6.5.2.Final to 6.6.11.Final

I’ve registered custom function:

public class ArrayContainsArraySQLFunction extends StandardSQLFunction {
    private static final BasicTypeReference<Boolean> RETURN_TYPE = new BasicTypeReference<>("boolean", Boolean.class, SqlTypes.BOOLEAN);

    public ArrayContainsArraySQLFunction(final String functionName) {
        super(functionName, true, RETURN_TYPE);
    }

    @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> sqlAstArguments, ReturnableType<?> returnType, SqlAstTranslator<?> translator) {
        sqlAstArguments.get(0).accept(translator);
        sqlAppender.append(" @> ");
        sqlAstArguments.get(1).accept(translator);
    }
}

So I used it in specification like so:

        static Specification<MailingList> byEmailList(List<String> emailList) {
            return (root, query, cb) ->
                    cb.isTrue(
                            cb.function("array_contains_all", Boolean.class,
                                    root.get(MailingList_.recipientList),
                                    cb.literal(emailList.toArray(new String[0])).as(String[].class)
                            ));
        }

In 6.5.2.Final version it worked fine, producing SQL query (PostgreSQL):

Hibernate: 
    select
        ml1_0.id,
        ml1_0.operation,
        ml1_0.recipient_list 
    from
        mailing_list ml1_0 
    where
        ml1_0.recipient_list @> cast(ARRAY['test@test.com'] as varchar array)

But in 6.6.11 it doesn’t add explicit type casting as varchar array:

Hibernate: 
    select
        ml1_0.id,
        ml1_0.operation,
        ml1_0.recipient_list 
    from
        mailing_list ml1_0 
    where
        ml1_0.recipient_list @> ARRAY['test@test.com']

And prints error:

org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying[] @> text[]
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

How to fix it?
Thanks.

2

Hello @grindnoise, Hibernate 6.6 natively supports array functions. You can use the HibernateCriteriaBuilder extension APIs and its native arrayContains method, without the need for any custom function registration.

3

Hi! Thanks, but what if I need to add explicit type cast for custom function?
Not concerning this case

4

If you need an explicit cast operation, there’s org.hibernate.query.criteria.HibernateCriteriaBuilder#cast.