Annotated query with wildcard-appended named parameter does not handle null in 2.7.0

[jonhakr]

Context

We often create queries with optional filtering on parameters (ignore them when null) like so:

@Query("""
    select j from Jedi j
    where (:name is null or j.name = :name)
    and (:desc is null or j.description like %:desc%)
  """)
  fun searchContains(
      @Param("name") name: String?,
      @Param("desc") desc: String?,
  ): List<Jedi>

As noted in Example 62 in https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.advanced-like

the LIKE delimiter character (%) is recognized, and the query is transformed into a valid JPQL query (removing the %).
Upon running the query, the parameter passed to the method call gets augmented with the previously recognized LIKE pattern.

This worked fine previously, but after upgrading til 2.7.0 it does not seem to work when such a named parameter is null, which it is intended to be when using that to implement optional filtering like this.

Problem

When the named parameter is null, the binded value is the literal string org.hibernate.jpa.TypedParameterValue@<number> augmented with the wildcard parameter, instead of just null.

I have prepared a demo app that showcases the issue: https://github.com/jonhakr/spring-data-jpa-null-optional-query
The query above is generated as such:

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

When name is null it works fine, and the resulting bound parameter values are:

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

However, if the wildcard appended desc is null, the resulting bound parameter values are:

binding parameter [1] as [VARCHAR] - [Mace Windu]
binding parameter [2] as [VARCHAR] - [Mace Windu]
binding parameter [3] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@38d895e8%]
binding parameter [4] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@38d895e8%]

Simply setting spring-data-jpa to 2.6.4 makes this work fine again

[jonhakr]

Possibly related to #2544 ?

[quaff]

Possibly related to #2461.

[gregturn]

Related: #2549

[mhdb96]

Having the same problem with spring-data-jpa to 2.7.0

[pkernevez]

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

[edigu]

@gregturn seems like the change you made at 3d8b287 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

[gregturn]

This was backported to 2.7.x as of f0216b0.

[michalkleszcz12]

@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.

[dvcer]

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]

I have updated the example with null data in DB, and can confirm that this still fails for 2.7.5 (also fails for 2.7.0 as before), as stated by @michalkleszcz12, since the injected parameter in the ? is null comparison is the relevant wildcards instead of null.

[gregturn]

@jonhakr Check out the snapshots to see if things are now working as expected.

[dwursteisen]

Hi 👋

For Information, I bump to Spring Boot 2.7.5 yesterday. Some tests failed after the bump.
After investigation, it's the same issue described in this ticket. So I try the 2.7.6-SNAPSHOT version.
All tests turned green using this snapshot version. So @gregturn fixed our issue, in our case.

Regards

[jonhakr]

Can confirm what @dwursteisen is saying. Updated example with spring-data set to that snapshot, and all tests went green

[gregturn]

Duplicate of #2653.

[jonhakr]

Strictly speaking, #2653 is a duplicate of this

[livanov]

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

[meisten]

2.7.x

Be careful if you use the Spring Native project.

You should create a hint for the TypedParameterValue type

@TypeHint(types = org.hibernate.jpa.TypedParameterValue.class)
public class YourNativeHibernateHints implements NativeConfiguration {

}

The hint for this type is missing from the native configuration for Hibernate.

Without this hint, your binary will use org.hibernate.jpa.TypedParameterValue:

select distinct stationent0_.id as col_0_0_ 
from stations stationent0_ 
where 
    (NULL is null or stationent0_.city_id=NULL) and 
    ('%org.hibernate.jpa.TypedParameterValue@701ac132%' is null or 
        lower(stationent0_.address) like '%org.hibernate.jpa.TypedParameterValue@701ac132%'
    ) 

[ankovi]

Spring Boot 3.1.4 :

Same problem persists (in my case for a Boolean parameter), but, resolvable as follows :

@Query("""
        FROM StockEntity s
        WHERE
                  (:#{#species?.size() < 1} = true OR s.objectCode IN :species)
                AND
                  (:#{#areas?.size() < 1} = true OR s.locationCode IN :areas)
                AND
                  (:#{#previous?.size() < 1} = true OR s.ancestor.id IN :previous)
                AND
                  (:#{#multiple == null} = true OR s.multiSpeciesIndicator = :multiple)
        """)
Page<StockEntity> searchStocks(List<String> species, List<String> areas, List<Long> previous, Boolean multiple, Pageable pageable);

This throws :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = boolean
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

The problem is with the Boolean multiple parameter.
But, if I use SPeL also for the rest of the (last line) it works fine :

AND
(:#{#multiple == null} = true OR s.multiSpeciesIndicator = :#{#multiple})

NOTE: Testing always worked fine without any problem, so I'd say this was resolved somewhow in Spring Test, but
not in the Spring Boot JPA.

It would be nice though to have it working without the need of SPeL as in the versions prior to 2.7.x.

[madyx]

Spring Boot 3.1.4 :

Same problem persists (in my case for a Boolean parameter), but, resolvable as follows :

@Query("""
        FROM StockEntity s
        WHERE
                  (:#{#species?.size() < 1} = true OR s.objectCode IN :species)
                AND
                  (:#{#areas?.size() < 1} = true OR s.locationCode IN :areas)
                AND
                  (:#{#previous?.size() < 1} = true OR s.ancestor.id IN :previous)
                AND
                  (:#{#multiple == null} = true OR s.multiSpeciesIndicator = :multiple)
        """)
Page<StockEntity> searchStocks(List<String> species, List<String> areas, List<Long> previous, Boolean multiple, Pageable pageable);

This throws :

Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = boolean Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

The problem is with the Boolean multiple parameter. But, if I use SPeL also for the rest of the (last line) it works fine :

AND (:#{#multiple == null} = true OR s.multiSpeciesIndicator = :#{#multiple})

NOTE: Testing always worked fine without any problem, so I'd say this was resolved somewhow in Spring Test, but not in the Spring Boot JPA.

It would be nice though to have it working without the need of SPeL as in the versions prior to 2.7.x.

Same error in 3.1.5