- 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.

1 files changed, 111 insertions, 43 deletions

diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 46a08379b..37c0ac65c 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -1468,6 +1468,10 @@ def _cloned_intersection(a, b):
     return set(elem for elem in a
                if all_overlap.intersection(elem._cloned_set))
 
+def _cloned_difference(a, b):
+    all_overlap = set(_expand_cloned(a)).intersection(_expand_cloned(b))
+    return set(elem for elem in a
+                if not all_overlap.intersection(elem._cloned_set))
 
 def _from_objects(*elements):
     return itertools.chain(*[element._from_objects for element in elements])
@@ -5262,7 +5266,7 @@ class Select(HasPrefixes, SelectBase):
     _distinct = False
     _from_cloned = None
     _correlate = ()
-    _correlate_except = ()
+    _correlate_except = None
     _memoized_property = SelectBase._memoized_property
 
     def __init__(self,
@@ -5357,7 +5361,8 @@ class Select(HasPrefixes, SelectBase):
 
         return froms
 
-    def _get_display_froms(self, existing_froms=None, asfrom=False):
+    def _get_display_froms(self, explicit_correlate_froms=None,
+                                    implicit_correlate_froms=None):
         """Return the full list of 'from' clauses to be displayed.
 
         Takes into account a set of existing froms which may be
@@ -5368,7 +5373,9 @@ class Select(HasPrefixes, SelectBase):
         """
         froms = self._froms
 
-        toremove = set(itertools.chain(*[f._hide_froms for f in froms]))
+        toremove = set(itertools.chain(*[
+                            _expand_cloned(f._hide_froms)
+                            for f in froms]))
         if toremove:
             # if we're maintaining clones of froms,
             # add the copies out to the toremove list.  only include
@@ -5383,36 +5390,42 @@ class Select(HasPrefixes, SelectBase):
             # using a list to maintain ordering
             froms = [f for f in froms if f not in toremove]
 
-        if not asfrom:
-            if self._correlate:
+        if self._correlate:
+            to_correlate = self._correlate
+            if to_correlate:
                 froms = [
                     f for f in froms if f not in
                     _cloned_intersection(
-                        _cloned_intersection(froms, existing_froms or ()),
-                        self._correlate
-                    )
-                ]
-            if self._correlate_except:
-                froms = [
-                    f for f in froms if f in
-                    _cloned_intersection(
-                        froms,
-                        self._correlate_except
+                        _cloned_intersection(froms, explicit_correlate_froms or ()),
+                        to_correlate
                     )
                 ]
 
-            if self._auto_correlate and existing_froms and len(froms) > 1:
-                froms = [
-                    f for f in froms if f not in
-                    _cloned_intersection(froms, existing_froms)
-                ]
+        if self._correlate_except is not None:
+
+            froms = [
+                f for f in froms if f not in
+                _cloned_difference(
+                    _cloned_intersection(froms, explicit_correlate_froms or ()),
+                    self._correlate_except
+                )
+            ]
 
-                if not len(froms):
-                    raise exc.InvalidRequestError("Select statement '%s"
-                            "' returned no FROM clauses due to "
-                            "auto-correlation; specify "
-                            "correlate(<tables>) to control "
-                            "correlation manually." % self)
+        if self._auto_correlate and \
+            implicit_correlate_froms and \
+            len(froms) > 1:
+
+            froms = [
+                f for f in froms if f not in
+                _cloned_intersection(froms, implicit_correlate_froms)
+            ]
+
+            if not len(froms):
+                raise exc.InvalidRequestError("Select statement '%s"
+                        "' returned no FROM clauses due to "
+                        "auto-correlation; specify "
+                        "correlate(<tables>) to control "
+                        "correlation manually." % self)
 
         return froms
 
@@ -5757,19 +5770,52 @@ class Select(HasPrefixes, SelectBase):
 
     @_generative
     def correlate(self, *fromclauses):
-        """return a new select() construct which will correlate the given FROM
-        clauses to that of an enclosing select(), if a match is found.
-
-        By "match", the given fromclause must be present in this select's
-        list of FROM objects and also present in an enclosing select's list of
-        FROM objects.
-
-        Calling this method turns off the select's default behavior of
-        "auto-correlation". Normally, select() auto-correlates all of its FROM
-        clauses to those of an embedded select when compiled.
-
-        If the fromclause is None, correlation is disabled for the returned
-        select().
+        """return a new :class:`.Select` which will correlate the given FROM
+        clauses to that of an enclosing :class:`.Select`.
+
+        Calling this method turns off the :class:`.Select` object's
+        default behavior of "auto-correlation".  Normally, FROM elements
+        which appear in a :class:`.Select` that encloses this one via
+        its :term:`WHERE clause`, ORDER BY, HAVING or
+        :term:`columns clause` will be omitted from this :class:`.Select`
+        object's :term:`FROM clause`.
+        Setting an explicit correlation collection using the
+        :meth:`.Select.correlate` method provides a fixed list of FROM objects
+        that can potentially take place in this process.
+
+        When :meth:`.Select.correlate` is used to apply specific FROM clauses
+        for correlation, the FROM elements become candidates for
+        correlation regardless of how deeply nested this :class:`.Select`
+        object is, relative to an enclosing :class:`.Select` which refers to
+        the same FROM object.  This is in contrast to the behavior of
+        "auto-correlation" which only correlates to an immediate enclosing
+        :class:`.Select`.   Multi-level correlation ensures that the link
+        between enclosed and enclosing :class:`.Select` is always via
+        at least one WHERE/ORDER BY/HAVING/columns clause in order for
+        correlation to take place.
+
+        If ``None`` is passed, the :class:`.Select` object will correlate
+        none of its FROM entries, and all will render unconditionally
+        in the local FROM clause.
+
+        :param \*fromclauses: a list of one or more :class:`.FromClause`
+         constructs, or other compatible constructs (i.e. ORM-mapped
+         classes) to become part of the correlate collection.
+
+         .. versionchanged:: 0.8.0 ORM-mapped classes are accepted by
+            :meth:`.Select.correlate`.
+
+        .. versionchanged:: 0.8.0 The :meth:`.Select.correlate` method no
+           longer unconditionally removes entries from the FROM clause; instead,
+           the candidate FROM entries must also be matched by a FROM entry
+           located in an enclosing :class:`.Select`, which ultimately encloses
+           this one as present in the WHERE clause, ORDER BY clause, HAVING
+           clause, or columns clause of an enclosing :meth:`.Select`.
+
+        .. versionchanged:: 0.8.2 explicit correlation takes place
+           via any level of nesting of :class:`.Select` objects; in previous
+           0.8 versions, correlation would only occur relative to the immediate
+           enclosing :class:`.Select` construct.
 
         .. seealso::
 
@@ -5787,9 +5833,30 @@ class Select(HasPrefixes, SelectBase):
 
     @_generative
     def correlate_except(self, *fromclauses):
-        """"Return a new select() construct which will auto-correlate
-        on FROM clauses of enclosing selectables, except for those FROM
-        clauses specified here.
+        """return a new :class:`.Select` which will omit the given FROM
+        clauses from the auto-correlation process.
+
+        Calling :meth:`.Select.correlate_except` turns off the
+        :class:`.Select` object's default behavior of
+        "auto-correlation" for the given FROM elements.  An element
+        specified here will unconditionally appear in the FROM list, while
+        all other FROM elements remain subject to normal auto-correlation
+        behaviors.
+
+        .. versionchanged:: 0.8.2 The :meth:`.Select.correlate_except`
+           method was improved to fully prevent FROM clauses specified here
+           from being omitted from the immediate FROM clause of this
+           :class:`.Select`.
+
+        If ``None`` is passed, the :class:`.Select` object will correlate
+        all of its FROM entries.
+
+        .. versionchanged:: 0.8.2 calling ``correlate_except(None)`` will
+           correctly auto-correlate all FROM clauses.
+
+        :param \*fromclauses: a list of one or more :class:`.FromClause`
+         constructs, or other compatible constructs (i.e. ORM-mapped
+         classes) to become part of the correlate-exception collection.
 
         .. seealso::
 
@@ -5798,11 +5865,12 @@ class Select(HasPrefixes, SelectBase):
             :ref:`correlated_subqueries`
 
         """
+
         self._auto_correlate = False
         if fromclauses and fromclauses[0] is None:
             self._correlate_except = ()
         else:
-            self._correlate_except = set(self._correlate_except).union(
+            self._correlate_except = set(self._correlate_except or ()).union(
                     _interpret_as_from(f) for f in fromclauses)
 
     def append_correlation(self, fromclause):