How to check collection for null in spring data jpa @Query with in predicate

I have this query in my spring data jpa repository:

@Query("SELECT table1 FROM Table1 table1 "
   + "INNER JOIN FETCH table1.error error"
   + "WHERE table1.date = ?1 "
   + "AND (COALESCE(?2) IS NULL OR (table1.code IN ?2)) "
   + "AND (COALESCE(?3) IS NULL OR (error.errorCode IN ?3)) ")
List<Table1> findByFilter(Date date, List<String> codes, List<String> errorCodes);

When I run this query, it shows me this error by console:

    org.postgresql.util.PSQLException: 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: 1642

However if I run the query without the (COALESCE (?2) IS NULL OR part, just the table1.code IN ?2, it does work

Does anyone know what this error could be due to?

Answer

1. COALESCE with one parameter does not make sense. This is an abbreviated CASE expression that returns the first non-null operand. (See this)

2. I would suggest you to use named parameters instead of position-based parameters. As it’s stated in the documentation this makes query methods a little error-prone when refactoring regarding the parameter position.

3. As it’s stated in documentation related to the IN predicate:

The list of values can come from a number of different sources. In the constructor_expression and collection_valued_input_parameter, the list of values must not be empty; it must contain at least one value.

4. I would suggest you also avoid to use outdated Date and use instead java 8 Date/Time API.

So, taken into account all above, you should use a dynamic query as it was suggested also in comments by @SimonMartinelli. Particularly you can have a look at the specifications.

Assuming that you have the following mapping:

@Entity
public class Error
{
   @Id
   private Long id;
   private String errorCode;

   // ...
}

@Entity
public class Table1
{
   @Id
   private Long id;
   private LocalDateTime date;
   private String code;

   @ManyToOne
   private Error error;

   // ...
}

you can write the following specification:

import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;

import org.springframework.data.jpa.domain.Specification;
import org.springframework.util.CollectionUtils;

public class TableSpecs
{

   public static Specification<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return (root, query, builder) -> {
         root.fetch("error", JoinType.LEFT);
         Predicate result = builder.equal(root.get("date"), date);
         
         if (!CollectionUtils.isEmpty(codes)) {
            result = builder.and(result, root.get("code").in(codes));
         }
         if (!CollectionUtils.isEmpty(errorCodes)) {
            result = builder.and(result, root.get("error").get("errorCode").in(errorCodes));
         }
         return result;
      };
   }
}

public interface TableRepository extends CrudRepository<Table1, Long>, JpaSpecificationExecutor<Table1>
{
   default List<Table1> findByFilter(LocalDateTime date, List<String> codes, List<String> errorCodes)
   {
      return findAll(TableSpecs.findByFilter(date, codes, errorCodes));
   }
}

and then use it:

List<Table1> results = tableRepository.findByFilter(date, Arrays.asList("TBL1"), Arrays.asList("ERCODE2")));