- rework of correlation, continuing on #2668, #2746

- add support for correlations to propagate all the way in; because
correlations require context now, need to make sure a select enclosure
of any level takes effect any number of levels deep.
- fix what we said correlate_except() was supposed to do when we first
released #2668 - "the FROM clause is left intact if the correlated SELECT
is not used in the context of an enclosing SELECT..." - it was not
considering the "existing_froms" collection at all, and prohibited
additional FROMs from being placed in an any() or has().
- add test for multilevel any()
- lots of docs, including glossary entries as we really need to define
"WHERE clause", "columns clause" etc. so that we can explain correlation better
- based on the insight that a SELECT can correlate anything that ultimately
came from an enclosing SELECT that links to this one via WHERE/columns/HAVING/ORDER BY,
have the compiler keep track of the FROM lists that correspond in this way,
link it to the asfrom flag, so that we send to _get_display_froms() the exact
list of candidate FROMs to correlate.  no longer need any asfrom logic in the
Select() itself
- preserve 0.8.1's behavior for correlation when no correlate options are given, not
to mention 0.7 and prior's behavior of not propagating implicit correlation more than one level..
this is to reduce surprises/hard-to-debug situations when a user isn't trying
to correlate anything.

4 files changed, 216 insertions, 5 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 8fc9a0cc2..ba2450d55 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -7,6 +7,41 @@
     :version: 0.8.2
 
     .. change::
+        :tags: bug, sql
+        :tickets: 2746, 2668
+
+        Multiple fixes to the correlation behavior of
+        :class:`.Select` constructs, first introduced in 0.8.0:
+
+        * To satisfy the use case where FROM entries should be
+          correlated outwards to a SELECT that encloses another,
+          which then encloses this one, correlation now works
+          across multiple levels when explicit correlation is
+          established via :meth:`.Select.correlate`, provided
+          that the target select is somewhere along the chain
+          contained by a WHERE/ORDER BY/columns clause, not
+          just nested FROM clauses. This makes
+          :meth:`.Select.correlate` act more compatibly to
+          that of 0.7 again while still maintaining the new
+          "smart" correlation.
+
+        * When explicit correlation is not used, the usual
+          "implicit" correlation limits its behavior to just
+          the immediate enclosing SELECT, to maximize compatibility
+          with 0.7 applications, and also prevents correlation
+          across nested FROMs in this case, maintaining compatibility
+          with 0.8.0/0.8.1.
+
+        * The :meth:`.Select.correlate_except` method was not
+          preventing the given FROM clauses from correlation in
+          all cases, and also would cause FROM clauses to be incorrectly
+          omitted entirely (more like what 0.7 would do),
+          this has been fixed.
+
+        * Calling `select.correlate_except(None)` will enter
+          all FROM clauses into correlation as would be expected.
+
+    .. change::
         :tags: bug, ext
 
         Fixed bug whereby if a composite type were set up
diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 88c12b251..abf0fde41 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -7,6 +7,43 @@
     :version: 0.9.0
 
     .. change::
+        :tags: bug, sql
+        :tickets: 2746, 2668
+
+        Multiple fixes to the correlation behavior of
+        :class:`.Select` constructs, first introduced in 0.8.0:
+
+        * To satisfy the use case where FROM entries should be
+          correlated outwards to a SELECT that encloses another,
+          which then encloses this one, correlation now works
+          across multiple levels when explicit correlation is
+          established via :meth:`.Select.correlate`, provided
+          that the target select is somewhere along the chain
+          contained by a WHERE/ORDER BY/columns clause, not
+          just nested FROM clauses. This makes
+          :meth:`.Select.correlate` act more compatibly to
+          that of 0.7 again while still maintaining the new
+          "smart" correlation.
+
+        * When explicit correlation is not used, the usual
+          "implicit" correlation limits its behavior to just
+          the immediate enclosing SELECT, to maximize compatibility
+          with 0.7 applications, and also prevents correlation
+          across nested FROMs in this case, maintaining compatibility
+          with 0.8.0/0.8.1.
+
+        * The :meth:`.Select.correlate_except` method was not
+          preventing the given FROM clauses from correlation in
+          all cases, and also would cause FROM clauses to be incorrectly
+          omitted entirely (more like what 0.7 would do),
+          this has been fixed.
+
+        * Calling `select.correlate_except(None)` will enter
+          all FROM clauses into correlation as would be expected.
+
+        Also in 0.8.2.
+
+    .. change::
         :tags: bug, ext
 
         Fixed bug whereby if a composite type were set up
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index fd6c69bff..0203248ae 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -1357,6 +1357,29 @@ as the argument:
     ('wendy',)
     {stop}[(u'wendy',)]
 
+We can also control correlation via exclusion, using the :meth:`.Select.correlate_except`
+method.   Such as, we can write our SELECT for the ``users`` table
+by telling it to correlate all FROM clauses except for ``users``:
+
+.. sourcecode:: pycon+sql
+
+    >>> stmt = select([users.c.id]).\
+    ...             where(users.c.id == addresses.c.user_id).\
+    ...             where(users.c.name == 'jack').\
+    ...             correlate_except(users)
+    >>> enclosing_stmt = select(
+    ...         [users.c.name, addresses.c.email_address]).\
+    ...     select_from(users.join(addresses)).\
+    ...     where(users.c.id == stmt)
+    >>> conn.execute(enclosing_stmt).fetchall()  # doctest: +NORMALIZE_WHITESPACE
+    {opensql}SELECT users.name, addresses.email_address
+     FROM users JOIN addresses ON users.id = addresses.user_id
+     WHERE users.id = (SELECT users.id
+     FROM users
+     WHERE users.id = addresses.user_id AND users.name = ?)
+     ('jack',)
+     {stop}[(u'jack', u'jack@yahoo.com'), (u'jack', u'jack@msn.com')]
+
 Ordering, Grouping, Limiting, Offset...ing...
 ---------------------------------------------
 
