[jira] [Commented] (SPARK-27877) ANSI SQL: LATERAL derived table(T491)

["Yuming Wang (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/SPARK-27877?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16884552#comment-16884552 ] 

Yuming Wang commented on SPARK-27877:
-------------------------------------

The lateral versus parent references case:
{code:sql}
create or replace temporary view INT8_TBL as select * from
  (values (123, 456),
  (123, 4567890123456789),
  (4567890123456789, 123),
  (4567890123456789, 4567890123456789),
  (4567890123456789, -4567890123456789))
  as v(q1, q2);
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
{code}
Spark SQL:
{noformat}
select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl
-- !query 235 schema
struct<>
-- !query 235 output
org.apache.spark.sql.AnalysisException
Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Project [outer(q1#xL) AS q2#xL]
+- OneRowRelation
;
{noformat}

PostgreSQL:
{noformat}
postgres=# select *, (select r from (select q1 as q2) x, (select q2 as r) y) from int8_tbl;
        q1        |        q2         |         r
------------------+-------------------+-------------------
              123 |               456 |               456
              123 |  4567890123456789 |  4567890123456789
 4567890123456789 |               123 |               123
 4567890123456789 |  4567890123456789 |  4567890123456789
 4567890123456789 | -4567890123456789 | -4567890123456789
(5 rows)
{noformat}



> ANSI SQL: LATERAL derived table(T491)
> -------------------------------------
>
>                 Key: SPARK-27877
>                 URL: https://issues.apache.org/jira/browse/SPARK-27877
>             Project: Spark
>          Issue Type: Sub-task
>          Components: SQL
>    Affects Versions: 3.0.0
>            Reporter: Yuming Wang
>            Priority: Major
>
> 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]
> [https://github.com/postgres/postgres/commit/5ebaaa49445eb1ba7b299bbea3a477d4e4c0430]



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org