Hibernates adds `escape ''` to LIKE in PostgreSQL

Hibernate ORM
1

Hi,

I am using Spring Data JPA and I have the following query method in my Repository:

@Query("SELECT app FROM Application app WHERE app.lcName LIKE LOWER(:name)")
List<Application> findBySimilarName(@Param("name") String name);

The generated query is:

[2024-07-08 19:44:32,231] [DEBUG  ]: [main] [SqlStatementLogger.java::logStatement:135] select ... from applications a1_0 where a1_0.lcName like lower(?) escape ''

The escape '' is preventing me from escaping special chars.
I expected that if I don’t configure anything special, it should be escape '\' that matches PostgreSQL database or with no escape at all.

In older Hibernate (5.4.23.Final) the escape was not added at all and PostgreSQL defaulted to escape '\'.

I don’t want to explicitly add escape '\' in every query that uses LIKE.

The database is PostgreSQL version 13 and the dialect I am using is PostgreSQLDialect.

Using debugger, I see that org.hibernate.dialect.PostgreSQLSqlAstTranslator#visitLikePredicate is invoked with likePredicate that has likePredicate.getEscapeCharacter() == null.

I see that in org.hibernate.query.hql.internal.SemanticQueryBuilder#visitLikePredicate the ctx.likeEscape() is null but unsure why.

How can I configure the default escape char or prevent escape from being added.

Spring Data JPA 3.3.1
Hibernate 6.5.2.Final

2

This was discussed multiple times already. Please read through existing discussions: