Entity:

@Entity
@Data
@Table(name = "location")
@NoArgsConstructor
public class Location {
    
    @Id
    @Column(name = "")
    private String id;

    @Column(name = "status", nullable = false)
    @Enumerated(EnumType.STRING)
    private Status status;
}

Enum:

public enum Status {
    
    ACTIVE, INACTIVE

}

JpaRepository

@Repository
public interface LocationReposity extends CrudRepository<Product, UUID> {

    @Query("select l from Location l where l.deleted is null and (:status is null or LOWER(l.status)=LOWER(:#{#status?.toString()}))")
    public List<Location> findLocations(@Param("status") String status);
}

Expected:

We want to query locations. When status is null, it returns all. When status is active, it only returns locations with status 'ACTIVE'.

Actual:

When we assign status 'ACTIVE', it only returns locations with status 'Active'. But when we assign null, it throws errors:

org.postgresql.util.PSQLException: ERROR: function lower(bytea) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Questions:

With condition (:status is null), while it still compares status (LOWER(l.status)=LOWER(:#{#status?.toString()}))? Can we know an enum is null? How to judge? And hibernate will convert null to bytea?

Solutions:

After we change @query as below, it works:

@Query("select l from Location l where l.deleted is null and (:status is null or cast(l.status as text)=:status)")
    public List<Location> findLocations(@Param("status") String status);

Is there any other ways to pass an enum params in Query?