Queries with LIKE-filters always return empty results with H2 database in Oracle Compatibility Mode
Description
Activity
Felix König March 1, 2023 at 10:39 AM
Thanks, explicitly setting the dialect to org.hibernate.dialect.OracleDialect instead of letting hibernate auto-detect it (it auto-detects org.hibernate.dialect.H2Dialect) fixes the issue!
Hibernate currently does not officially support H2's Oracle compatibility mode
I suppose this can be closed then, since my problem is caused by an unsupported usecase, correct?
Probably completely unrelated, but just for the record: Using the OracleDialect causes a different issues with timestamps being interpreted in the wrong timezone. I have already set spring.jpa.properties.hibernate.jdbc.time_zone=UTC and am using criteriaBuilder.literal(someOffsetDateTimeWithOffsetZero) in a query. When I set spring.jpa.show-sql=true I see the timestamp being submitted without a time zone, e.g. {ts '2021-01-02 00:00:00.000000'} (With the H2 dialect it was e.g. timestamp with time zone '2021-01-02 00:00:00.000000Z'). I was able to work around that issue by additionally setting the H2 timezone using ;TIME ZONE=UTC in the jdbc connection string.
Marco Belladelli February 28, 2023 at 3:59 PM
Are you using H2Dialect? Hibernate currently does not officially support H2's Oracle compatibility mode, but I believe using org.hibernate.dialect.OracleDialect should be enough to fix this specific issue.
Details
Details
Assignee
Reporter
Labels
Worked in
Components
Affects versions
Priority
Major
https://hibernate.atlassian.net/browse/HHH-15736 (merge request https://github.com/hibernate/hibernate-orm/pull/5746 ) introduced a regression for LIKE-queries in H2 databases running in ORACLE-compatibility mode.
Setting
escape ''successfully disables the escape character if running H2 in its default configuration. However, when running H2 in Oracle compatibility mode, H2 fails to return any results.I opened an issue with H2 (https://github.com/h2database/h2database/issues/3745 ), and it seems users are advised against attempting to use
escape ‘'in Oracle-compatibility mode. But starting with hibernate 6.1.7, this possibly illegal use ofescape ''is now the default behaviour.Workaround: Explicitly set a non-null escape character for LIKE-expressions, so the newly introduced special case does not get executed.
Possible solution: Detect whether H2 is running in Oracle Compatibility Mode, and skip the newly introduced special case then.