Submitted by: Lukas Eder (lukas.eder)

Votes: 2

The SQL:2011 standard specifies in 7.13 <query expression>

----------------------------------------------------------------------------------------
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

<query expression body> ::=
<query term>
| <query expression body> UNION [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>

<query term> ::=
<query primary>
| <query term> INTERSECT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query primary>

<query primary> ::=
<simple table>
| <left paren> <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ] <right paren>

<simple table> ::=
<query specification>
| <table value constructor>
| <explicit table>
----------------------------------------------------------------------------------------

As you can see, the <query primary> is allowed to wrap the <query expression body> in explicit parentheses, which is currently not possible in Firebird. I.e. the following valid query is not accepted by the Firebird parser:

----------------------------------------------------------------------------------------
select 'A' "x"
from "RDB$DATABASE"
union (
select 'A' "x"
from "RDB$DATABASE"
)
----------------------------------------------------------------------------------------

Many databases that I'm aware of allow for nesting set operators. In jOOQ, we're going to be working around this limitation by wrapping nested set operations in derived tables (jOOQ/jOOQ#3579), e.g. the following two queries are functionally equivalent:

----------------------------------------------------------------------------------------
SELECT 1
UNION (
SELECT 2
INTERSECT (
SELECT 2
UNION ALL
SELECT 3
)
)

SELECT 1
UNION
SELECT * FROM (
SELECT 2
INTERSECT
SELECT * FROM (
SELECT 2
UNION ALL
SELECT 3
)
)
----------------------------------------------------------------------------------------

This might be useful for a quick-win-implementation in the Firebird SQL parser, even if INTERSECT (and EXCEPT) are not yet supported. But such nesting is already useful when combining UNION with UNION ALL