The issue is already described in this issue:
https://hibernate.atlassian.net/browse/HHH-16277
This issue has been closed incorrectly as a duplicate does anyone know how to reopen it?
Hibernate ORM simply does not support H2 compatibility modes, but why would you even want to use that?
Ok, that’s inconvenient then that we have chosen Hibernate for our project.
Maybe I don’t understand your question, we are using H2 for testing but want it to behave like Oracle which is what we use when not testing but this is already implicit in the question above. If I turn off oracle mode then we immediately start getting issues with date times. Are you suggesting we turn off compatibility mode and find fixes for the other issues? (to overcome the fact that Hibernate doesn’t provide a way to modify the additional code it’s injecting into our queries?)
Ok, that’s inconvenient then that we have chosen Hibernate for our project.
If your project relies on H2 compatibility modes to just work with out of the box Hibernate ORM, then yes, wrong choice, but I doubt that, so let’s stay real 
I would suggest you to use Testcontainers and test your app on a real Oracle database. Regardless of the compatibility mode, testing on H2 is not really testing the real thing, so you might as well test without the Oracle compatibility mode as Hibernate already abstracts over the database differences quite a lot.
If I turn off oracle mode then we immediately start getting issues with date times.
I don’t know what issues you have, but if you have a question or concern that this could be a Hibernate ORM bug then please create a new topic for that.
Are you suggesting we turn off compatibility mode and find fixes for the other issues? (to overcome the fact that Hibernate doesn’t provide a way to modify the additional code it’s injecting into our queries?)
First off, Hibernate ORM isn’t “injecting” code, it actually makes sure that your queries behave the same way on all databases. The fact that the compatibility mode of H2 is a half baked thing is the problem.
You could switch to the OracleDialect and configure an explicit database version for the dialect to render only Oracle SQL, but then H2 will probably choke because the compatibility mode is not perfect.
Or you provide a custom Dialect/SqlAstTranslator that extends the H2Dialect/H2SqlAstTranslator and change the implementation of org.hibernate.dialect.H2SqlAstTranslator#visitLikePredicate to not generate that escape '' part.
Hi Beikov,
Thanks for your detailed update. Maybe I can explore the final comment about the SqlAstTranslator.
I would still say that Hibernate is injecting the escape ‘’ though and that it should provide a mechanism to not do this or to change the character it’s adding in.
Unfortunately the software is an older piece of software so it’s of a good size with many attached libraries. The issue here is that we needed to update to Spring Boot 3 which in turn brings in Hibernate 6 so this isn’t unfortunately a case of quickly swapping in H2 for Oracle and everything starts to work nicely without days of work. I can see online that multiple companies online are suffering the same problem.
Although I appreciate Hibernate can’t guarantee compatibility with all databases and configurations I feel it should be providing a mechanism to easily control choices it has made that make significant changes. I would consider making unrequested changes to the SQL a significant change.
I hope that you can reopen this bug and fix this issue. In the meantime though I do thank you for your explanation and will see if I can find a fix to get my application working again in a reasonable time with Spring Boot 3.
If your project depends on H2 compatibility modes to function seamlessly with Hibernate ORM without any additional configurations, it might not be the right choice. H2 doesn’t precisely replicate real-world scenarios, so it’s advisable to conduct testing without the Oracle compatibility mode. Hibernate is already designed to handle various database differences effectively. However, when Oracle mode is disabled, issues with date and time may arise.
Although I appreciate Hibernate can’t guarantee compatibility with all databases and configurations I feel it should be providing a mechanism to easily control choices it has made that make significant changes. I would consider making unrequested changes to the SQL a significant change.
You asked Hibernate ORM to implement a like predicate with no escape char and Hibernate ORM ensures that no escape character is interpreted in the resulting SQL, so this is not “injecting” anything but just correctly implementing the semantics of the predicate. If you can’t understand that this is just how it is supposed to work then I’m sorry, but I don’t know how else to explain it. Some databases just chose to deviate their defaults from the SQL standard and Hibernate ORM tries to heal the rift by implementing the same semantics everywhere.
it should be providing a mechanism to easily control choices
It does provide a mechanism to alter this. Write a custom SqlAstTranslator if you want to rely on broken behavior.
Just know that a SQL predicate like col like '%'||?||'%' where ? is a search string will break your contains semantics if the parameter value ends with a \, because \ is the default escape character on H2, MySQL and PostgreSQL. I doubt that you want that behavior, which is why Hibernate ORM appends escape ''. That’s just how this default escape character is reset on H2.
You are using H2 with a compatibility mode, so the semantics of SQL changes. If you do such a non-standard thing which is not supported by Hibernate ORM then you will have to simply do something extra.
If you can’t understand that this is just how it is supposed to work then I’m sorry, but I don’t know how else to explain it.
Maybe I can help you here because some people do struggle with the difference between null and empty. What Hibernate had before was the implementation of null, the absence of the declaration of the escape char. Now it has the implementation of empty. They are quite different, here’s a link that describes it in a little more details https://www.baeldung.com/java-string-null-vs-empty. I understand that a lot of developers struggle between the two.
The fact is Hibernate is injecting this escape code and that it doesn’t provide an easy mechanism to turn it off. If you don’t want to provide a means just say so, I can’t see how trying to say Hibernate doesn’t inject it or I don’t understand the problem helps anyone. This is a breaking change that is causing business issues, I’m just letting you know.
The fact is Hibernate is injecting this escape code and that it doesn’t provide an easy mechanism to turn it off. If you don’t want to provide a means just say so, I can’t see how trying to say Hibernate doesn’t inject it or I don’t understand the problem helps anyone. This is a breaking change that is causing business issues, I’m just letting you know.
I’m telling you one last time, Hibernate ORM uses whatever mechanism necessary for a database i.e. escape '' to reset the default escape character in order to retain the semantics of the HQL like predicate, which is to not escape any of the special wildcard characters ? and %.
If you want to use a specific escape character, then just add your custom escape clause col like :param escape '\'. That way at least you will consciously decide what char to use for escaping which will be respected by all databases.
You can view this however you like, to the Hibernate ORM team it’s a bug fix. People have means to get the behavior they want. I understand that your use of the H2 compatibility mode is the source of your trouble and can only tell you again that Hibernate ORM does not support that. If you want to support that, you can implement a custom SqlAstTranslator for that purpose.
Had a similar issue, per workaround suggested,
I created one class H2SqlAstTranslatorOverrider as a replacement for H2SqlAstTranslator which has exactly the same implementation except visitLikePredicate method. The new class does not have these lines in visitLikePredicate method
if ( likePredicate.getEscapeCharacter() == null ) {
appendSql( " escape ''" );
}
Second another separate class, H2DialectOverrider that extends H2Dialect, and overriding getSqlAstTranslatorFactory() and returning the custom H2SqlAstTranslatorOverrider with updated visitLikePredicate
@Override
public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
return new StandardSqlAstTranslatorFactory() {
@Override
protected SqlAstTranslator buildTranslator(
SessionFactoryImplementor sessionFactory, Statement statement) {
return new H2SqlAstTranslatorOverrider(sessionFactory, statement);
}
};
}
Then pass this new Dialect in application-test property spring.jpa.properties.hibernate.dialect=com.**.H2DialectOverrider
Another solution for this issue is to:
- Create a subclass of H2SqlAstTranslator
- Override visitLikePredicate() to provide a mutated LikePredicate with an escape character of '' (H2 default when in Oracle compatibility mode)
- Create a subclass of H2Dialect and set spring.jpa.properties.hibernate.dialect or hibernate.dialect to the FQDN of your subclass
- Override the dialect’s getSqlAstTranslatorFactory() function to return an instance of your new translator
This will result in all LIKE queries being appended with escape '\' instead of escape ''.
An example is available at GitHub - steveperkins/hibernate6-h2-like-fix: Fix for bug in Hibernate 6 appending `escape ''` to LIKE conditions when using H2's Oracle compatibility mode.
That’s not a solution since this changes the semantics of the HQL like predicate for just H2.
The problem OP has is “Incorrect escape ” added to SQL for H2 in Oracle Mode”. Changing the semantics of the HQL LIKE predicate just for H2 is what they are trying to do.
I have the same problem. AbstractSqlAstTranslator.visitLikePredicate() appends " escape " to the SQL string if the likePredicate.getEscapeCharacter() Expression object is non-null (even if the escape character string itself is is null/empty). The H2SqlAstTranslator subclass’ visitLikePredicate() appends the hardcoded " escape ''" if the likePredicate.getEscapeCharacter() Expression object is null. So the root problem is that “escape” is appended to the SQL string regardless of whether an escape character has been specified, making it impossible to omit the escape clause even if the desired behavior is to use the DB’s default escape character.
In H2’s Oracle compatibility mode, an empty string is not a valid escape character, so a SQL error is thrown, the query fails silently, and zero results are returned. So for those of us using this mode, the escape clause must be set to the default escape character (\) in order to prevent this issue from causing LIKE queries to fail.
The solution I presented solves this problem, as does @leandremucyo’s solution of creating a copy of the H2SqlAstTranslator class and removing the visitLikePredicate() override so the hardcoded " escape ‘’" isn’t appended to the SQL string if no escape character is provided. With the solution I presented, any future updates/bugfixes to H2SqlAstTranslator will also be applied automatically without any manual intervention.
even if the desired behavior is to use the DB’s default escape character.
Relying on the “default” is exactly what we want to avoid. People should program against the HQL semantics, which try to match the SQL standard. The LIKE predicate, by default, has no escape character for the pattern. To implement this behavior, on H2, one needs to specify escape ''. Now for some reason, H2 does not support an empty string when running in Oracle compatibility mode, because Oracle treats empty strings like NULL, and this is fine.
The problem is that people try to use the H2Dialect when the underlying database tries to mimic Oracle. If you want to test Oracle specifics, then use an Oracle database, it’s as simple as that. If the OracleDialect doesn’t work with H2 in its Oracle compatibility mode, then go complain on their forum about the feature that is missing, or simply don’t use the Oracle compatibility mode, since it’s probably a bad way of testing Oracle specifics anyway.
Your solution just changes the semantics for every like predicate on H2, so if you run that same application on a different database (with a different SqlAstTranslator), you might see different results, because all the other SqlAstTranslator implementations ensure that by default, there is no escape character.
@beikov friend, I’m not complaining. I provided a solution to a problem I’m having that others are also having. The problem is that H2Dialect against H2 in Oracle compatibility mode behaves unexpectedly for LIKE queries. I’m not planning to suddenly switch databases or switch away from Oracle compatibility mode. I want the LIKE semantics for all queries against H2 to be altered in such a way that result sets aren’t incorrectly empty because Hibernate swallows the SQL error, and this solution does that.
I don’t understand why your responses across this forum are so combative. There is a problem for a stated use case. Two solutions to the problem have been posted so others in the same situation can benefit. That’s all there is to it.
I provided a solution to a problem I’m having that others are also having.
I’m not planning to suddenly switch databases or switch away from Oracle compatibility mode. I want the LIKE semantics for all queries against H2 to be altered in such a way that result sets aren’t incorrectly empty because Hibernate swallows the SQL error, and this solution does that.
It’s fine, I just want to make clear that people who read this thread understand the implications of such a change, because most people use H2 just for testing.
The problem is that H2Dialect against H2 in Oracle compatibility mode behaves unexpectedly for LIKE queries.
The problem is that Hibernate ORM does not support compatibility modes of H2, yet people still try to use that.