JPA Query with like and concat fails dependening on the initial invocation order (Spring Boot 3.1.5)

[nilshartmann]

I have a JPA query that uses like together with concat. In Spring Boot 3.1.4 the query works fine, but starting with Spring Boot 3.1.5 it sometimes does not work anymore, and it seems it depends on the order it is executed.

The repository method:

@Query("""
        select b from Book b
            where :t is null or b.title like concat(:t, '%')
        """)
List<Book> findWithTitle(String t);

When the first call (after booting the application) to findWithTitle has the parameter t set to null, I receive the following postgres error:

InvalidDataAccessResourceUsage JDBC exception executing SQL [select b1_0.id,b1_0.title from books b1_0 where ? is null or b1_0.title like (?||'%') escape ''] [ERROR: operator does not exist: text ~~ bytea
  Hinweis: No operator matches the given name and argument types. You might need to add explicit type casts.

Susequent calls to findWithTitle with null as parameter then all fail, while calls with a String as parameter work as expected:

 bookRepository.findWithTitle(null); // ERR
 bookRepository.findWithTitle("lilalu"); // OK
 bookRepository.findWithTitle(null); // ERR
 bookRepository.findWithTitle("lilalu"); // OK

But when the application started and the first call to findWithTitle has set a String as parameter t, subsequent calls with null also are working:

 bookRepository.findWithTitle("lilalu"); // OK
 bookRepository.findWithTitle(null); // OK
 bookRepository.findWithTitle(null); // OK
 bookRepository.findWithTitle("lilalu"); // OK

(btw: I think the same problem occus when using lower instead of concat in the query).

If I add (as suggested by the error message) a cast in the query, it also works:

select b from Book b
                where :t is null or b.title like concat(cast(:t as text), '%')

(IntelliJ IDEA marks this as syntax error, so I'm not sure, if this should be valid query string or not, but it works)

You can find a reproducable test case here: https://github.com/nilshartmann/spring-jpa-concat

Please refer to the README.md file on how you can run the tests isolated using maven so that one fails and the other not, depending on the invocation order of findWithTitle.

[mp911de]

This is because we restricted TypedParameterValue usage (see #3137) to native queries only. The Hibernate team hinted us that using TypedParameterValue for JPQL queries is wrong.

The ticket we decided to introduce TypedParameterValue: https://hibernate.atlassian.net/browse/HHH-14411

Discussion about TypedParameterValue:

Gavin King: the thing is, as we've said repeatedly, it "just works" if you don't pass the (wrong) TypedParameterValue

Gavin King: because Hibernate infers the type from the query itself, I guess

https://hibernate.zulipchat.com/#narrow/stream/132096-hibernate-user/topic/.E2.9C.94.20Parameter.20binding.20as.20incorrect.20type.20on.20null.20with.20Converter/near/374576975

With a construction of concat(:t, '%') we have zero knowledge what the target type is expected to be and Spring Data isn't going to analyze the JPA model for type inference or attribute converters. That's the business of the underlying JPA provider.

As transformation of JPQL into SQL is also something that the underlying JPA provider does, I would ask you to file a ticket with Hibernate.

I'm sorry that this is quite frustrating.

[nilshartmann]

Hi @mp911de ,

thanks for your fast reply. I think / I hope I can work with the cast workaround in our app, but at least a warning or something would be great, because this problem was very hard to tackle down. But I think, if Spring Data would be able to warn about a problematic query, it would also be able to fix the problem? Probably not easy for you...

[mp911de]

We actually do not want to know what the query is doing, TBCH. We have parsers for JPQL, but these serve the purpose of deriving count queries or appending sort clauses. If we wanted to detect parameter usage within functions, then we would introduce a lot of complexity that we do not want to maintain. I wish there would be an easier way out.

[nilshartmann]

@mp911de Thanks a lot for your explanation! Didn't know why you're parsing the queries (and why not).

[mp911de]

After discussing with our team, we will stick to the guidance provided by the Hibernate team to not use ParametrizedTypeValue. Any caching issues within Hibernate that depend on the order of execution must be solved within Hibernate.