Using PostgreSQL's = ANY(array) syntax with Hibernate 6.2.9

lw-mcno · October 26, 2023, 10:49am

We’ve successfully used the syntax ‘WHERE attribute = function(‘any’, :some_array)’ syntax in Hibernate 5 (while passing collections as custom array types from this library GitHub - vladmihalcea/hypersistence-utils: The Hypersistence Utils library (previously known as Hibernate Types) gives you Spring and Hibernate utilities that can help you get the most out of your data access layer., e.g. StringArrayType).

Seems, that Hibernate 6.2.9 (coming with the current Quarkus), contains additional validation and doesn’t allow to issue such a query at all:

Caused by: org.hibernate.query.sqm.InterpretationException: Error interpreting query [DELETE FROM XXX WHERE name = ?1 AND id = function('any', ?2)]; this may indicate a semantic (user query) problem or a bug in the parser [DELETE FROM XXX WHERE name = ?1 AND id = function('any', ?2)]
	at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:97)
	...
Caused by: java.lang.IllegalArgumentException: Can't compare test expression of type [BasicSqmPathSource(id : String)] with element of type [basicType@21(java.lang.Boolean,16)]
	at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.assertComparable(SqmCriteriaNodeBuilder.java:2102)
	...

(in the entity, the “id” is a String).

Same happens when the Criteria API is used instead of JPQL.

What would be the best practice here?

We’re using =ANY (instead of IN) and passing the matching collection of elements as a single array parameter for two reasons:

to avoid the limit of query parameters (max 32767 bind-params in the PG JDBC driver, AFAIK)
performance vs (very long) boolean expressions that PostgreSQL generates for ‘plain’ INs

BTW. does Hibernate 6 JPQL / criteria API have a way to express this kind native query:

SELECT * FROM my_table
WHERE (attr_1, attr_2) IN (SELECT * FROM unnest(:attr_1_array, :attr_2_array))

– it was SELECTing * FROM a function (syntax required by PostgreSQL) I wasn’t able to express. Again, the arguments are arrays for the same reasons as above.

beikov · October 27, 2023, 8:22am

If the attribute by which you want to filter by is the primary key or a natural id, then you can just use the Hibernate multiLoad API and it will use that SQL syntax behind the scenes automatically for PostgreSQL.

Out of the box support for array functions/predicates just landed in Hibernate ORM 6.4, so if you can/want to upgrade to that, you’ll be able to make use of where array_contains(arrayAttribute, :arrayParam).

In the meantime, you can create a custom SqmFunction for this purpose and register it via a FunctionContributor. You can render any SQL you want in such a custom function implementation and use it from HQL like a regular function e.g. where array_any(arrayAttribute, :arrayParam).

lw-mcno · October 27, 2023, 9:58am

Thank you. I was able to successfully register the following function:

    public void contributeFunctions(FunctionContributions functionContributions) {
        BasicType<Boolean> resultType = functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN);
        functionContributions.getFunctionRegistry().registerPattern("array_any", "?1 = ANY(?2)", resultType);
    }

and use it from JPQL with an array argument:

        delete(MyEntity_.UNAME + " = ?1 AND array_any(" + MyEntity_.ID + ", ?2)",
                username,
                new TypedParameterValue<>(StringArrayType.INSTANCE, ids.toArray(String[]::new)));

Unfortunately, it doesn’t work with CriteriaQuery:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaDelete<MyEntity> delete = cb.createCriteriaDelete(MyEntity.class);
        Root<MyEntity> myEntity = delete.from(MyEntity.class);
        Predicate unamePredicate = cb.equal(myEntity.get(MyEntity_.uname), username);
        Expression<Boolean> arrayAny = cb.function("array_any", Boolean.class, cb.literal(MyEntity_.ID), cb.literal(new TypedParameterValue<>(StringArrayType.INSTANCE, ids.toArray(String[]::new))));
        delete.where(cb.and(unamePredicate, arrayAny));
        em.createQuery(delete).executeUpdate();

resulting in:

