Yuming Wang created SPARK-28379:
-----------------------------------
Summary: ANSI SQL: LATERAL derived table(T491)
Key: SPARK-28379
URL: https://issues.apache.org/jira/browse/SPARK-28379
Project: Spark
Issue Type: Sub-task
Components: SQL
Affects Versions: 3.0.0
Reporter: Yuming Wang
Subqueries appearing in {{FROM}} can be preceded by the key word {{LATERAL}}.
This allows them to reference columns provided by preceding {{FROM}} items.
(Without {{LATERAL}}, each subquery is evaluated independently and so cannot
cross-reference any other {{FROM}} item.)
Table functions appearing in {{FROM}} can also be preceded by the key word
{{LATERAL}}, but for functions the key word is optional; the function's
arguments can contain references to columns provided by preceding {{FROM}}
items in any case.
A {{LATERAL}} item can appear at top level in the {{FROM}} list, or within a
{{JOIN}} tree. In the latter case it can also refer to any items that are on
the left-hand side of a {{JOIN}} that it is on the right-hand side of.
When a {{FROM}} item contains {{LATERAL}} cross-references, evaluation proceeds
as follows: for each row of the {{FROM}} item providing the cross-referenced
column(s), or set of rows of multiple {{FROM}} items providing the columns, the
{{LATERAL}} item is evaluated using that row or row set's values of the
columns. The resulting row(s) are joined as usual with the rows they were
computed from. This is repeated for each row or set of rows from the column
source table(s).
A trivial example of {{LATERAL}} is
{code:sql}
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
{code}
*Feature ID*: T491
https://www.postgresql.org/docs/11/queries-table-expressions.html#QUERIES-FROM
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
