I’m currently migrating an application from Spring Boot 2.7 to 3 and thus from Hibernate 5.6 to 6.1 and stumbled upon a change in the SQL query generation that feels inconvenient to me. I also asked about this on StackOverflow but I’m now a little more into the topic. I’m using MariaDB 10.6 and the problem might be related to the MariaDB/MySQL dialects.
My use case is that I’m building select queries with where ... like clauses with the Criteria API in order to search for entities that match a given search string. In Hibernate 5.7, this generated queries like ... like ? whereas in Hibernate 6.1 the resulting SQL query is always something like ... like replace(?, '\\', '\\\\'). In my use case, that’s bad because I do not want backslashes to be escaped - I use backslashes to escape % and _ because I want to match fields that contain exactly my search string. When Hibernate escapes the backslashes, % and _ get unescaped again.
I dug into the source code a little bit and stumbled upon MariaDBSqlAstTranslator.visitLikePredicate. It seems like the only way preventing it from rendering like replace (...) is to manually override the escape symbol. Explicitly setting the escape symbol to \ when creating the Predicate does indeed work and creates a query similar to the old one, without a replace. But it feels inconvenient to me to have to manually specify an escape character that is the database default anyway.
Is this expected behaviour? If yes, could you point me to the rationale behind this design? I also think this change could be documented better because I didn’t find this mentioned in the migration guides.
Thanks for the quick response! Unfortunately upgrading to 6.2.0.CR4 doesn’t seem to solve my problem completely.
Currently, I generate the like predicate in the following way:
String searchString = "%something\_with\_underscores%";
Predicate p = criteriaBuilder.like(root.get("myColumn"), searchString);
This does work when I apply the following modifications:
Change searchString to criteriaBuilder.literal(searchString), because that makes pattern a Literal in the condition in AbstractSqlAstTranslator:7202. In this case, no “replace” is appended to the SQL. Otherwise it’s not a Literal but an SqmParameterInterpretation, which makes AbstractSqlAstTranslator append the “replace” part.
Set noBackslashEscapesEnabled to true in the MariaDB/MySQL dialect (false is the default). Because when set to false, AbstractSqlAstTranslator will escape the backslashes not in SQL but on the Java side.
Is this desired behaviour? It feels strange that cb.like(..., "abc") produces different results than cb.like(..., cb.literal("abc")). Also I think it should be documented that it is now default behaviour that backslashes are escaped by Hibernate, because that wasn’t the case earlier. Unfortunately I also didn’t find a way to modify noBackslashEscapesEnabled from Spring Boot in the first place, but that might be an issue with Spring Boot.
It feels strange that cb.like(..., "abc") produces different results than cb.like(..., cb.literal("abc"))
The former uses a JDBC parameter, whereas the latter renders as string literal. MySQL and MariaDB are very strange with respect to how literals are processed and on top of that, the LIKE pattern is specially escaped as well, so we tried our best to implement unified behavior for HQL/Criteria, so that you don’t have to worry about this madness. Also see [HHH-15736] - Hibernate JIRA
The SQL spec says that the LIKE pattern should be taken literally. Some databases violate that and introduce a default escape char. HQL tries to obey/follow the rules of the SQL spec.
