Incorrect escape '' added to SQL for H2 in Oracle Mode

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 :wink:

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