Hinnerk Oetting ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5c87511... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYTExZjBkYWQx... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16277?atlOrigin=eyJpIjoiYTExZj... ) HHH-16277 ( https://hibernate.atlassian.net/browse/HHH-16277?atlOrigin=eyJpIjoiYTExZj... ) LIKE Queries not working anymore in H2database with oracle compatibility mode ( https://hibernate.atlassian.net/browse/HHH-16277?atlOrigin=eyJpIjoiYTExZj... ) Issue Type: Bug Affects Versions: 6.1.7 Assignee: Unassigned Components: hibernate-core Created: 09/Mar/2023 07:24 AM Environment: Java 17 Hibernate 6.1.7 spring-data 3.0.4 Priority: Major Reporter: Hinnerk Oetting ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5c87511... ) *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/4d75202f0cff2843ab9b03e... ( https://github.com/hibernate/hibernate-orm/commit/4d75202f0cff2843ab9b03e... ) 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 ( 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 ( 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. ( https://hibernate.atlassian.net/browse/HHH-16277#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16277#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=Em... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100217- sha1:e5bf00f )