Array_contains fails on text[] field after migration to 7.4.1 (from 6.6.49)

Hibernate ORM
1

array_contains generates varchar[] cast against a PostgreSQL text[] column — regression from 6.6

Environment

  • Hibernate ORM: 7.4.1.Final (works on 6.6.49.Final)
  • Database: PostgreSQL 17.x, driver org.postgresql:postgresql
  • Dialect: PostgreSQLDialect

Summary

When an entity maps a PostgreSQL text[] column to a String[] / List<String> attribute and a query uses the array_contains function, Hibernate 7.x renders the element argument with an explicit varchar[] cast:

... @> cast(array[?] as varchar array)

Because the physical column is text[], PostgreSQL rejects this — there is no implicit conversion between text[] and varchar[]:

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

The identical mapping and query work on 6.6.49.Final; they fail on 7.4.1.Final.

Mapping

@Entity
@Table(name = "item")
public class Item {

    @Id
    @Column(name = "id")
    private Long id;

    // physical column: tags text[] NOT NULL
    @Column(name = "tags", columnDefinition = "text[]")
    private String[] tags;
}

DDL:

CREATE TABLE item (
    id   bigint PRIMARY KEY,
    tags text[] NOT NULL
);

Query

em.createQuery(
    "select count(i) from Item i where array_contains(i.tags, :tag)",
    Long.class)
  .setParameter("tag", "alpha")
  .getSingleResult();

Generated SQL

7.4.1.Final (fails):

select count(i1_0.id)
from item i1_0
where i1_0.tags @> cast(array[?] as varchar array)

→ operator does not exist: text[] @> character varying[]

6.6.49.Final (works): the same query executes successfully — no cast to varchar[] (it targets a text-compatible type), so text[] @> text[] is used and PostgreSQL accepts it.

Workaround

I have, for now used the following custom function as a workaround :

public class TextArrayContainsFunction implements FunctionContributor {
    @Override
    public void contributeFunctions(FunctionContributions fc) {
        fc.getFunctionRegistry().registerPattern(
            "text_array_contains",
            "(?1 @> array[?2]::text[])",
            fc.getTypeConfiguration().getBasicTypeRegistry()
              .resolve(StandardBasicTypes.BOOLEAN));
    }
}
2

Don’t use columnDefinition. Why do you think you need to use that in the first place?
You can also tell Hibernate ORM to use the biggest text type in a database independent way by using this mapping:

@Entity
@Table(name = "item")
public class Item {

    @Id
    @Column(name = "id")
    private Long id;

    @Array(length = 1) // Doesn't matter for PostgreSQL
    @JdbcTypeCode(SqlTypes.LONG32VARCHAR)
    @Column(name = "tags")
    private String[] tags;
}
3

You’re right column definition is not necessary, so I have removed it. However the behavior does not change.

  1. With just the @Array annotation still getting the same
ERROR: operator does not exist: text[] @> character varying[]
  1. If I add @JdbcTypeCode(SqlTypes.LONG32VARCHAR) the application fails on startup when hibernate is parsing the hql queries with

org.hibernate.query.sqm.produce.function.FunctionArgumentException: Parameter 0 of function 'array_contains()' requires an array type, but argument is of type 'java.lang.String[]'

because in org.hibernate.dialect.function.array.ArrayArgumentValidator#getPluralType the comparison else if ( arrayType instanceof BasicPluralType<?, ?> basicPluralType ) fails because the column is now of type BasicTypeImpl

4

I would appreciate if you could 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.

In the meantime, you can force this type also by specifying a long length e.g.

@Entity
@Table(name = "item")
public class Item {

    @Id
    @Column(name = "id")
    private Long id;

    @Array(length = 1) // Doesn't matter for PostgreSQL
    @Column(name = "tags", length = Length.LONG32)
    private String[] tags;
}
5

Adding length = Length.LONG32 does resolve the problem. I have created a reproducer (one for v6 which passes and identical for v7 that fails) using the recommended template; should the issue still be opened or is this the expected behavior?

6

Yes, please open a Jira ticket. It shouldn’t be necessary to specify the length like that to make array_contains work.

7

Thanks for creating HHH-20695. Please cross-post the link in the future for other users who stumble into this problem to be able to see how it went.