Spring boot 2 @Query named parameter binding value resolution messes up after upgrade from 1.5

Question

We have the following working query using SpringBoot 1.5:

@Query(value = "SELECT DISTINCT c FROM Customer c INNER JOIN c.industry i WHERE " +
            "c.role IN :roleFilter " +
            "AND (:#{#industryFilter.size()} = 1 OR i.id IN :industryFilter) " +
            "AND (:searchString IS NULL " +
            "OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') " +
            "OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) " +
            "AND (:includeDeleted = true OR c.deletedDate is NULL)",
            countQuery = "SELECT COUNT(DISTINCT c) FROM Customer c INNER JOIN c.industry i WHERE " +
                    "c.role IN :roleFilter AND " +
                    "(:#{#industryFilter.size()} = 1 OR i.id IN :industryFilter) " +
                    "AND (:searchString IS NULL " +
                    "OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') " +
                    "OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) " +
                    "AND (:includeDeleted = true OR c.deletedDate is NULL)")
    Page<Customer> findCustomers(@Param("roleFilter") Set<Role> roleFilter,
                                 @Param("industryFilter") Set<String> industryFilter,
                                 @Param("searchString") String searchString,
                                 @Param("includeDeleted") boolean includeDeleted, Pageable pageable);

Please note how we pass the input to the LIKE: CONCAT('%', :searchString, '%')

After upgrading from springBootVersion = '1.5.17.RELEASE' to springBootVersion = '2.1.3.RELEASE' (we use Gradle) that query will fail at runtime with an exception:

org.hibernate.QueryException: Named parameter not bound : includeDeleted

Replacing CONCAT('%', :searchString, '%') with %:searchString% fixes the problem.

The question I have is: why?

By going into debug mode and following the full callstack, I could see the parameters being correctly retrieved from the method invocation as observed in JdkDynamicAopProxy at line 205 makes a call Object[] argsToUse = AopProxyUtils.adaptArgumentsIfNecessary(method, args); that results in:

argsToUse = {Object[5]@15562} 
 0 = {HashSet@15491}  size = 4
 1 = {HashSet@15628}  size = 1
 2 = null
 3 = {Boolean@15629} false
 4 = {PageRequest@15490} "Page request [number: 0, size 20, sort: name: ASC,name2: ASC]"

So far so good. Then, we keep going and the method to call is also correctly resolved:

parameterTypes = {Class[5]@15802} 
 0 = {Class@198} "interface java.util.Set"
 1 = {Class@198} "interface java.util.Set"
 2 = {Class@311} "class java.lang.String"
 3 = {Class@15811} "boolean"
 4 = {Class@9875} "interface org.springframework.data.domain.Pageable"

Then we go a bit further and we get to RepositoryFactorySupport line 599 calling private Object doInvoke(MethodInvocation invocation) throws Throwable which uses private final Map<Method, RepositoryQuery> queries; from the inner class public class QueryExecutorMethodInterceptor implements MethodInterceptor (I am unsure when/how was this variable created and populated), which contains all the queries annotated with @Query in my repository interface.

For our specific case, it contains an entry (last one) that matches the query I am invoking (findCustomers):

queries = {HashMap@16041}  size = 3
 0 = {HashMap$Node@16052} "public abstract com.swisscom.psp.domain.Customer com.swisscom.psp.repository.CustomerRepository.getOne(java.lang.String)" -> 
 1 = {HashMap$Node@16055} "public abstract boolean com.swisscom.psp.repository.CustomerRepository.existsWithRole(java.lang.String,java.util.Set)" -> 
 2 = {HashMap$Node@16058} "public abstract org.springframework.data.domain.Page com.swisscom.psp.repository.CustomerRepository.findCustomers(java.util.Set,java.util.Set,java.lang.String,boolean,org.springframework.data.domain.Pageable)" -> 

And expanding that entry I can see where the error comes from, the binding for the :includeDeleted named parameter is simply not there:

value = {SimpleJpaQuery@16060} 
 query = {ExpressionBasedStringQuery@16069} 
  query = "SELECT DISTINCT c FROM Customer c INNER JOIN c.industry i WHERE c.role IN :roleFilter  AND (:__$synthetic$__1 = 1 OR i.id IN :industryFilter) AND (:searchString IS NULL OR CONCAT_WS(' ', c.name, c.name2) LIKE CONCAT('%', :searchString, '%') OR CONCAT_WS(' ', c.name2, c.name) LIKE CONCAT('%', :searchString, '%')) AND (:includeDeleted = true OR c.deletedDate is NULL)"
  bindings = {ArrayList@16089}  size = 6
   0 = {StringQuery$InParameterBinding@16092} "ParameterBinding [name: roleFilter, position: null, expression: null]"
   1 = {StringQuery$ParameterBinding@16093} "ParameterBinding [name: __$synthetic$__1, position: null, expression: #industryFilter.size()]"
   2 = {StringQuery$InParameterBinding@16094} "ParameterBinding [name: industryFilter, position: null, expression: null]"
   3 = {StringQuery$ParameterBinding@16095} "ParameterBinding [name: searchString, position: null, expression: null]"
   4 = {StringQuery$ParameterBinding@16096} "ParameterBinding [name: searchString, position: null, expression: null]"
   5 = {StringQuery$ParameterBinding@16097} "ParameterBinding [name: searchString, position: null, expression: null]"

Now, I have the fix as mentioned earlier, but I would still very much like to know the following for future reference:

1. when and how is the private final Map<Method, RepositoryQuery> queries variable created and populated?
2. what exactly is causing this error? Did I miss something in the upgrade process? Am I using/mixing deprecated logic/wrong logic and should change the code further?

Our DB is MariaDB 10.1.36

EDIT: In all the places where this behaviour occurred (in some it still occurs), the unbound parameter is always the last one

EDIT2: Someone else also has a similar behaviour after the upgrade, why does this happen? reference

EDIT3: reference and also this weird behaviour has been reported. Interesting enough, I do not get the exception IF I pass already concatenated input to :searchString (eg: %SOMETHING%) and I do get the exception if I leave %:searchString% instead. And yes, moving those parameters in the end solves some errors I had with binding.

EDIT4: Maybe related bug?

Clearly there is something strange going on, so: how does this binding resolution happen exactly?

Thanks in advance and have a nice day

Plonk

Answer 1

Actually, as far as I know, neither of your two approaches is the correct one to use here for handling LIKE with a wildcard placeholder. Instead, the LIKE expression should be:

LIKE :searchString

To this parameter :searchString you should be binding:

String searchString = "bananas";
String param = "%" + searchString + "%";
// then bind param to :searchString

That is, you bind the entire string, with the % wildcard, together. Then, let the database worry about how to escape it.

Impassable

According to Spring Data JPA reference example 65, this is supported: @Query("select u from User u where u.lastname like %:#{[0]}% and u.lastname like %:lastname%") List<User> findByLastnameWithSpelExpression(@Param("lastname") String lastname); – Plonk

If you scroll down that page a bit, you'll see an example of LIKE with a parameter, and the language parameter bound with appended %. This is exactly what my answer is saying to do. – Impassable

Sorry I really am not seeing that. At the provided link I am looking at example 65, where the % character is used unquoted in the query itself, not before binding. Other references in that page to % lead to an example with StringMatcher (table 5) and translation between naming and query (example 52) but no mentioning of concatenating before binding. Are we referring to the same source link? Also to be determined, why was it working before changing to Spring 2? Thanks for the help – Plonk

Also, maybe unrelated, the DB we have is MariaDB 10.1.36 – Plonk