java.lang.NullPointerException: Cannot invoke "org.hibernate.metamodel.model.domain.internal.BasicSqmPathSource.getSqmPathType()" because the return value of "org.hibernate.query.sqm.tree.expression.SqmLiteral.getNodeType()" is null

	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitLiteral(BaseSqmToSqlAstConverter.java:5445)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitLiteral(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.expression.SqmLiteral.accept(SqmLiteral.java:65)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWithInferredType(BaseSqmToSqlAstConverter.java:6800)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.resolveSqlAstArguments(SelfRenderingSqmFunction.java:132)
	at org.hibernate.query.sqm.function.SelfRenderingSqmFunction.convertToSqlAst(SelfRenderingSqmFunction.java:144)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:6018)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitFunction(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.expression.SqmFunction.accept(SqmFunction.java:66)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitBooleanExpressionPredicate(BaseSqmToSqlAstConverter.java:7730)
	at org.hibernate.query.sqm.tree.predicate.SqmBooleanExpressionPredicate.accept(SqmBooleanExpressionPredicate.java:71)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:6967)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitJunctionPredicate(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.predicate.SqmJunctionPredicate.accept(SqmJunctionPredicate.java:74)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2478)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(BaseSqmToSqlAstConverter.java:1121)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitDeleteStatement(BaseSqmToSqlAstConverter.java:435)
	at org.hibernate.query.sqm.tree.delete.SqmDeleteStatement.accept(SqmDeleteStatement.java:94)
	at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:771)
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.createDeleteTranslator(SimpleDeleteQueryPlan.java:86)
	at org.hibernate.query.sqm.internal.SimpleDeleteQueryPlan.executeUpdate(SimpleDeleteQueryPlan.java:105)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.doExecuteUpdate(QuerySqmImpl.java:735)
	at org.hibernate.query.sqm.internal.QuerySqmImpl.executeUpdate(QuerySqmImpl.java:705)

It fails while trying to inspect the array literal:

Its nodeType and javaType are null.

lw-mcno · October 27, 2023, 11:26am

Looks like the usage of custom array types is unnecessary now and Hibernate 6 will actually pass java arrays as SQL arrays without further steps. Are there any caveats here?

This simpler predicate just works:

Expression<Boolean> arrayAny = cb.function("array_any", Boolean.class, myEntity.get(MyEntity_.id), cb.literal(ids.toArray(String[]::new)));

(also: the compared column reference is not a literal)

Leaving the previous quesion in the thread, so that others with the same problem can find it.

beikov · October 27, 2023, 2:38pm

No caveats, it should just work ideally.

lw-mcno · October 27, 2023, 11:58pm

one more question: in a function contributor, what would be the correct invariant / return type for a function, that turns array(s) into a set of rows? (PostgreSQL’s select * from unnest(…) with one or more arrays).
The types of columns are not fixed in this case.

As of now, I’ve tried with StandardBasicTypes.OBJECT_TYPE (which definitely doesn’t describe reality), and though it works with the use case column IN (my_custom_function(?1), it doesnt’ work when trying to compare multiple columns, e.g. (column1, column2) in (my_custom_function(?1, ?2), resulting in:

java.lang.ClassCastException: class org.hibernate.type.JavaObjectType cannot be cast to class org.hibernate.metamodel.mapping.EmbeddableMappingType (org.hibernate.type.JavaObjectType and org.hibernate.metamodel.mapping

beikov · October 30, 2023, 10:23am

You can’t model this. You rather have to model a function that returns a boolean and you render a predicate into SQL.

Pat · February 7, 2024, 5:00pm

Hi all,
we are using ORM 6.4.1 now. But how can use array_contains function?
Can you give me an example?

Thanks in advance
Regards
Pat

beikov · February 7, 2024, 6:05pm

Look into the documentation: Hibernate ORM User Guide

Topic		Replies	Views
Postgres createArrayOf for batch queries Hibernate ORM	2	896	May 30, 2018
Hibernate 6.6 with array of enum Hibernate ORM	16	103	November 28, 2024
Postgres ANY query with an array of bind Variables Hibernate ORM	1	1503	September 6, 2021
Error with @Query in hibernate 6.2.2 Hibernate ORM	9	2784	September 12, 2023
Parameter 0 of function 'array_contains()' requires an array type, but argument is of type 'java.lang.String[]' Hibernate ORM	4	598	May 14, 2024

Using PostgreSQL's = ANY(array) syntax with Hibernate 6.2.9

Related topics