Syntax error when UNION subquery ("query primary") in parentheses
-----------------------------------------------------------------
Key: CORE-4577
URL: http://tracker.firebirdsql.org/browse/CORE-4577
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.3
Reporter: Lukas Eder
Priority: Minor
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 (https://github.com/jOOQ/jOOQ/issues/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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Meet PCI DSS 3.0 Compliance Requirements with EventLog Analyzer
Achieve PCI DSS 3.0 Compliant Status with Out-of-the-box PCI DSS Reports
Are you Audit-Ready for PCI DSS 3.0 Compliance? Download White paper
Comply to PCI DSS 3.0 Requirement 10 and 11.5 with EventLog Analyzer
http://pubads.g.doubleclick.net/gampad/clk?id=154622311&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
