spring-data-jpa: `CONCAT` of `LIKE` with percentage (`%`) literal fails for `null` values
Hello! I have such repository method (simplified):
@Query("""
SELECT pe AS profile,
device AS profileDevice
FROM ProfileEntity AS pe
LEFT JOIN ProfileDeviceEntity AS device
ON device.profile = pe
WHERE (pe.lastSeen IS NULL OR (pe.lastSeen >= :#{#filter.dateStart} AND pe.lastSeen <= :#{#filter.dateEnd}))
AND ( :#{#filter.id} IS NULL OR pe.id = :#{#filter.id} )
AND ( :#{#filter.login} IS NULL OR pe.login LIKE :#{#filter.login}% )
AND ( :#{#filter.preset} IS NULL OR pe.preset LIKE :#{#filter.preset}% )
AND ( :#{#filter.lastName} IS NULL OR pe.lastName = :#{#filter.lastName} )
AND ( :#{#filter.firstName} IS NULL OR pe.firstName = :#{#filter.firstName} )
AND ( :#{#filter.application} IS NULL OR device.application = :#{#filter.application} )
AND ( :#{#filter.platform} IS NULL OR device.platform = :#{#filter.platform} )
AND ( device.lastSeen IS NULL OR device.lastDevice IS TRUE )
ORDER BY pe.id DESC
""")
Page<AdminProfileProjection> profilesForJournal(Pageable pageable, ProfileJournalFilter filter);
Columns are defined simply:
After I upgraded Spring Boot from 2.7.12 to 2.7.13 I’ve received failing test which calls this method. The error:
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying ~~ bytea
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 1805
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse([QueryExecutorImpl.java:2713](https://queryexecutorimpl.java:2713/))
...
I’ve discovered that this is caused by this feature: https://github.com/spring-projects/spring-data-jpa/issues/2939
How can I fix query? Or this is implementation bug?
About this issue
- Original URL
- State: closed
- Created a year ago
- Comments: 27 (17 by maintainers)
Commits related to this issue
- Use COALESCE for LIKE with wildcards. To handle potential NULL values when LIKE is combined with wildcards, insert a COALESCE function. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use COALESCE for LIKE with wildcards. To handle potential NULL values when LIKE is combined with wildcards, insert a COALESCE function. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use COALESCE for LIKE with wildcards. To handle potential NULL values when LIKE is combined with wildcards, insert a COALESCE function. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use COALESCE for LIKE with wildcards. To handle potential NULL values when LIKE is combined with wildcards, insert a COALESCE function. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use COALESCE for LIKE with wildcards. To handle potential NULL values when LIKE is combined with wildcards, insert a COALESCE function. Also, apply a provider-specific CAST to parameters to ensure it... — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use unique named parameter bindings for like parameters. We now replace LIKE expressions according to their type with individual bindings if an existing binding cannot be used because of how the boun... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Revise String Query `ParameterBinding`. We now distinguish between the binding parameter target and its origin. The parameter target represents how the binding is bound to the query, the origin point... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Ensure correct bindings for parameter reuse. We now verify all bindings to ensure that a like-parameter doesn't mix up with plain bindings (e.g. firstname = %:myparam or lastname = :myparam). We also... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Polishing. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use unique named parameter bindings for like parameters. We now replace LIKE expressions according to their type with individual bindings if an existing binding cannot be used because of how the boun... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Revise String Query `ParameterBinding`. We now distinguish between the binding parameter target and its origin. The parameter target represents how the binding is bound to the query, the origin point... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Ensure correct bindings for parameter reuse. We now verify all bindings to ensure that a like-parameter doesn't mix up with plain bindings (e.g. firstname = %:myparam or lastname = :myparam). We also... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Polishing. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
- Use unique named parameter bindings for like parameters. We now replace LIKE expressions according to their type with individual bindings if an existing binding cannot be used because of how the boun... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Revise String Query `ParameterBinding`. We now distinguish between the binding parameter target and its origin. The parameter target represents how the binding is bound to the query, the origin point... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Ensure correct bindings for parameter reuse. We now verify all bindings to ensure that a like-parameter doesn't mix up with plain bindings (e.g. firstname = %:myparam or lastname = :myparam). We also... — committed to spring-projects/spring-data-jpa by mp911de a year ago
- Polishing. See #3041 — committed to spring-projects/spring-data-jpa by gregturn a year ago
Resolved. Merged to
main.Thanks a lot.
#{…}%are two parameters; therefore we went for concatenation. https://www.postgresql.org/docs/15/sql-syntax-calling-funcs.html calls out that parametrized concatenation works. Let us investigate a bit further to check what causes that the value is sent asbytea.