The following query runs fine in PostgreSQL:
select ((select 1 x) except (select 1 y)) t;
H2 fails to parse it with the following exception:
Syntax error in SQL statement "SELECT ((SELECT 1 X) EXCEPT[*] (SELECT 1 Y)) T "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
select ((select 1 x) except (select 1 y)) t [42001-196]
As a workaround, this works:
select (select 1 x except (select 1 y)) t;
But if ORDER BY .. LIMIT would be required in the first set operation subquery, then there wouldn't be a reasonable solution, as the parentheses around that first subquery are now mandatory:
select ((select 1 x order by 1 limit 1) except (select 1 y)) t;