Answer a question

I'm using Spring Boot with PostgreSQL JDBC 42.2.13 and Hibernate 5.3.18.Final, and it seems that when adding IS NULL check to a collection of UUIDs, Hibernate/PostgreSQL cannot determine the data type of the collection.

Entity

class Element {
    @Column
    private UUID uuid;
}

Repository

@Query("SELECT e FROM Element e WHERE (:uuids) IS NULL OR e.uuid IN (:uuids)")
    List<Element> findByUuidIn(@Param("uuids") Collection<UUID> uuids);

Query execution

elementRepository.findByUuidIn(List.of(UUID.randomUUID())); // works fine!
elementRepository.findByUuidIn(null); // -> Throws exception

Parameters binding from trace logging

org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [OTHER] - [null]
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [OTHER] - [null]

Exception

Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $1
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:103)
    at jdk.internal.reflect.GeneratedMethodAccessor582.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy398.executeQuery(Unknown Source)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:60)
    ... 114 common frames omitted

Temporary solution

Change the query parameter to String and cast the column type in query.

@Query("SELECT e FROM Element e WHERE (:uuids) IS NULL OR CAST(e.uuid AS string) IN (:uuids)")
    List<Element> findByUuidIn(@Param("uuids") Collection<String> uuids);

elementRepository.findByUuidIn(null); // works fine!

org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - [null]
org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [2] as [VARCHAR] - [null]

Question to the community

• Is there a way to provide a null collection of UUID as parameter and have the IS NULL check?
• Is it a bug in Hibernate that doesn't bind parameter type UUID and uses OTHER instead when using null collection as parameter?
• Is it a bug in PostgreSQL JDBC that doesn't determine the type of parameter when using OTHER type?

Answers

Hibernate has three basic types mapped to the java.util.UUID:

1. org.hibernate.type.UUIDBinaryType is mapped to binary data types (bytea)

2. org.hibernate.type.UUIDCharType is mapped to CHAR, can also read VARCHAR

3. org.hibernate.type.PostgresUUIDType is mapped to the PostgreSQL UUID, through Types#OTHER, which complies to the PostgreSQL JDBC driver definition.

A lot of PostgreSQL types mapped to the java.sql.Types.OTHER, for example org.postgresql.util.Pgobject (bit varying), org.postgresql.geometric.Pgpoint (point), org.postgresql.geometric.Pgbox (box), java.util.UUID (uuid), ... (see this). So, PostgreSQL JDBC driver could not determine data type of parameter when you pass null.

Solutions

1. You can correct your query in the following way:

@Query("SELECT e FROM Element e WHERE (:uuids) IS NULL OR CAST(e.uuid as org.hibernate.type.UUIDCharType) IN (:uuids)")
List<Element> findByUuidIn(@Param("uuids") Collection<UUID> uuids);

It will allow you to use Collection<UUID> as passed parameter type. But, actually this is similar to your workaround, because it will generate the following sql:

select
   element0_.id as id1_2_ 
from TEST_SCHEMA.ELEMENT element0_ 
where  ? is null or cast(element0_.id as varchar(255)) in (?)

2. You can change your table's column definition:

create table ELEMENT
(
   id bytea,
   ...
);

Then map this column in the following way:

import org.hibernate.annotations.Type;

@Entity
public class Element
{
   @Id
   @GeneratedValue
   @Type(type = "uuid-binary") // This is pg-uuid by default for PostgreSQL82Dialect and higher
   private UUID id;

   // ...
}

and then you will be able to query it without any casting:

Collection<UUID> uuids = null;

List<Element> elements = em.createQuery(
   "select e from Element e where (:uuids) is null or e.id in (:uuids)",
   Element.class)
.setParameter("uuids", uuids)
.getResultList();