Spring Spring Data: query with null handling not working

Affects: spring-boot 2.6.6

While trying to write a query via Spring Data, I found a confusing bug:

Look at following two methods. Only difference is the order in the where block

// not working
@RestResource(path = "description1")
@Query("select u from User u where description like %:description% or :description is null")
List<User> findByDescription1(String description);

// working
@RestResource(path = "description2")
@Query("select u from User u where :description is null or description like %:description%")
List<User> findByDescription2(String description);

Calling them both give different results: - Calling both without any parameter results the same - /api/widgetTeamDaten/search/description1?description=test - 0 results - /api/widgetTeamDaten/search/description2?description=test - 2 results

Looking up, what Hibernate is doing whith the parameters, in description1 the parameter is bound with test, in description2 with %test%:

/api/userDaten/search/description1
-> 3 matches
select user0_.id as id1_211_, user0_.version as version2_211_,  user0_.name as name3_211_, user0_.description as descript4_211_ from users user0_ where user0_.description like ? or ? is null
binding parameter [1] as [VARCHAR] - [null]
binding parameter [2] as [VARCHAR] - [null]

/api/userDaten/search/description1?description=test
-> 0 matches
select user0_.id as id1_211_, user0_.version as version2_211_,  user0_.name as name3_211_, user0_.description as descript4_211_ from users user0_ where user0_.description like ? or ? is null
binding parameter [1] as [VARCHAR] - [test]
binding parameter [2] as [VARCHAR] - [test]

/api/userDaten/search/description2?description=test
-> 2 matches
select user0_.id as id1_211_, user0_.version as version2_211_,  user0_.name as name3_211_, user0_.description as descript4_211_ from users user0_ where ? is null or user0_.description like ?
binding parameter [1] as [VARCHAR] - [%test%]
binding parameter [2] as [VARCHAR] - [%test%]

/api/userDaten/search/description2
-> 3 matches
select user0_.id as id1_211_, user0_.version as version2_211_,  user0_.name as name3_211_, user0_.description as descript4_211_ from users user0_ where ? is null or user0_.description like ?
binding parameter [1] as [VARCHAR] - [null]
binding parameter [2] as [VARCHAR] - [null]

There should be the same results, independent on the order in the where statement.

Comment From: quaff

I think you want description is null not :description is null, are you?

Comment From: iambastian

No, I want :description is null. This should have the effect, if there is no parameter given, then all Users should be returned.

My actual use-case has multiple parameters to search for users, i.e. search for name and description. If one parameter is not given, then the parameter should be ignored, but the other parameter should be used:

SELECT * 
FROM USERS 
WHERE 
    ( NAME IS NULL OR NAME LIKE ? ) AND 
    ( DESCRIPTION IS NULL OR DESCRIPTION LIKE ? )

Comment From: rstoyanchev

Thanks for getting in touch, but this is the Spring Framework issue tracker, and not Spring Data. Moreover, it feels like a question that would be better suited to Stack Overflow. As mentioned in the guidelines for contributing, we prefer to use the issue tracker only for bugs and enhancements. Feel free to update this issue with a link to the re-posted question (so that other people can find it) or add some more details if you feel this is a genuine bug.