- Added :meth:`.Select.lateral` and related constructs to allow

for the SQL standard LATERAL keyword, currently only supported
by Postgresql.  fixes #2857

4 files changed, 118 insertions, 0 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index f5c87d399..373fe4c3a 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -23,6 +23,18 @@
 
     .. change::
         :tags: feature, sql
+        :tickets: 2857
+
+        Added :meth:`.Select.lateral` and related constructs to allow
+        for the SQL standard LATERAL keyword, currently only supported
+        by Postgresql.
+
+        .. seealso::
+
+            :ref:`change_2857`
+
+    .. change::
+        :tags: feature, sql
         :tickets: 1957
         :pullreq: github:209
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index 11dc8a61f..13749b047 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -882,6 +882,40 @@ statement::
 
 :ticket:`2551`
 
+.. _change_2857:
+
+Support for the SQL LATERAL keyword
+------------------------------------
+
+The LATERAL keyword is currently known to only be supported by Postgresql 9.3
+and greater, however as it is part of the SQL standard support for this keyword
+is added to Core.   The implementation of :meth:`.Select.lateral` employs
+special logic beyond just rendering the LATERAL keyword to allow for
+correlation of tables that are derived from the same FROM clause as the
+selectable, e.g. lateral correlation::
+
+    >>> from sqlalchemy import table, column, select, true
+    >>> people = table('people', column('people_id'), column('age'), column('name'))
+    >>> books = table('books', column('book_id'), column('owner_id'))
+    >>> subq = select([books.c.book_id]).\
+    ...      where(books.c.owner_id == people.c.people_id).lateral("book_subq")
+    >>> print (select([people]).select_from(people.join(subq, true())))
+    SELECT people.people_id, people.age, people.name
+    FROM people JOIN LATERAL (SELECT books.book_id AS book_id
+    FROM books WHERE books.owner_id = people.people_id)
+    AS book_subq ON true
+
+.. seealso::
+
+    :ref:`lateral_selects`
+
+    :class:`.Lateral`
+
+    :meth:`.Select.lateral`
+
+
+:ticket:`2857`
+
 .. _change_3216:
 
 The ``.autoincrement`` directive is no longer implicitly enabled for a composite primary key column
diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst
index a582ab4dc..3f4d9565e 100644
--- a/doc/build/core/selectable.rst
+++ b/doc/build/core/selectable.rst
@@ -23,6 +23,8 @@ elements are themselves :class:`.ColumnElement` subclasses).
 
 .. autofunction:: join
 
+.. autofunction:: lateral
+
 .. autofunction:: outerjoin
 
 .. autofunction:: select
@@ -70,6 +72,8 @@ elements are themselves :class:`.ColumnElement` subclasses).
    :members:
    :inherited-members:
 
+.. autoclass:: Lateral
+
 .. autoclass:: ScalarSelect
    :members:
 
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 04262ac5e..0fd78abeb 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -1690,6 +1690,74 @@ by telling it to correlate all FROM clauses except for ``users``:
      ('jack',)
      {stop}[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]
 
+.. _lateral_selects:
+
+LATERAL correlation
+^^^^^^^^^^^^^^^^^^^
+
+LATERAL correlation is a special sub-category of SQL correlation which
+allows a selectable unit to refer to another selectable unit within a
+single FROM clause.  This is an extremely special use case which, while
+part of the SQL standard, is only known to be supported by recent
+versions of Postgresql.
+
+Normally, if a SELECT statement refers to
+``table1 JOIN (some SELECT) AS subquery`` in its FROM clause, the subquery
+on the right side may not refer to the "table1" expression from the left side;
+correlation may only refer to a table that is part of another SELECT that
+entirely encloses this SELECT.  The LATERAL keyword allows us to turn this
+behavior around, allowing an expression such as:
+
+.. sourcecode:: sql
+
+    SELECT people.people_id, people.age, people.name
+    FROM people JOIN LATERAL (SELECT books.book_id AS book_id
+    FROM books WHERE books.owner_id = people.people_id)
+    AS book_subq ON true
+
+Where above, the right side of the JOIN contains a subquery that refers not
+just to the "books" table but also the "people" table, correlating
+to the left side of the JOIN.   SQLAlchemy Core supports a statement
+like the above using the :meth:`.Select.lateral` method as follows::
+
+    >>> from sqlalchemy import table, column, select, true
+    >>> people = table('people', column('people_id'), column('age'), column('name'))
+    >>> books = table('books', column('book_id'), column('owner_id'))
+    >>> subq = select([books.c.book_id]).\
+    ...      where(books.c.owner_id == people.c.people_id).lateral("book_subq")
+    >>> print (select([people]).select_from(people.join(subq, true())))
+    SELECT people.people_id, people.age, people.name
+    FROM people JOIN LATERAL (SELECT books.book_id AS book_id
+    FROM books WHERE books.owner_id = people.people_id)
+    AS book_subq ON true
+
+Above, we can see that the :meth:`.Select.lateral` method acts a lot like
+the :meth:`.Select.alias` method, including that we can specify an optional
+name.  However the construct is the :class:`.Lateral` construct instead of
+an :class:`.Alias` which provides for the LATERAL keyword as well as special
+instructions to allow correlation from inside the FROM clause of the
+enclosing statement.
+
+The :meth:`.Select.lateral` method interacts normally with the
+:meth:`.Select.correlate` and :meth:`.Select.correlate_except` methods, except
+that the correlation rules also apply to any other tables present in the
+enclosing statement's FROM clause.   Correlation is "automatic" to these
+tables by default, is explicit if the table is specified to
+:meth:`.Select.correlate`, and is explicit to all tables except those
+specified to :meth:`.Select.correlate_except`.
+
+
+.. versionadded:: 1.1
+
+    Support for the LATERAL keyword and lateral correlation.
+
+.. seealso::
+
+    :class:`.Lateral`
+
+    :meth:`.Select.lateral`
+
+
 Ordering, Grouping, Limiting, Offset...ing...
 ---------------------------------------------