Like operator with % does not work when checking nullable

[polaris6921]

I tried to compare optional string value using @Query method by containing and nullable like this;

@Query(
        """
            select u from User u
            where (u.username like %:username% or :username is null)
                and (u.isStaff = :isStaff or :isStaff is null)
                and (u.isManager = :isManager or :isManager is null)
        """
    )
    fun findAllByUsernameAndStaffAndManager(
        username: String?,
        isStaff: Boolean?,
        isManager: Boolean?
    ): List<User>

When this code calls in runtime, however, the Query remove the % operator like this

Hibernate: 
    select
        user0_.is_manager as is_manag5_73_,
        user0_.is_staff as is_staff6_73_,
        user0_.username as usernam23_73_,
        ...
    from
        users_user user0_ 
    where
        (
            user0_.username like ? 
            or ? is null
        ) 
        and (
            user0_.is_staff=? 
            or ? is null
        ) 
        and (
            user0_.is_manager=? 
            or ? is null
        )
2023-01-12 08:32:19.776 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [john]
2023-01-12 08:32:19.776 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [john]
2023-01-12 08:32:19.777 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BOOLEAN] - [null]
2023-01-12 08:32:19.777 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [BOOLEAN] - [null]
2023-01-12 08:32:19.777 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [BOOLEAN] - [null]
2023-01-12 08:32:19.777 TRACE 2796 --- [atcher-worker-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [BOOLEAN] - [null]

I found that this issue is solved when changing SQL order as checking nullable first, then containing, but I think this should be fixed.

Hibernate: 
    select
        user0_.is_manager as is_manag5_73_,
        user0_.is_staff as is_staff6_73_,
        user0_.username as usernam23_73_,
        ...
    from
        users_user user0_ 
    where
        (
            ? is null 
            or user0_.username like ?
        ) 
        and (
            user0_.is_staff=? 
            or ? is null
        ) 
        and (
            user0_.is_manager=? 
            or ? is null
        )
2023-01-12 08:30:39.203 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [%john%]
2023-01-12 08:30:39.204 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [%john%]
2023-01-12 08:30:39.204 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BOOLEAN] - [null]
2023-01-12 08:30:39.204 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [BOOLEAN] - [null]
2023-01-12 08:30:39.204 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [BOOLEAN] - [null]
2023-01-12 08:30:39.204 TRACE 2663 --- [atcher-worker-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [6] as [BOOLEAN] - [null]

My spring data jpa version is 2.6.10.

[gregturn]

I've reproduced this against the latest version of Spring Data JPA.

[gregturn]

This problem appears to involve the query parser, and isn't simply confined to null. It appears solely related to parsing of LIKE operators. Hence, this doesn't run the null handling stuff we coded our condensing function to handle.

[gregturn]

When the query is this:

@Query("select e from EmployeeWithName e where e.name like %:partialName%")
List<EmployeeWithName> customQueryWithNullableParam(@Nullable @Param("partialName") String partialName);

...where there is simply the LIKE and the inputs are surrounded by %, the StringQuery.parseParameterBindingsOfQueryIntoBindingsAndReturnCleanedQuery method extracts the :partialName, derives a LikeBinding and re-wraps the argument with %, e.g. "Baggins" becomes "%Baggins%". The query itself is transformed into select e from EmployeeWithName e where e.name like :partialName with the wildcards moved into the bound variable.

2023-03-22 15:12:08,322 DEBUG                        org.hibernate.SQL: 128 - select e1_0.id,e1_0.name from EmployeeWithName e1_0 where e1_0.name like ?
2023-03-22 15:12:08,325 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [1] as [VARCHAR] - [%Baggins%]

However, when the query is this:

@Query("select e from EmployeeWithName e where e.name like %:partialName% or :partialName is null")
List<EmployeeWithName> customQueryWithNullableParam(@Nullable @Param("partialName") String partialName);

...then apparently the same process turns the query into:

2023-03-22 15:12:35,571 DEBUG                        org.hibernate.SQL: 128 - select e1_0.id,e1_0.name from EmployeeWithName e1_0 where e1_0.name like ? or ? is null
2023-03-22 15:12:35,573 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [1] as [VARCHAR] - [Baggins]
2023-03-22 15:12:35,574 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [2] as [VARCHAR] - [Baggins]

In both situations, the "%" are stripped out of the query. However, in the first, the wildcards are moved to the bound variable. In the second, they aren't. At a glance, this looks like a bug.

But I'm curious if we need this approach now that we have the query parsers in place.

[gregturn]

The problem is that you're using the same parameter in two different places. When Spring Data JPA does the parameter binding, if there is a LIKE operator with any wildcards, the wildcards are moved to the bound parameter. This was done in the past to make query management easier.

Unfortunately, it turns into a race condition, in which the LAST instance of that parameter (:partialName) will overwrite any prior instances of that parameter. For example, if the query were select e from EmployeeWithName e where :partialName is null or e.name like %:partialName% (with the like clause being last), then you will see this:

2023-05-01 12:16:45,587 DEBUG                        org.hibernate.SQL: 133 - select e1_0.id,e1_0.name from EmployeeWithName e1_0 where ? is null or e1_0.name like ?
2023-05-01 12:16:45,589 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [1] as [VARCHAR] - [%Baggins%]
2023-05-01 12:16:45,589 TRACE              org.hibernate.orm.jdbc.bind:  28 - binding parameter [2] as [VARCHAR] - [%Baggins%]

That's because the LAST binding done on partialName in this scenario is the LIKE clause, and the wildcards get applied. Essentially, there is only ONE binding actually submitted to Hibernate, and it simply logs BOTH places they are used.

One way to work around this is to put two different named parameters in the query, and feed the input twice. This will act as expected.

As stated, this particular wrinkle of Spring Data JPA was implemented to move wildcards out of the query and into the bindings to ease a really complicated query pattern. The consequence is that we can't go back to standard JPQL (or HQL) and leave the wildcards in the query without breaking a lot of existing applications.

I may explore if there is a backward compatible way to handle this scenario, but it will take time and effort.

[gregturn]

I'd also like @schauder and @mp911de opinion on potential approaches.

[gregturn]

With a little more digging it has become apparent that the solution in your situation requires a rewrite like this:

@Query(
        """
            select u from User u
            where (u.username like '%' || :username || '%' or :username is null)
                and (u.isStaff = :isStaff or :isStaff is null)
                and (u.isManager = :isManager or :isManager is null)
        """
    )
    fun findAllByUsernameAndStaffAndManager(
        username: String?,
        isStaff: Boolean?,
        isManager: Boolean?
    ): List<User>

By decoupling the wildcards from the parameter binding, Spring Data JPA will no longer attempt to "rewrite" your like condition and then later stomp on that with the is null check.

I realize this may be confusing so I've opened #2939 to update the reference documentation and properly document this. Basically, like %:param% is a shortcut we offer that will yank the wildcards out and move them to the parameter binding. However if that parameter is used more than once, you now run the risk of the wildcard being there or NOT being there, based on where it appears in the query. And unless ALL usages of that parameter are the same, your query will most likely be wrong.

If you rewrite your query as shown above you will sidestep all that risk and the query should operate as expected.

[gregturn]

Superceded by #2939.