Unable to do a query with a null paramater with 'contains' and 'is null' in 2.7.0

[pkernevez]

We works with postgresql event I don't think it change something.
This was working fine until 2.6.4 (included).

We had a query with a param that should be null.

    @Query("SELECT b FROM BenchmarkEntity b WHERE (:name is null or b.name like %:name%) "
           "and (:reference is null or b.reference = :reference) ")
    List<BenchmarkEntity> findBenchmarks(String name, Boolean reference);

It avoids us to use a 'like' when the optional parameter 'name' is null.

Before 2.6.4, it returned all the tables rows.
With 2.7.0, it doesn't return any row => no match

The behaviour changed for the parameter pass to the sql query.
Before : 2.6.4 (working)

11:37:57.415 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [VARCHAR] - [null]
11:37:57.415 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [VARCHAR] - [null]
11:37:57.416 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [BOOLEAN] - [null]
11:37:57.416 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [BOOLEAN] - [null]

With 2.7.0 (broken):

11:48:41.547 TRACE [,,] 34086 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@78184e8c%]
11:48:41.547 TRACE [,,] 34086 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@78184e8c%]
11:48:41.548 TRACE [,,] 34086 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [BOOLEAN] - [null]
11:48:41.548 TRACE [,,] 34086 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [4] as [BOOLEAN] - [null]

It seems that the bind value is the encapsulating instance (TypedParameterValue) instead of it's internal value, but only for String value (works with other types).

What is strange is that the QueryParameters object has a list of TypedValue (normal), and for the boolean, the value is 'null'

But for the String the value is not null but '%org.hibernate.jpa.TypedParameterValue@7eaf7a70%'

When I remove the 'contains' part in the query, the behaviour didn't change between the 2.6.4 and the 2.7.0, meaning it works fine with 2.7.0.

    @Query("SELECT b FROM BenchmarkEntity b WHERE (:name is null) "
           "and (:reference is null or b.reference = :reference) ")
    List<BenchmarkEntity> findBenchmarks(String name, Boolean reference);

11:37:57.415 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [1] as [VARCHAR] - [null]
11:37:57.416 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [2] as [BOOLEAN] - [null]
11:37:57.416 TRACE [,,] 33012 --- [           main] o.h.t.d.sql.BasicBinder        : binding parameter [3] as [BOOLEAN] - [null]

That seems it's the fact that the param is use in a 'contains' clause change its value instead of the binding.

This issue may linked to #2544 and #2549

[jochenwierum]

Possibly a duplicate of #2548?

[pkernevez]

Yes, duplicate of #2548

[gregturn]

Resolved by the linked commit.

[dvag-joerg-winter]

@gregturn
Hi, so the fix for this issue is not available in Spring Releases 2.7.x ..right ?
this question is, bc for basic errors like this one would expect a fix in 2.6 & 2.7 line also

[joheb-mohemian]

@dvag-joerg-winter Would like a fix too...we currently use 2.7.x but override the spring-data-bom.version property as
<spring-data-bom.version>2021.1.3</spring-data-bom.version>, i.e. a version that did not have the bug...

[gregturn]

Backported to 2.7.x with f0216b0

[dvag-joerg-winter]

@gregturn
Is this already fixed in spring-projects/spring-boot#32275
or later ?

[Axinet]

Looks like the issue is still present with newest spring-boot 2.7.5 version :(. Just encountered it when trying to upgrade from 2.6.x line and found this issue-topic.

[gregturn]

For Spring Boot 2.7, if you bump your app's spring-data-bom.version up to 2021.2.6 or higher and you should see the patch.

[joheb-mohemian]

@gregturn This works indeed. But forgive me for asking, if spring-boot 2.7.x is compatible with spring-data-bom.version >= 2021.2.6, why isn't there a spring-boot 2.7.x release that includes one of these spring-data versions? Wouldn't the fix for this bug justify a minor release?