ArrayType causes org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column found (Types#ARRAY), but expecting (Types#OTHER) with Hibernate 6.3 and 6.4

[CorayThan]

Describe the bug
When I upgrade from

hypersistence-utils-hibernate-62:3.7.1
to
hypersistence-utils-hibernate-63:3.7.1

The following code:

@Type(
     value = ListArrayType::class,
     parameters = [Parameter(
         value = "house",
         name = ListArrayType.SQL_ARRAY_TYPE,
     )]
 )
 @Column(columnDefinition = "house[]")
 val houses: List<House>,

Throws this error:

org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [houses] in table [dok_card]; found [_house (Types#ARRAY)], but expecting [house[] (Types#OTHER)]

My hibernate version is 6.4.1.Final.

To Reproduce

Follow the instructions here: https://vladmihalcea.com/postgresql-array-java-list/ with the latest version of hibernate and hypersistence-utils.

Expected behavior
No error is thrown when using a postgresql array of enums with a java list.

[vladmihalcea]

The validation will no longer work since without using Types.OTHER, the ArrayTypes will not work at all. So, either you disable the schema validation or you switch to the Hibernate native ARRAY Type. There's no way at the moment to have both options.

If HHH-17588 will be fixed, then we can switch back to Types.ARRAY. In the meantime, you can try to migrate to the Hibernate native ARRAYs, as the native Types should be more stable since they are tested by the Hibernate ORM prior to any release.

[flozano]

Just as a reference: I got affected by the same when upgrading from 3.7.0 to 3.7.2 with Hibernate 6.4 (which I think wasn't fully supported before :)

[vladmihalcea]

This issue is a side effect of #698. However, without it, you wouldn't be able to use the ArrayTypes from this library at all with Hibernate 6.4.

Check out my previous message for more details about it.

[flozano]

Yes... I've tried moving to native array, but then when I trigger an update my enums try to get mapped to positional enum.
[ERROR: column "roles" is of type company_role[] but expression is of type smallint[]

[vladmihalcea]

You should open a Hibernate issue then. In the long run, it's better to have the native Type support everything this library has been supporting.

[GuiF007]

@vladmihalcea when you say "native Type support", it's implementation of UserType<String[]> for example ? (for a array of string)

Do you confirm your fix make it works better with Hibernate 6.4+ and only validation fail ?

[vladmihalcea]

Native Type support is what Hibernate ORM offers by default without the need for custom UserType implementations.

The tests in the 63 module are running fine on Hibernate 6.4. That's the only thing I can confirm. If there are other issues, they will have to be replicated with additional integration tests.

If your project has integration tests for the entire data access logic, then you will know for sure whether a native Type or a custom one works or not. That's thr only confirmation that really matters.

[GuiF007]

Sure ! Thank you for fast answering ! Just wanted to know there is no known regression than the validation.
We will check to update to native support in any case.

[vladmihalcea]

The only known regressions are the ones for which there are Open issues on GitHub. There might be unknown regressions, of course.

[kth13]

Hi @vladmihalcea , I am facing the similar issue The code we have

@Type(ListArrayType::class) @Column(name = "disabilities", columnDefinition = "text[]") var disabilities: List<String>? = null,

What is your suggestion to change this using Hibernate native support

[vladmihalcea]

@kth13 Remove the @Type annotation and see if it works.

[kth13]

@vladmihalcea It worked, thanks a lot.

[vladmihalcea]

Great. The native Hibernate Type should be fine in many situations.

[Javasick]

How to implement array of SQL enum in 6.4?

    @Type(
        value = EnumArrayType.class,
        parameters = @Parameter(
            name = AbstractArrayType.SQL_ARRAY_TYPE,
            value = "status"
        )
    )
    @Column(name = "statuses", columnDefinition = "status[]")
    private Status[] statuses;

Native Type support cause the following error on saving:
column "statuses" is of type status[] but expression is of type smallint[]

[vladmihalcea]

@Javasick There is a PostgreSQLEnumArrayTypeTest that shows you how to use the EnumArrayType with Hibernate 6.4.

[hahosseini]

@Javasick There is a PostgreSQLEnumArrayTypeTest that shows you how to use the EnumArrayType with Hibernate 6.4.

how about hibernate 6.5 ? This solution does not work with hibernate 6.5

[vladmihalcea]

It works just fine with Hibernate 6.5. The 63 module is built with Hibernate 6.5.

https://github.com/vladmihalcea/hypersistence-utils/blob/master/hypersistence-utils-hibernate-63/pom.xml#L207

[hahosseini]

It works just fine with Hibernate 6.5. The 63 module is built with Hibernate 6.5.

https://github.com/vladmihalcea/hypersistence-utils/blob/master/hypersistence-utils-hibernate-63/pom.xml#L207

for this definition :

@Type(value = EnumArrayType.class,
 parameters = @Parameter(name = AbstractArrayType.SQL_ARRAY_TYPE, value = “tag_enum”))
@Column(nullable = false, columnDefinition = "tag_enum[]")
Tag[] tags;

hibernate 6.5 and 63 module (version 3.8.2) I'm getting :
Schema-validation: wrong column type encountered in column [tags] in table ; found [__tag_enum (Types#ARRAY)], but expecting [tag_enum[] (Types#OTHER)]

[vladmihalcea]

@hahosseini Well, you said that the PostgreSQLEnumArrayTypeTest solution doesn't work, and it does work.

Now, it's obvious that you are getting that wrong column type warning since this issue is still open, right?

The only way to get rid of that warning is if you fix HHH-17588 in Hibernate. Be the change you want to see in this world!

[soufianefhiyil]

@vladmihalcea i still have the same issue => Schema-validation: wrong column type encountered in column [valeurs] in table [champs_composant_specifiques]; found [_text (Types#ARRAY)], but expecting [text[] (Types#OTHER)]

@type(value = StringArrayType.class)
@column(name = VALEURS, columnDefinition = "text[]")
private String[] valeurs;

CREATE TABLE champs_composant_specifiques (
id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR NOT NULL,
valeurs TEXT[],
composant_id INTEGER REFERENCES composant (id) ON DELETE CASCADE
);