Disabling default escape character (ESCAPE '') causes no results in Oracle compatibility mode

[Felk]

The documentation for LIKE states the following:

To select no escape character, use ESCAPE '' (empty string)

This works fine with default settings. However, in Oracle compatibility mode the query always returns an empty result. Possibly because:

Empty strings are treated like NULL values

---

My test setup:

start a h2 in docker using docker run -d -p 1521:1521 -e H2_OPTIONS=-ifNotExists --name=MyH2Instance oscarfonts/h2 (This runs a H2 in version 2.1.210). Connect to it using jdbc:h2:tcp://localhost:1521/test with username sa and no password.

create some test data:

create table test(
    str varchar not null
);

insert into test(str) values ('foo');
insert into test(str) values ('bar');
insert into test(str) values ('foobar');

Then perform these queries:

select * from test where str like '%foo%';
select * from test where str like '%foo%' escape '';

Both return this result:

+--------+
|  STR   |
+--------+
| foo    |
| foobar |
+--------+

If I connect using the jdbc string jdbc:h2:tcp://localhost:1522/test;mode=Oracle (note the ;mode=Oracle) instead, Only the first query returns the expected result. The second query returns an empty result.

[katzyn]

ESCAPE '' is a database-specific extension to the LIKE predicate, this extension isn't expected to be compatible with all other database systems.

The SQL Standard allows only null value and strings with exactly one character (or octet for binary version) to be specified here. If null value is specified, this predicate must return UNKNOWN (NULL). If a string with length different from 1 is specified, database should raise an error.

Oracle allows only strings with length of 1, it doesn't allow NULL or '' to be used here, so you shouldn't try to use them in Oracle compatibility mode of H2 too.

[Felk]

That makes sense, thanks!

If a string with length different from 1 is specified, database should raise an error.
Oracle allows only strings with length of 1, it doesn't allow NULL or '' to be used here, so you shouldn't try to use them in Oracle compatibility mode of H2 too.

May I suggest that H2 then throws an error when attempting to use ESCAPE '' in Oracle-compatibility mode (or any non-compatible mode)? It sounds a bit better to throw an error than to do the wrong thing, even if due to misuse of a feature.

[katzyn]

You cannot use H2 to check correctness of your queries for other database systems.

H2 doesn't know whether '' or NULL was used in Oracle compatibility mode. And NULL is a perfectly valid value here for every standard-compliant database system. H2 intentionally almost always allows standard features in all compatibility modes due to various reasons (sometimes they are needed for H2 itself, sometimes third-party database systems begin to accept them too in their newer versions).

[Felk]

H2 doesn't know whether '' or NULL was used in Oracle compatibility mode. And NULL is a perfectly valid value here for every standard-compliant database system.

Okay, that changes things a bit then. Thanks for your insights!