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.
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
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?
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))
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
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.
Dmitry
------------------------------------------------------------------------------
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
