I have a nested select query with a GROUP BY and HAVING clause. Found this weird issue when using oracle mode. When there are more than one condition in a HAVING clause, connected by AND / OR and one of those conditions contains an aggregate function, that condition needs to be the last. Otherwise, H2 console throws syntax error. It works fine without any compatibility modes or even in Legacy mode.

Test case:
Table ABC

Compatibility mode=Oracle
select 1 AS one from ( select count(id) from abc group by name having sum(amount) <> 10 OR name <> 'c' OR name <> 'b');
Syntax error :

Syntax error in SQL statement "SELECT\000a COUNT(""ID"")\000aFROM ""PUBLIC"".""ABC""\000aGROUP BY ""NAME""\000aHAVING (""NAME"" <> 'c')\000a OR (""NAME"" <> 'b')\000a OR (SUM(""AMOUNT"") <> 10) [*]AS U&""(SUM(AMOUNT) <> 10)\000a OR (NAME <> 'c')\000a OR (NAME <> 'b')"""; SQL statement:
select 1 AS one from (
select count(id) from abc group by name having sum(amount) <> 10 OR name <> 'c' OR name <> 'b') [42000-212] 42000/42000 (Help)

select 1 AS one from ( select count(id) from abc group by name having name <> 'c' OR name <> 'b' OR sum(amount) <> 10); --> Works fine!

Both the queries work fine in mode=legacy, mode=mssqlserver and also without any modes.