ERROR: operator does not exist: bigint = character varying for empty list of long

Question

I'm using Hibernate to get data from Postgresql DB, but always getting this error when trying to pass EMPTY or NULL as list of Long values to query:

@Repository
public interface ProductRepository extends JpaRepository<Category, Long> {
    @Query(nativeQuery = true, 
        value = "SELECT * FROM Products p " + 
                "WHERE (COALESCE(:#{#param.colors}) IS NULL OR p.color IN (:#{#param.colors}))")
    List<Product> findFiltered(@Param("param") ProductFilterParams params);
}

(note: I use native query because of complex request)

ProductFilterParams looks like:

@Data
public class ProductFilterParams {
    private List<Long> colors;

    ...other product filters...
}

So the idea is to filter products by color (which works good) or show all of them, if there is no color filter (and here I get this error).

When I pass null as colors logs is:

binding parameter [1] as [LONGVARCHAR] - [null]

ERROR: operator does not exist: bigint = character varying

or when I try to pass empty list:

*** NO BINDING LOG AT ALL ***

ERROR: syntax error at or near ")"

I have no idea why this error occure and how to fix it. I tried some googled answers from web/stackoverflow, but all of them for HQL and can't fix it for native query.

@RogerLindsjö the OR statement. In fact I have many other product filters, so query looks like: `where (:a is null or column_a in (:a)) and (:b is null or column_b in (:b) and ...)` — koigor, Jan 28 '21 at 07:35

SternK · Accepted Answer · 2021-01-28T22:23:17.867

Actually, hibernate documentation says:

IN predicates performs a check that a particular value is in a list of values. Its syntax is:

in_expression ::= single_valued_expression [NOT] IN single_valued_list

single_valued_list ::= constructor_expression | (subquery) | collection_valued_input_parameter

constructor_expression ::= (expression[, expression]*)

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.

But, as it is explained here it was changed in hibernate 5.4.10. But as you use hibernate 5.3.7.final you should move checking for collection emptiness outside your query.

And by the way, there is no sense to use COALESCE function with one argument.

ERROR: operator does not exist: bigint = character varying for empty list of long

1 Answers1