[ 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-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org