Syntax error when UNION subquery ("query primary") in parentheses [CORE4577]

[firebird-automations]

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

[firebird-automations]

Commented by: Lukas Eder (lukas.eder)

For the record, the INTERSECT / EXCEPT feature request is here: CORE808

[Bart76]

Having issues like these open for such a long time makes me wonder... Are these features really low-prio? Do they really need to be 'sponsored' to make it into a release? Is it really true that nobody seems to actually need them? Or does everybody who do need such features simply switch to another DBMS?...

[mrotteveel]

I think this issue (the parentheses) was actually fixed through a different ticket for Firebird 5 (though I'd need to double check that).

That said, it is better to raise such questions on the firebird-devel mailing list than asking on an individual ticket.

[mrotteveel]

I think this was fixed with #6740

[Bart76]

Ah. If the issue is fixed in Firebird 5, that's great. Apologies for the confusion and thanks for the remark about that mailing list. I'll keep it in mind for the next time.

[dyemanov]

I can confirm that your example posted above works fine in FB5. So this issue may be closed as already fixed by #6740.