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 3 years ago
  • Comments: 27 (17 by maintainers)

Commits related to this issue

Most upvoted comments

@SimSonic I’m afraid not. There are simply too many differences between Spring Data JPA 2.7.x and 3.x to backport it that far.

Resolved. Merged to main.

Okay, it looks like CAST( :parm as string) is purely Hibernate and will fail with EclipseLink, but CAST(:parm as text) seems to be more universal.

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.