Adjust derivation rules for table vs. subquery against a join

Fixed bug where ORM queries using a correlated subquery in conjunction with
:func:`_orm.column_property` would fail to correlate correctly to an
enclosing subquery or to a CTE when :meth:`_sql.Select.correlate_except`
were used in the property to control correlation, in cases where the
subquery contained the same selectables as ones within the correlated
subquery that were intended to not be correlated.

This is achieved by adding a limiting factor to ClauseAdapter
which is to explicitly pass the selectables we will be adapting
"from", which is then used by AliasedClass to limit "from"
to the mappers represented by the AliasedClass.

This did cause one test where an alias for a contains_eager()
was missing to suddenly fail, and the test was corrected, however
there may be some very edge cases like that one where the tighter
criteria causes an existing use case that's relying on the more
liberal aliasing to require modifications.

Fixes: #6060
Change-Id: I8342042641886e1a220beafeb94fe45ea7aadb33

1 files changed, 18 insertions, 0 deletions

diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index 4300d8a29..4dec30a80 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -813,6 +813,7 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         exclude_fn=None,
         adapt_on_names=False,
         anonymize_labels=False,
+        adapt_from_selectables=None,
     ):
         self.__traverse_options__ = {
             "stop_on": [selectable],
@@ -823,6 +824,7 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         self.exclude_fn = exclude_fn
         self.equivalents = util.column_dict(equivalents or {})
         self.adapt_on_names = adapt_on_names
+        self.adapt_from_selectables = adapt_from_selectables
 
     def _corresponding_column(
         self, col, require_embedded, _seen=util.EMPTY_SET
@@ -850,6 +852,13 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         if isinstance(col, FromClause) and not isinstance(
             col, functions.FunctionElement
         ):
+            if self.adapt_from_selectables:
+                for adp in self.adapt_from_selectables:
+                    if adp.is_derived_from(col):
+                        break
+                else:
+                    return None
+
             if self.selectable.is_derived_from(col):
                 return self.selectable
             elif isinstance(col, Alias) and isinstance(
@@ -875,6 +884,13 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         if "adapt_column" in col._annotations:
             col = col._annotations["adapt_column"]
 
+        if self.adapt_from_selectables and col not in self.equivalents:
+            for adp in self.adapt_from_selectables:
+                if adp.c.corresponding_column(col, False) is not None:
+                    break
+            else:
+                return None
+
         if self.include_fn and not self.include_fn(col):
             return None
         elif self.exclude_fn and self.exclude_fn(col):
@@ -924,6 +940,7 @@ class ColumnAdapter(ClauseAdapter):
         adapt_on_names=False,
         allow_label_resolve=True,
         anonymize_labels=False,
+        adapt_from_selectables=None,
     ):
         ClauseAdapter.__init__(
             self,
@@ -933,6 +950,7 @@ class ColumnAdapter(ClauseAdapter):
             exclude_fn=exclude_fn,
             adapt_on_names=adapt_on_names,
             anonymize_labels=anonymize_labels,
+            adapt_from_selectables=adapt_from_selectables,
         )
 
         self.columns = util.WeakPopulateDict(self._locate_col)