like 'test%' with escape '' not working in MODE=ORACLE

[Bastian Linneweber]

Hi all,

I am a bit unsure if this is the right place here to raise a potential bug in h2 (found in h2.1.200, h2.2.214 and the most recent h2.2.222):

a very straight forward 

select * from test_table where

 column1 like 'test%' escape ''

returns values (when appropriate) when MODE is not ORACLE but always returns an empty record set for MODE=ORACLE. This might be explainable due to the '' / empty string behavior in MODE=ORACLE - but it is quite problematic w/r to the following:

* the result set is completely different depending on the MODE

* ORA databases themselves do not allow for ESCAPE '' (ORA-01425)

* the H2 documentation says otherwise (escape '' deactivates escaping - no MODE specific behavior is mentioned)

* and probably the worst: as far as I can see, hibernate 6.x is now automatically generating SQL when LIKE is used adding escape '' to each query. I have not found a switch to deactivate that - hence you can currently not combine hibernate 6.x + h2 in ORACLE mode with non-native LIKE queries.

I have found much older threads here (like https://groups.google.com/g/h2-database/c/OQ7tXiFYT6o and https://groups.google.com/g/h2-database/c/zdqGA5cw3fM/m/C0J60SPjDfEJ but no answer)

 

Any ideas or thoughts are much appreciated.

Thanks and Cheers

Bastian

[Evgenij Ryazanov]

Hello!

This issue cannot be resolved on H2 side.

ESCAPE '' is not valid according to the SQL Standard:
> If the length in characters of ECV is not equal to 1, then an exception condition is raised:
> data exception — invalid escape character.
Oracle throws an exception as expected:

ORA-01425: escape character must be character string of length 1

H2 allows ESCAPE '' with a special meaning, but this extension is not portable across all compatibility modes, because in Oracle compatibility mode (just like in the real Oracle) an empty character string and NULL aren't distinct from each other and it isn't possible to distinguish them here.

This issue was already reported in bugtracker of Hibernate ORM:
https://hibernate.atlassian.net/browse/HHH-16277
It was closed as a duplicate of another issue, but I think it isn't a duplicate and should be re-opened.

[Marcus]

Hello,

I came along here having the same problem. Setting H2 to Oracle mode doesn't make Hibernate detect "Oracle" dialect, still uses "H2" dialect, and in H2 dialect it keeps putting this damned "ESCAPE ''" sequence. We have two parties which say it's the other side's problem, no improvement to come.

But my question is: if H2 says "ESCAPE ''" is not a valid syntax in Oracle mode, shouldn't it then at least give an error message instead of simply returning no results? An error message would make it much much easier to spot the bug and create a workaround.

Regards

Marcus.

[Noel Grandin]

On Sun, 12 Jan 2025 at 16:21, Marcus <msch...@gmail.com> wrote:

I came along here having the same problem. Setting H2 to Oracle mode doesn't make Hibernate detect "Oracle" dialect, still uses "H2" dialect, 

You can tell Hibernate to explicitly use a specific dialect.

[Marcus]

Noel Grandin schrieb am Sonntag, 12. Januar 2025 um 16:27:15 UTC+1:

You can tell Hibernate to explicitly use a specific dialect.

Of course, this is what I eventually did to solve this. But it took me a long time to find out that this completely unsuspicious 'LIKE' clause was the cause that the rather complex HQL suddenly stopped working (after H2 update). If H2 would simply have complained about this syntax error in Oracle mode, rather than giving an empty result, it would have saved me a lot of time.

And, to be honest, giving an empty result because of a syntax error in the query sound like a bug to me.

[Noel Grandin]

On 1/14/2025 10:25 AM, Marcus wrote:
>
> And, to be honest, giving an empty result because of a syntax error in the query sound like a bug to me.
>

You are welcome to submit a PR to improve this.