spring-data-jpa: Annotated query with wildcard-appended named parameter does not handle null in 2.7.0

Is there any plan for this issue ? (and the other related issues)

This was backported to 2.7.x as of f0216b0d.

@gregturn seems like the change you made at 3d8b287e0e2129ec827b15613b44f31c19c65c61 addresses the original problem in 3.0.0-M5 release line. However, it is not obvious to me if this issue has to be handled in a different way in 2.7.x or will be backported to 2.7.x automatically during the next release.

I am maintaining an application that is stuck in 2.6.x due to this issue which is closed now, I tried to reproduce with spring-boot 2.7.3 and the issue still persists there. Any feedback regarding 2.7.x would be really helpful for the app maintainers who still use 2.6.x as noted by quaff here

We have the same issue with 2.7.4 and as workaround it helps to use (:name IS NULL or p.name LIKE '%'||:name||'%' ) instead of (:name IS NULL or p.name LIKE %:name%)

(we use postgres, but other db I think it’s possible to use the appropriate concatenation func) @nemezmaksim

@jonhakr @gregturn the original issue was fixed however there still is a problem with null values. The demo app doesn’t show an example where the database contains null values. We use null values in ceratin columns which in turn does not return the full dataset we require. This worked correctly in versions under 2.7.0. Now on version 2.7.5 it does not.

Example

The null value previously was bound as null instead of wildcard like it is now.

Using the query from the demo app:

Hibernate: 
    select
        jedi0_.id as id1_0_,
        jedi0_.description as descript2_0_,
        jedi0_.name as name3_0_ 
    from
        jedi jedi0_ 
    where
        (
            ? is null 
            or lower(jedi0_.name)=?
        ) 
        and (
            ? is null 
            or jedi0_.description like ?
        )

We change the data so it contains a null value in one of the records like so:

saved = jediRepository.saveAll(listOf(
        Jedi(1, "General Kenobi", "A bold one"),
        Jedi(2, "Anakin Skywalker", null),
        Jedi(3, "Mace Windu", "Purple Lightsaber"),
    ))

And when we run a query with parameters set to null the following parameters are bound:

: binding parameter [1] as [VARCHAR] - [null]
: binding parameter [2] as [VARCHAR] - [null]
: binding parameter [3] as [VARCHAR] - [%%]
: binding parameter [4] as [VARCHAR] - [%%]

This in turn returns only 2 of the records when all records should be returned:

org.opentest4j.AssertionFailedError: 
expected: 3
 but was: 2

However on version 2.6.4 before this issue appeared the null parameter is picked up by the is null condition and isn’t turned into a wildcard:

: binding parameter [1] as [VARCHAR] - [null]
: binding parameter [2] as [VARCHAR] - [null]
: binding parameter [3] as [VARCHAR] - [null]
: binding parameter [4] as [VARCHAR] - [null]

Which correctly returns all records.

I am still facing this issue with Spring Boot 3+ for the edge case of enum object nested within another object. Here’s an example:

public interface MyRepository extends CrudRepository<Person, Long> {

    @Query(value = "SELECT p FROM Person p" +
            " WHERE :#{#filter.type} IS NULL OR p.type = :#{#filter.type}")
    List<Person> findCustom(@Param("filter") Person.Filter filter);
}

public class Person {
    // ....
    private Type type;

    public static class Filter {
        Type type;
    }

    public enum Type {
        VALUE_1, VALUE_2
    }
}

This worked with 2.7.x

Strictly speaking, #2653 is a duplicate of this