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

Question

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

Pretty sure the issue is here as `cd.name like %?3%` as `LIKE` resolves to `~~`. So the error is occurring because `cd.name` which I assume is a varchar is being compared via `~~` to something Postgres thinks is a `bytea` type. In other words something is being done to `%?3%` to make it come across as `bytea` when `String name` is null. — Adrian Klaver, Sep 19 '20 at 16:53
I've given my answer that you are likely to need whenever you combine Postgres and Hibernate, but I would suggest you change your search logic. Instead of `(?3 is null or cd.name like %?3%)` have `cd.name ~* ?3`, and filter the parameter against regex symbols before calling, and if it's null, replace it with `.*` to match all. This is also case-insensitive regex search. The case-sensitive one is `cd.name ~ ?3` if you want. You shouldn't ever let untrusted user searches have unrestricted wildcards. — coladict, Sep 25 '20 at 13:01
for me, it was due to passing null value for a where parameter of type string — Gaurav, Mar 30 '21 at 05:50

score 3 · Answer 1 · answered Sep 24 '20 at 12:17

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

The problem has been discussed here: 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

I tried this solution. But still the same error. As the issue is with the later part in the condition and not in the null check. — mevada.yogesh, Sep 26 '20 at 05:26

Yann39 · Answer 2 · 2021-07-05T09:14:19.903

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.

score 1 · Answer 3 · answered Sep 25 '20 at 12:52

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.

Your analogy is quite interesting. But named queries do not accept the '%' in the query. So will need to provide "%"+ "name" + "%" in setParameter which is going to defeat the sole purpose the query is written for i.e. return all results if the name parameter is null. — mevada.yogesh, Sep 26 '20 at 06:19
Well you have three options. This one, or replace the parameter with an empty string when null before calling `listByStore`, or send patches for workarounds to Postgres, Hibernate or SpringBoot. As a personal preference I would go with a native query using regex that I have filtered the input against, as I suggested in the root question. — coladict, Sep 26 '20 at 12:33

score 1 · Answer 4 · answered Sep 28 '20 at 07:41

1

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 || '%'
          )

answered Sep 28 '20 at 07:41

Laurenz Albe

209,280
17
206
263

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

4 Answers4