I'm using Kotlin, Spring Boot and Spring Data with Hibernate and Postgresql.

We created a repository class which extends JpaRepository and just found out that native queries are not working if nullable parameter is provided.

In this example, isInternal parameter can be true, false or null if we don't know that information yet (same as in our database).

    @Query(
        nativeQuery = true,
        value = "SELECT * FROM story WHERE is_internal = :isInternal",
        countQuery = "SELECT COUNT(*) FROM story WHERE is_internal = :isInternal"
    )
    fun findEmployeeStories(
        @Param("isInternal") isInternal: Boolean?,
        pageable: Pageable
    ): Page<StoryEntity>

This query results in:

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

If we try to add explicit casting, error changes.

    @Query(
        nativeQuery = true,
        value = "SELECT * FROM story WHERE is_internal = CAST(:isInternal AS boolean)",
        countQuery = "SELECT COUNT(*) FROM story WHERE is_internal = CAST(:isInternal AS boolean)"
    )
    fun findEmployeeStories(
        @Param("isInternal") isInternal: Boolean?,
        pageable: Pageable
    ): Page<StoryEntity>

This query results in:

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to boolean

If we change @Param("isInternal") isInternal: Boolean? to @Param("isInternal") isInternal: Boolean (to forbid nullable values) it works fine but we don't want that.

Above is just a simple example to reproduce the issue. Our query is complex that's why we tried to switch to native query instead of using JQL (where there where no such problems).

Thanks