Details

    • Sub-task
    • Status: Closed
    • Major
    • Resolution: Won't Fix
    • 3.0.0
    • None
    • SQL
    • None

    Description

      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.

      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

      SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
      

      Feature ID: T491

      https://www.postgresql.org/docs/11/queries-table-expressions.html#QUERIES-FROM
      https://github.com/postgres/postgres/commit/5ebaaa49445eb1ba7b299bbea3a477d4e4c0430

      Attachments

        Issue Links

          Activity

            lishuming ShuMing Li added a comment -

            Can this issue assign to me?  Let me do it.

            lishuming ShuMing Li added a comment - Can this issue assign to me?  Let me do it.
            yumwang Yuming Wang added a comment -

            lishuming tickets are assigned only once the PR is merged. Please go ahead submitting the PR: https://github.com/apache/spark/pulls

            yumwang Yuming Wang added a comment - lishuming tickets are assigned only once the PR is merged. Please go ahead submitting the PR: https://github.com/apache/spark/pulls
            yumwang Yuming Wang added a comment -

            The lateral versus parent references case:

            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;
            

            Spark SQL:

            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
            ;
            

            PostgreSQL:

            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)
            
            yumwang Yuming Wang added a comment - The lateral versus parent references case: 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; Spark SQL: 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 ; PostgreSQL: 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)

            People

              Unassigned Unassigned
              yumwang Yuming Wang
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: