Like with an escape clause fails

[olavloite]

ERROR: Postgres function like_escape(text, text) is not supported - Statement: 'select s1_0.id,s1_0.active,s1_0.created_at,s1_0.first_name,s1_0.full_name,s1_0.last_name,s1_0.updated_at from singers s1_0 where s1_0.last_name like $1 escape '\''

[amuware]

Hello,
we are facing the same issue:
@Query("SELECT r FROM DNSRecordEntity r WHERE LOWER(r.name) LIKE LOWER(?1) OR LOWER(r.irn) LIKE LOWER(?1)")

{"code":"500","message":"JDBC exception executing SQL ...ERROR: Postgres function like_escape(text, text) is not supported - Statement: 'select ... from ttdev de1_0 where lower(de1_0.name) like lower($1) escape '' or lower(de1_0.xx) like lower($2) escape '''] [n/a]","status":"Internal Server Error"}

is there any solution ?
Thanks

[olavloite]

It depends a bit on the use case. One possible option could be to use the starts_with function like in this example:

pgadapter/samples/java/spring-data-jpa/src/main/java/com/google/cloud/spanner/pgadapter/sample/repository/SingerRepository.java

Line 26 in 05a09bc

@Query("SELECT s FROM Singer s WHERE cast(starts_with(s.lastName, :lastName) as boolean)")

If that is not an option in your case, then I can have a look at adding a query replacement in PGAdapter. We already replace some known function calls and statements, so this could be added to that.

[hnarra7]

Hello @olavloite,

We encountered the same LIKE ESCAPE issue with PgAdapter and implemented a solution that transforms queries to remove the ESCAPE clause while preserving the intended behavior of LIKE statements.

Summary of Our Approach:
• Detection (isPotentialJdbcMetadataStatement()): Uses regex with capturing groups to identify queries containing LIKE ESCAPE patterns.
• Transformation (replaceJdbcMetadataStatement()): Removes the ESCAPE clause from queries using a targeted regex replacement.

Testing and Validation:
• Verified behavior with queries containing LIKE clauses (with and without ESCAPE).
• Confirmed compatibility with PgAdapter’s existing query transformations.

Seeking Your Guidance:
Is JdbcMetadataStatementHelper the appropriate class for implementing this fix?
Does our approach align with PgAdapter’s design principles?
Would you recommend submitting a pull request (PR) for this enhancement?

We look forward to your feedback and are happy to contribute further if this solution aligns with PgAdapter's current design.

Best regards,
Harshini Narra
American Express

[olavloite]

@hnarra7 Thanks for reaching out!

Seeking Your Guidance: Is JdbcMetadataStatementHelper the appropriate class for implementing this fix? Does our approach align with PgAdapter’s design principles?

JdbcMetadataStatementHelper is probably not the right place for this query replacement, as that class is only intended for replacing JDBC metadata queries (so queries generated by the DatabaseMetaData class in the PostgreSQL JDBC driver).

Would you recommend submitting a pull request (PR) for this enhancement?

Yes, I think that would be good for further discussion regarding where it would be best to place this replacement.