Nullable parameter with optional check query regression

[therealaleko]

In 2.6.x this worked (focus is on the last and clause)

@Query("select distinct t from AgentParticipant p " +
      "join p.transaction t " +
      "where p.agent.id = :agentId " +
      "and t.lifecycleState in :state " +
      "and (  :searchText is null or upper(t.address.street) like %:searchText% or upper(t.address.street2) like %:searchText% or upper(t.address.city) like %:searchText% or t.address.zip like %:searchText% )"
  )
  Page<Transaction> findAllForAgentInLifecycleState(
      @Param("agentId") String agentId,
      @Param("state") Set<TransactionLifecycleState> state,
      @Param("searchText") String searchText,
      Pageable pageable);

In 2.7 it no longer works. It doesn't throw an exception, but rather it returns the incorrect result set.

Workaround is to move the null check at the end of the query like so...

      "join p.transaction t " +
      "where p.agent.id = :agentId " +
      "and t.lifecycleState in :state " +
      "and ( upper(t.address.street) like %:searchText% or upper(t.address.street2) like %:searchText% or upper(t.address.city) like %:searchText% or t.address.zip like %:searchText% or :searchText is null )"
  )

Other passing scenario:

• If the query does not use LIKE clauses it works . E.G., replace like with equality checks

[therealaleko]

here is github repo that reproduces the issue.

https://github.com/therealaleko/spring-things

Just revert the version to any 2.6.x SB version and the test will pass

[schauder]

Duplicate of #2548