Query resulting into "ERROR: operator does not exist: character varying ~~ bytea"

I have a query where I need to check first if the input parameter is null or compare the column value to pass input parameter. It means that the column value can be null or pass the specified condition (?3 is null or cd.name like %?3%).

public interface PageableCategoryRepository extends PagingAndSortingRepository<Category, Long> {
  @Query(
      value = "select distinct c from Category c left join fetch c.descriptions cd join fetch cd.language cdl join fetch c.merchantStore cm"
          + "  where cm.id=?1 and cdl.id=?2 and (?3 is null or cd.name like %?3%) order by c.lineage, c.sortOrder asc",
      countQuery = "select  count(c) from Category c join c.descriptions cd join c.merchantStore cm "
          + "where cm.id=?1 and cd.language.id=?2 and (?3 is null or cd.name like %?3%)")
  Page<Category> listByStore(Integer storeId, Integer languageId, String name, Pageable pageable);
}

The above query is failing for the null value passed in name property.
Error:

> ERROR: operator does not exist: character varying ~~ bytea
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 3259

I tried to search on google as well as here on Stack Overflow. There are many similar questions asked & answered. But none of those solutions work for me.

Would really appreciate if anybody can provide some insight or direction.

Note: Spring boot version- 2.2.7.RELEASE, Postgresql library version used- 42.2.16, Postgresql version used- 12.4

Postgres cannot determine the type of the parameter if it is null.

The problem has been discussed here: https://stackoverflow.com/questions/35571624/spring-data-rest-date-is-null-query-throws-an-postgres-exception

The suggested solutions were to explicitly cast the parameter (like also suggested in the error message), or to wrap the parameter in a coalesce statement.
So this should to the trick:
Replace all of these:

?3 is null 

by this statement:

coalesce(?3, null) is null

When it comes to queries where the parameters vary, it is also a good idea to have a look at the Criteria API instead of using @Query as it allows to create queries very dynamically:
https://www.baeldung.com/hibernate-criteria-queries

It seems that using named parameters instead of anonymous parameters make it works.

In my case, this was not working :

@Query("""
	SELECT p FROM Participant p
	WHERE (?1 IS NULL OR p.firstName LIKE ?1)
	AND ?2 IS NULL OR e.id = ?2
	AND p.waitingList = ?3
	""")
List<Participant> findFiltered(String searchCriteria, Long eventId, boolean waitingList);

> 2021-07-05 10:13:39.768 WARN 28896 --- [ XNIO-1 task-3]
> o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 0, SQLState:
> 42883 2021-07-05 10:13:39.768 ERROR 28896 --- [ XNIO-1 task-3]
> o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: operator does not exist: text ~~ bytea Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. Position : 951

But using named parameters, it works :

@Query("""
	SELECT p FROM Participant p
	WHERE (:searchCriteria IS NULL OR p.firstName LIKE :searchCriteria)
	AND :eventId IS NULL OR e.id = :eventId
	AND p.waitingList = :waitingList
	""")
List<Participant> findFiltered(@Param("searchCriteria") String searchCriteria, @Param("eventId") Long eventId, @Param("waitingList") boolean waitingList);

Else, as stated by the error message, explicit cast also works fine :

@Query("""
	SELECT p FROM Participant p
	WHERE (cast(?1 as text) IS NULL OR p.firstName LIKE cast(?1 as text))
	AND cast(?2 as long) IS NULL OR e.id = cast(?2 as long)
	AND p.waitingList = ?3
	""")
List<Participant> findFiltered(String searchCriteria, Long eventId, boolean waitingList);

For available cast types refer to https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#basic-provided

I use PostgreSQL 13.

If you have the potential for null values being used in a native query, then you have to use the JPA interfaces directly, instead of having Spring call them for you. Instead of:

  @Query(
      value = "select distinct c from Category c left join fetch c.descriptions cd join fetch cd.language cdl join fetch c.merchantStore cm"
          + "  where cm.id=?1 and cdl.id=?2 and (?3 is null or cd.name like %?3%) order by c.lineage, c.sortOrder asc",
      countQuery = "select  count(c) from Category c join c.descriptions cd join c.merchantStore cm "
          + "where cm.id=?1 and cd.language.id=?2 and (?3 is null or cd.name like %?3%)")
  Page<Category> listByStore(Integer storeId, Integer languageId, String name, Pageable pageable);

you need:

Page<Category> listByStore(Integer storeId, Integer languageId, String name, Pageable pageable) {
    EntityManager em = ...get from somewhere (maybe parameter?);
    TypedQuery<Category> q = (TypedQuery<Category>) em.createNativeQuery(..., Category.class);
    Integer exampleInt = 0;
    String exampleString = "";
    q.setParameter(1, exampleInt).setParameter(1, storeId);
    q.setParameter(2, exampleInt).setParameter(2, languageId);
    q.setParameter(3, exampleString).setParameter(3, name);
}

The first call to setParameter tells it the type, the second one sets the real value.

The reason behind this is that Postgres determines types during parse time, and Hibernate cannot determine the type of null, so it is assumed to be a java.io.Serializable at one stage, and which then tells it to assume bite[] at a later stage. This is done for legacy compatibility reasons with other databases, and is unlikely to change. Maybe the new Hibernate 6.0 type system will address it, but I haven't kept up. So then when it tells Postgres that the type is bytea, the query parser can't find an implicit type converter registered between bytea and the given other database type, so it throws an error.

The easiest solution is to use explicit type casts. Moreover, the right argument of LIKE must be a string, so enclosed in single quotes:

WHERE ... (?3::text IS NULL
           OR cd.name::text LIKE '%' || ?3::text || '%'
          )