PostgreSQL, Spring Data JPA: Integer null interpreted as bytea

Question

In PostgreSQL I have the table

CREATE TABLE public.my_table
(
    id integer NOT NULL,
    ...

I want to perform the query: Show me the rows with a given id. If id is null, show me all rows.

I tried it with

public interface MyRepository extends JpaRepository<MyTable, Integer> {

    @Query(value = "SELECT * FROM my_table WHERE (?1 IS NULL OR id = ?1)", nativeQuery = true)
    List<MyTable> findAll(Integer id);

If id != null, everything is fine. But if id == null, I will receive the error

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) ~[spring-orm-4.3.13.RELEASE.jar:4.3.13.RELEASE]
...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) ~[hibernate-core-5.0.12.Final.jar:5.0.12.Final]
...
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = bytea
  Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440) ~[postgresql-42.2.5.jar:42.2.5]
...

Obviously short circuit evaluation does not work and null is transformed into bytea.

As a workaround I have changed the query value into

SELECT * FROM my_table WHERE (?1 IS NULL OR id = (CAST (CAST(?1 AS character varying) AS integer)))

But this is not nice, because the int is cast to string and to int again. Do you have a better solution, e.g. a better cast or sql query?

Answer 1

Another workaround for this is to create the query manually from the EntityManager (em in the example) and call setParameter on it once with a non-null value, then again with the real value.

private static final Integer exampleInt = 1;

List<MyTable> findAll(Integer id) {
    return em.createNativeQuery("SELECT * FROM my_table WHERE (:id IS NULL OR id = :id)", MyTable.class)
            .setParameter("id", exampleInt)
            .setParameter("id", id)
            .resultList();
}

This ensures that Hibernate knows the type of value the next second time it's called, even if it's null.

The fault is in the PostgreSQL server, and not in Hibernate, but they have refused to fix it, because it works as intended. You just have a few hundred types of SQL NULL on the server and they're mostly incompatible with each-other, even though it's supposed to be one singular special value.