Problem

We are trying to update to hibernate 6.1.7 and found an issue when running LIKE queries with our h2-database in oracle compatibility mode. Queries do not return any result at all.

Details

Since this change https://github.com/hibernate/hibernate-orm/commit/4d75202f0cff2843ab9b03ecb28d449b3a0b3432#diff-dda03febd9932eea221fb54e52af18ec794de6fb02e8c079e55a3646f3e6cd23 hibernate renders ESCAPE '' behind each LIKE statement.

So what was previously

select u1_0.id,u1_0.name from users u1_0 where u1_0.name like ?

became

select u1_0.id,u1_0.name from users u1_0 where u1_0.name like ? escape ''

This works with the h2database in regular mode, but unfortunately it does not work in oracle mode (SET MODE oracle;). Every statement that is using {{ escape ''}} will return no result at all.

An issue for the h2database was created, but they do not want to fix it (escape '' is invalid for Oracle, see https://github.com/h2database/h2database/issues/3745 )

I understand why it that change in hibernate was made, but it would be great if a change would be made so that we can configure hibernate to not render escape '' in our case.

Workaround

As a workaround we are currently adding ESCAPE ‘
’ to all LIKE statements but that is quite ugly.

For example we are changing

@Query("from User where name LIKE %:name% ")

to

@Query("from User where name LIKE %:name% ESCAPE '\\'")

Also since we are using Oracle on production, it slightly changes our queries (Oracle does not use an Escape character by default) but that is probably not the biggest issue.

Reproduce

I created a minimal testcase for this issue here

https://github.com/hinnerkoetting/hibernate-h2-like-escape-issue

Run it by calling

./gradlew test

It contains two testcases. One testcase runs the h2-database in regular mode, one testcase in oracle mode which is currently failing.