ensure _ORMJoin transfers parententity from left side

Fixed bug involving :class:`.Select` constructs which used a combination of
:meth:`.Select.select_from` with an ORM entity followed by
:meth:`.Select.join` against the entity sent in
:meth:`.Select.select_from`, as well as using plain
:meth:`.Select.join_from`, which when combined with a columns clause that
didn't explicitly include that entity would then cause "automatic WHERE
criteria" features such as the IN expression required for a single-table
inheritance subclass, as well as the criteria set up by the
:func:`_orm.with_loader_criteria` option, to not be rendered for that
entity. The correct entity is now transferred to the :class:`.Join` object
that's generated internally, so that the criteria against the left
side entity is correctly added.

Fixes: #8721
Change-Id: I8266430063e2c72071b7262fdd5ec5079fbcba3e

3 files changed, 30 insertions, 2 deletions

diff --git a/lib/sqlalchemy/orm/_orm_constructors.py b/lib/sqlalchemy/orm/_orm_constructors.py
index f00132593..30119d9d7 100644
--- a/lib/sqlalchemy/orm/_orm_constructors.py
+++ b/lib/sqlalchemy/orm/_orm_constructors.py
@@ -2282,8 +2282,16 @@ def join(
                 join(User.addresses).\
                 filter(Address.email_address=='foo@bar.com')
 
-    See :ref:`orm_queryguide_joins` for information on modern usage
-    of ORM level joins.
+    .. warning:: using :func:`_orm.join` directly may not work properly
+       with modern ORM options such as :func:`_orm.with_loader_criteria`.
+       It is strongly recommended to use the idiomatic join patterns
+       provided by methods such as :meth:`.Select.join` and
+       :meth:`.Select.join_from` when creating ORM joins.
+
+    .. seealso::
+
+        :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel` for
+        background on idiomatic ORM join patterns
 
     """
     return _ORMJoin(left, right, onclause, isouter, full)
diff --git a/lib/sqlalchemy/orm/context.py b/lib/sqlalchemy/orm/context.py
index f8c7ba714..8dca83756 100644
--- a/lib/sqlalchemy/orm/context.py
+++ b/lib/sqlalchemy/orm/context.py
@@ -2246,6 +2246,7 @@ class ORMSelectCompileState(ORMCompileState, SelectState):
 
         for fromclause in self.from_clauses:
             ext_info = fromclause._annotations.get("parententity", None)
+
             if (
                 ext_info
                 and (
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 481a71f8e..3302feb70 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -68,6 +68,7 @@ from ..sql import lambdas
 from ..sql import roles
 from ..sql import util as sql_util
 from ..sql import visitors
+from ..sql._typing import is_selectable
 from ..sql.annotation import SupportsCloneAnnotations
 from ..sql.base import ColumnCollection
 from ..sql.cache_key import HasCacheKey
@@ -1704,6 +1705,24 @@ class _ORMJoin(expression.Join):
 
             self._target_adapter = target_adapter
 
+            # we don't use the normal coercions logic for _ORMJoin
+            # (probably should), so do some gymnastics to get the entity.
+            # logic here is for #8721, which was a major bug in 1.4
+            # for almost two years, not reported/fixed until 1.4.43 (!)
+            if is_selectable(left_info):
+                parententity = left_selectable._annotations.get(
+                    "parententity", None
+                )
+            elif insp_is_mapper(left_info) or insp_is_aliased_class(left_info):
+                parententity = left_info
+            else:
+                parententity = None
+
+            if parententity is not None:
+                self._annotations = self._annotations.union(
+                    {"parententity": parententity}
+                )
+
         augment_onclause = onclause is None and _extra_criteria
         expression.Join.__init__(self, left, right, onclause, isouter, full)