On 8-9-2017 10:03, Dmitry Yemanov wrote:
All,
The key point of this standard feature is to allow sub-queries to
reference priorly defined contexts (in joins).
While thinking about this, I have a few questions to raise here. The
standard defines LATERAL for derived tables only. This sounds logical
but there are some corner cases to be discussed.
Isn't this all contained in the SQL specification?
1) LATERAL used when it doesn't make sense, e.g.
-- single context
FROM LATERAL (SELECT ...)
-- first context
FROM LATERAL (SELECT ...) JOIN T ...
In both cases, there's nothing priorly defined to be referenced to.
a) should be prohibited syntactically
b) should raise error at prepare
c) should raise warning at prepare
d) should be silently ignored
It might not make sense, but syntactically the SQL specification allows
this, and I can't find a rule that disallows it. As far as I can tell,
as a lateral derived table is only allowed to reference table factors
that are defined early, it simply devolves to a (simple) derived table
(at least that is how I read the access rule in <table reference> "If a
<derived table> or <lateral derived table> LDT simply containing <query
expression> QE is specified, then the result of LDT is the result of QE.".
See also the definition of <table primary> in the SQL standard and the
rules in section <table reference>.
2) LATERAL does not reference prior contexts
-- T is not referenced from inside the derived table
FROM T JOIN LATERAL (SELECT ...)
a) should raise error at prepare
b) should be ignored, but join order must be enforced (derived table is
executed per every row of T)
-- perhaps with a warning?
c) should be ignored, join order can be changed by the optimizer
-- perhaps with a warning?
Same as previous, should be allowed, it devolves to a simple derived
table. See also <joined table> rules. Also wording like "If TRB contains
a <lateral derived table> containing an outer reference that references
TRA, then <join type> shall not contain RIGHT or FULL." implies that
there can be TRB with a lateral derived table that does not have an
outer reference to TRA.
3) LATERAL was historically implied for joined stored procedures, e.g.
FROM T JOIN P(T.ID) ON 1=1
It used to produce invalid plan (and runtime errors) in older versions
but FB3 handles it properly by executing P after reading T.
a) keep status quo, disallow LATERAL syntax for procedures
b) keep status quo, allow LATERAL for procedures as syntax noise
(LATERAL is implied even if missing)
c) allow LATERAL for procedures, allow external references via
parameters only if LATERAL is specified (thus breaking legacy behaviour)
d) disallow LATERAL for procedures, require to be rewritten as
standard-friendly (thus breaking legacy behaviour):
FROM T JOIN LATERAL (SELECT ID FROM P(T.ID))
I suggest 3b
4) LATERAL in nested parenthesis joins, e.g.
FROM T1 JOIN LATERAL (T2 JOIN T3 ON T1.ID1 = T2.ID2 AND T2.ID3 = T3.ID3)
ON TRUE
a) should be allowed
b) should not be allowed
Not allowed by the syntax construction in the SQL specification:
<lateral derived table> ::=
LATERAL <table subquery>
<table subquery> ::=
<subquery>
<subquery> ::=
<left paren> <query expression> <right paren>
<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>
<explicit table> ::=
TABLE <table or query name>
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
Opinions, please.
My personal votes: 1d, 2c, 3a, 4b
BTW, I see no way to allow LATERAL in RIGHT or FULL outer joins, so it
should raise error.
"3) If TRB contains a <lateral derived table> containing an outer
reference that references TRA, then <join type> shall not contain RIGHT
or FULL." (see rules for <joined table>)
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
