Suboptimal specification queries for object arrays about spring-data-jpa

[quaff]

Returns the 1-based position of an element in the array. Returns 0 if the element is not found and null if the array is null.

You should use cb.ge(position, 1) instead of cb.isNotNull(position).

BTW, It's handled by Hibernate not Spring Data JPA.

[quaff]

https://docs.jboss.org/hibernate/orm/6.4/userguide/html_single/Hibernate_User_Guide.html#hql-array-position-functions

The function array_position is hql function provided by Hibernate, It will overlay native sql function.

[quaff]

Then it might be another Hibernate bug because it worked before when using user types to map to SQL arrays. In that case array_position was called directly with no adaption needed to provide the documented behavior.

I guess that works before array_position added, it's introduced since 6.4.0, see https://hibernate.atlassian.net/issues/HHH-17335

[markusheiden]

Sorry for misjudging this.

Your suggested change works. Thanks for that. So this issue might be closed.

I used cb.gt(position, 0) though.

But for PostgreSQL, the documentation says: Returns the subscript of the first occurrence of the second argument in the array, or NULL if it's not present. So the NOT NULL comparison worked for us before. Now we need to switch from user types to the "native" array support of Hibernate due to a Hibernate bug. The native array support generates different SQL for this use case.

[markusheiden]

Thanks for pointing me to the docs. Before I thought, these function calls were calls of native functions of the database.

Then it might be another Hibernate bug because it worked before when using user types to map to SQL arrays. In that case array_position was called directly with no adaption needed to provide the documented behavior.