Using || (pipes) along with named parameters in custom queries raises an exception [DATAJPA-1652]

[spring-projects-issues]

Andriy Redko opened DATAJPA-1652 and commented

Since the Lovelace release train, the usage of '||' along named parameters in custom queries ends up with Caused by: org.hibernate.QueryException: Named parameter [..] not set exception. The query is parsed and works as expected in Ingalls and before.

Example:

@Query(value = "SELECT u FROM User u WHERE u.lastname LIKE '%'||:name||'%'")
Page<User> findByNamedQueryWithLike(@Param("name") String name, Pageable page); 

The pipes (||) are supported by some SQL dialects (and HSQL) in order to support concatenation of multiple values, alternatively to concat function.

The cause: the identifier pattern (declared in QueryUtils) has changed in Lovelace and above, instead of capturing 'name', it captures 'name||', leading to:

Caused by: org.hibernate.QueryException: Named parameter [name] not set
        at org.hibernate.query.internal.QueryParameterBindingsImpl.verifyParametersBound(QueryParameterBindingsImpl.java:251)
        at org.hibernate.query.internal.AbstractProducedQuery.beforeQuery(AbstractProducedQuery.java:1313)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1412)
        at org.hibernate.query.Query.getResultList(Query.java:146) 

 

---

Affects: 2.1.14 (Lovelace SR14), 2.2.3 (Moore SR3)

Referenced from: pull request #405

Backported to: 2.2.4 (Moore SR4), 2.1.15 (Lovelace SR15)

[spring-projects-issues]

Jens Schauder commented

This problem is not just with | but also other characters that are considered punctuation by posix but not by unicode.

For example  > and < have the same problem.

See https://www.unicode.org/reports/tr18/#General_Category_Property
vs https://docs.oracle.com/javase/7/docs/api/java/util/regex/Pattern.html#posix

 An easy workaraound is to have a space between the parameter name and the following character

[spring-projects-issues]

Andriy Redko commented

Thanks for looking Jens Schauder, I agree that pipes is not the only issue (it just what triggered problem for us). It is certainly good to have a workaround but from the other side, quite inconvenient, the whitespaces should not change the outcomes. I would be happy to improve the PR (https://octolinker-demo.now.sh/spring-projects/spring-data-jpa/pull/405) to exclude more punctuation characters from the identifier name (or include narrowed set of punctuation characters), what do you think? Thanks!

[spring-projects-issues]

Jens Schauder commented

If you get a replacement of \P{P} with \P{Punct} to work I would appreciate such a PR

[spring-projects-issues]

Andriy Redko commented

Thanks Jens Schauder, resubmitted the change with \P{Punct}, would appreciate your opinion, thank you