`CONCAT` of `LIKE` with percentage (`%`) literal fails for `null` values

[SimSonic]

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: #2939

How can I fix query? Or this is implementation bug?

[mp911de]

Can you please attach the SQL that is generated from the query?

[SimSonic]

Can you please attach the SQL that is generated from the query?

A little bit cropped SQL:

select profileent0_."id"                   as id1_64_0_,
       profiledev1_."id"                   as id1_68_1_,
       profileent0_."blocked"              as blocked5_64_0_,
       profileent0_."coordinates_timezone" as coordina6_64_0_,
       profileent0_."coordinates_updated"  as coordina7_64_0_,
       profileent0_."login"                as dess_log9_64_0_,
       profileent0_."first_name"           as first_n11_64_0_,
       profileent0_."gps_timezone"         as gps_tim14_64_0_,
       profileent0_."last_name"            as last_na15_64_0_,
       profileent0_."last_seen"            as last_se16_64_0_,
       profileent0_."latitude"             as latitud17_64_0_,
       profileent0_."longitude"            as longitu18_64_0_,
       profileent0_."preset"               as preset20_64_0_,
       profiledev1_."application"          as applicat2_68_1_,
       profiledev1_."version"              as version3_68_1_,
       profiledev1_."auth_domain_type"     as auth_dom4_68_1_,
       profiledev1_."created"              as created5_68_1_,
       profiledev1_."installment_id"       as installm6_68_1_,
       profiledev1_."last_device"          as last_dev7_68_1_,
       profiledev1_."last_seen"            as last_see8_68_1_,
       profiledev1_."mobile_platform"      as mobile_p9_68_1_,
       profiledev1_."platform_version"     as platfor10_68_1_,
       profiledev1_."profile_id"           as profile11_68_1_
from "e2e_public"."profiles" profileent0_
         left outer join "e2e_public"."profiles_devices" profiledev1_ on (profiledev1_."profile_id" = profileent0_."id")
where (profileent0_."last_seen">=? and profileent0_."last_seen"<=? or profileent0_."last_seen" is null)
  and (? is null or profileent0_."id"=?)
  and (? is null or profileent0_."login" like (?||'%'))
  and (? is null or profileent0_."preset" like (?||'%'))
  and (? is null or profileent0_."last_name"=?)
  and (? is null or profileent0_."first_name"=?)
  and (? is null or profiledev1_."application"=?)
  and (? is null or profiledev1_."mobile_platform"=?)
  and (profiledev1_."last_seen" is null or profiledev1_."last_device" = true)
order by profileent0_."id" DESC
limit ?

so this lines are under question:

  and (? is null or profileent0_."login" like (?||'%'))
  and (? is null or profileent0_."preset" like (?||'%'))

[SimSonic]

2.7.12 generates this:

[mp911de]

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.

[gregturn]

Just to be clear, we used to take the wildcard and alter the input parameter by applying it.

Thus, LIKE :#{#filter.preset}% would be expanded by SpEL into LIKE :preset% (I'm not sure what your SpEL expression ACTUALLY expands to, but I'm just assuming preset), which would then be turned into LIKE :preset with the actual argument going from foo to foo%.

#2939 fixes the risk you face if you had :preset more than once in the query, with the last instance getting/not getting that wildcard.

Now we do LIKE :#{#filter.preset}% -> LIKE :preset% -> LIKE :preset || '%', with the actual input untouched. As @mp911de said, Postgres documentation shows this as supported, so something else is at play that is making it seem to transform things into a bytea operation.

I don't know if you can share you complete entity type as well as the arguments fed to that query. And I see you're using a projection, so that would be useful to know as well.

[gregturn]

Something else that could be at play is usage of Cyrillic alphabet as I see in the screenshot. I don't know if that's an issue at all or not, but if the data doesn't quite fit strings, the database could be trying to "help" by migrating to raw binary strings, and throwing everything for a loop. That would also be fitting in its warning to use a cast operation.

[SimSonic]

I don't know if you can share you complete entity type as well as the arguments fed to that query. And I see you're using a projection, so that would be useful to know as well.

Nothing interesting here.

Entity:

@Entity
@Table(name = "profiles")
@Getter
@Setter
@ToString
public class ProfileEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, unique = true)
    private Long id;

    @Column(name = "login")
    private String login;

    @Column(name = "phone")
    private String phone;

    @Column(name = "email")
    private String email;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "preset")
    private String preset;

    @Column(name = "last_seen")
    private Instant lastSeen;

    @Column(name = "latitude")
    private double latitude;

    @Column(name = "longitude")
    private double longitude;

    @Column(name = "coordinates_updated")
    private Instant coordinatesUpdated;

    @Column(name = "fis_timezone")
    private Integer fisTimezone;

    @Column(name = "coordinates_timezone")
    private Integer coordinatesTimezone;

    @Column(name = "gps_timezone")
    private Integer gpsTimezone;

    @Column(name = "blocked", nullable = false)
    private boolean blocked;
}

Filtering fields DTO:

@lombok.Value
@lombok.Builder
@lombok.Jacksonized
@JsonInclude(JsonInclude.Include.NON_NULL)
public class ProfileJournalFilter {

    Long id; // nullable...

    String login; // nullable!

    String preset; // nullable!

    @NotNull
    @Builder.Default
    Instant dateStart = TimeUtils.FILTER_DATE_TIME_START;

    @NotNull
    @Builder.Default
    Instant dateEnd = TimeUtils.FILTER_DATE_TIME_END;

    String lastName; // nullable!

    String firstName; // nullable!

    MobileApplication application; // it's enum, nullable

    MobilePlatform platform; // // it's enum, nullable
}

Projection:

public interface AdminProfileProjection {

    ProfileEntity getProfile();

    ProfileDeviceEntity getProfileDevice();
}

THe root of a problem is nullability of string fields in DTO. Before #2939 if preset was null it generated code like

... AND NULL IS NULL OR presetr LIKE NULL ...

but now it is:

... AND NULL IS NULL OR preset LIKE NULL || '%'

so PostgreSQL cannot concatenate NULL with '%' because interprets it as bytea.

Something else that could be at play is usage of Cyrillic alphabet as I see in the screenshot. I don't know if that's an issue at all or not, but if the data doesn't quite fit strings, the database could be trying to "help" by migrating to raw binary strings, and throwing everything for a loop.

No, it is not the case. All strings are utf-8 so no problems with ascii or cyrillic chars or even multi-byte emojies.

[mp911de]

so PostgreSQL cannot concatenate NULL with '%' because interprets it as bytea.

Thanks for the insight. That raises the general question how LIKE :#{#filter.login}% is supposed to work in the case of null as null cannot be combined with % (regardless of the underlying system). I suggest that you provide a property for SpEL usage that renders login + % on your side if the login field is not null.

[SimSonic]

No any other logic on my side. I think implementation before #2939 just did that (unexpectedly) :)

[mp911de]

Our code previously made an assumption regarding representing null values in the context of LIKE with percentage. We cannot rewrite the bound parameter value because if the parameter is used multiple times, the bound value no longer represents the actual value but the rewritten variant.

I wonder whether we could isolate LIKE with % literals so that we create a derived binding value and fall back to the previous approach @gregturn.