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:

image

image

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

Most upvoted comments

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 as bytea.