Why when parameter is null, postgres throws an exception?

Question

@Query(value = "select distinct m from MerchantStore m 
left join fetch m.parent mp left join fetch m.country mc 
left join fetch m.currency mc left join fetch m.zone mz 
left join fetch m.defaultLanguage md 
left join fetch m.languages mls where (?1 is null or m.storename like %?1%)",
countQuery = "select count(distinct m) from MerchantStore m where (?1 is null or m.storename like %?1%)")
    
Page<MerchantStore> listAll(String storeName, Pageable pageable);

I have the query from above, but it throws the following exception:

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.

When I rotate the condition to (m.storename like %?1% or ?1 is null) then it works okay.

Could someone explain to me what is going on here exactly? P.S. this is from Shopizer library, PageableMerchantRepository.java.

Answer 1

It could be because MerchantStore.storename is a non-nullable field/column! ;)

(?1 is null or m.storename like %?1%)

which postgres obviously not seems to like (with my words: "null is no valid 'data type' for that column"), whereas in:

(m.storename like %?1% or ?1 is null)

?1 is null (obviously!?) never evaluated.

So do it:

m.storename like %?1%

, and ensure to pass no nulls/what to do with them (like %null% or like '%'?).