diff --git a/doc/build/glossary.rst b/doc/build/glossary.rst
index afdc35eda..564668691 100644
--- a/doc/build/glossary.rst
+++ b/doc/build/glossary.rst
@@ -263,6 +263,102 @@ Glossary
 
             :doc:`orm/session`
 
+    columns clause
+        The portion of the ``SELECT`` statement which enumerates the
+        SQL expressions to be returned in the result set.  The expressions
+        follow the ``SELECT`` keyword directly and are a comma-separated
+        list of individual expressions.
+
+        E.g.:
+
+        .. sourcecode:: sql
+
+            SELECT user_account.name, user_account.email
+            FROM user_account WHERE user_account.name = 'fred'
+
+        Above, the list of columns ``user_acount.name``,
+        ``user_account.email`` is the columns clause of the ``SELECT``.
+
+    WHERE clause
+        The portion of the ``SELECT`` statement which indicates criteria
+        by which rows should be filtered.   It is a single SQL expression
+        which follows the keyword ``WHERE``.
+
+        .. sourcecode:: sql
+
+            SELECT user_account.name, user_account.email
+            FROM user_account
+            WHERE user_account.name = 'fred' AND user_account.status = 'E'
+
+        Above, the phrase ``WHERE user_account.name = 'fred' AND user_account.status = 'E'``
+        comprises the WHERE clause of the ``SELECT``.
+
+    FROM clause
+        The portion of the ``SELECT`` statement which incicates the initial
+        source of rows.
+
+        A simple ``SELECT`` will feature one or more table names in its
+        FROM clause.  Multiple sources are separated by a comma:
+
+        .. sourcecode:: sql
+
+            SELECT user.name, address.email_address
+            FROM user, address
+            WHERE user.id=address.user_id
+
+        The FROM clause is also where explicit joins are specified.  We can
+        rewrite the above ``SELECT`` using a single ``FROM`` element which consists
+        of a ``JOIN`` of the two tables:
+
+        .. sourcecode:: sql
+
+            SELECT user.name, address.email_address
+            FROM user JOIN address ON user.id=address.user_id
+
+
+    subquery
+        Refers to a ``SELECT`` statement that is embedded within an enclosing
+        ``SELECT``.
+
+        A subquery comes in two general flavors, one known as a "scalar select"
+        which specifically must return exactly one row and one column, and the
+        other form which acts as a "derived table" and serves as a source of
+        rows for the FROM clause of another select.  A scalar select is eligble
+        to be placed in the :term:`WHERE clause`, :term:`columns clause`,
+        ORDER BY clause or HAVING clause of the enclosing select, whereas the
+        derived table form is eligible to be placed in the FROM clause of the
+        enclosing ``SELECT``.
+
+        Examples:
+
+        1. a scalar subquery placed in the :term:`columns clause` of an enclosing
+           ``SELECT``.  The subquery in this example is a :term:`correlated subquery` because part
+           of the rows which it selects from are given via the enclosing statement.
+
+           .. sourcecode:: sql
+
+            SELECT id, (SELECT name FROM address WHERE address.user_id=user.id)
+            FROM user
+
+        2. a scalar subquery placed in the :term:`WHERE clause` of an enclosing
+           ``SELECT``.  This subquery in this example is not correlated as it selects a fixed result.
+
+           .. sourcecode:: sql
+
+            SELECT id, name FROM user
+            WHERE status=(SELECT status_id FROM status_code WHERE code='C')
+
+        3. a derived table subquery placed in the :term:`FROM clause` of an enclosing
+           ``SELECT``.   Such a subquery is almost always given an alias name.
+
+           .. sourcecode:: sql
+
+            SELECT user.id, user.name, ad_subq.email_address
+            FROM
+                user JOIN
+                (select user_id, email_address FROM address WHERE address_type='Q') AS ad_subq
+                ON user.id = ad_subq.user_id
+
     correlates
     correlated subquery
     correlated subqueries
@@ -290,8 +386,28 @@ Glossary
         table is recieved from the enclosing query, where each row selected from
         ``user_account`` results in a distinct execution of the subquery.
 
-        A correlated subquery is nearly always present in the :term:`WHERE clause`
-        or :term:`columns clause` of the enclosing ``SELECT`` statement, and never
-        in the :term:`FROM clause`; this is because
-        the correlation can only proceed once the original source rows from the enclosing
-        statement's FROM clause are available.
+        A correlated subquery is in most cases present in the :term:`WHERE clause`
+        or :term:`columns clause` of the immediately enclosing ``SELECT``
+        statement, as well as in the ORDER BY or HAVING clause.
+
+        In less common cases, a correlated subquery may be present in the
+        :term:`FROM clause` of an enclosing ``SELECT``; in these cases the
+        correlation is typically due to the enclosing ``SELECT`` itself being
+        enclosed in the WHERE,
+        ORDER BY, columns or HAVING clause of another ``SELECT``, such as:
+
+        .. sourcecode:: sql
+
+            SELECT parent.id FROM parent
+            WHERE EXISTS (
+                SELECT * FROM (
+                    SELECT child.id AS id, child.parent_id AS parent_id, child.pos AS pos
+                    FROM child
+                    WHERE child.parent_id = parent.id ORDER BY child.pos
+                LIMIT 3)
+            WHERE id = 7)
+
+        Correlation from one ``SELECT`` directly to one which encloses the correlated
+        query via its ``FROM``
+        clause is not possible, because the correlation can only proceed once the
+        original source rows from the enclosing statement's FROM clause are available.