Merge "remove should_nest behavior for contains_eager()" into main

3 files changed, 21 insertions, 2 deletions

diff --git a/lib/sqlalchemy/orm/context.py b/lib/sqlalchemy/orm/context.py
index ff0cdd680..4f24103df 100644
--- a/lib/sqlalchemy/orm/context.py
+++ b/lib/sqlalchemy/orm/context.py
@@ -529,6 +529,7 @@ class ORMFromStatementCompileState(ORMCompileState):
 
     _has_orm_entities = False
     multi_row_eager_loaders = False
+    eager_adding_joins = False
     compound_eager_adapter = None
 
     extra_criteria_entities = _EMPTY_DICT
@@ -794,6 +795,7 @@ class ORMSelectCompileState(ORMCompileState, SelectState):
 
     _has_orm_entities = False
     multi_row_eager_loaders = False
+    eager_adding_joins = False
     compound_eager_adapter = None
 
     correlate = None
@@ -1106,7 +1108,11 @@ class ORMSelectCompileState(ORMCompileState, SelectState):
         if self.order_by is False:
             self.order_by = None
 
-        if self.multi_row_eager_loaders and self._should_nest_selectable:
+        if (
+            self.multi_row_eager_loaders
+            and self.eager_adding_joins
+            and self._should_nest_selectable
+        ):
             self.statement = self._compound_eager_statement()
         else:
             self.statement = self._simple_statement()
diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 41e598c38..19c6493db 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -2108,6 +2108,9 @@ class JoinedLoader(AbstractRelationshipLoader):
         )
 
         if user_defined_adapter is not False:
+
+            # setup an adapter but dont create any JOIN, assume it's already
+            # in the query
             (
                 clauses,
                 adapter,
@@ -2119,6 +2122,11 @@ class JoinedLoader(AbstractRelationshipLoader):
                 adapter,
                 user_defined_adapter,
             )
+
+            # don't do "wrap" for multi-row, we want to wrap
+            # limited/distinct SELECT,
+            # because we want to put the JOIN on the outside.
+
         else:
             # if not via query option, check for
             # a cycle
@@ -2129,6 +2137,7 @@ class JoinedLoader(AbstractRelationshipLoader):
                 elif path.contains_mapper(self.mapper):
                     return
 
+            # add the JOIN and create an adapter
             (
                 clauses,
                 adapter,
@@ -2145,6 +2154,10 @@ class JoinedLoader(AbstractRelationshipLoader):
                 chained_from_outerjoin,
             )
 
+            # for multi-row, we want to wrap limited/distinct SELECT,
+            # because we want to put the JOIN on the outside.
+            compile_state.eager_adding_joins = True
+
         with_poly_entity = path.get(
             compile_state.attributes, "path_with_polymorphic", None
         )
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index cfbf24f3c..8167dc7e4 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -4461,7 +4461,7 @@ class Label(roles.LabeledColumnExprRole[_T], NamedColumn[_T]):
         # when a label name conflicts with other columns and select()
         # is attempting to disambiguate an explicit label, which is not what
         # the user would want.   See issue #6090.
-        if key != self.name:
+        if key != self.name and not isinstance(self.name, _anonymous_label):
             raise exc.InvalidRequestError(
                 "Label name %s is being renamed to an anonymous label due "
                 "to disambiguation "