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?

coladict · Accepted Answer · 2018-12-06 10:18:55Z

5

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.

answered Dec 6, 2018 at 10:18

coladict

5,1151 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jodiug Over a year ago

Just leaving this here, PostgreSQL claims they implemented the JDBC spec correctly, and that the way Hibernate passes nulls to JDBC is wrong because they send a "typed null" rather than a constant null. If that is indeed what the JDBC spec says (which I have not checked) then the fault would be at Hibernate even if the trick they do works correctly for other databases.

Collectives™ on Stack Overflow

PostgreSQL, Spring Data JPA: Integer null interpreted as bytea

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related