Merge "Alias the onclause if ORM join is to same polymorphic selectable"

5 files changed, 71 insertions, 2 deletions

diff --git a/doc/build/changelog/unreleased_13/5288.rst b/doc/build/changelog/unreleased_13/5288.rst
new file mode 100644
index 000000000..d5fbdf5af
--- /dev/null
+++ b/doc/build/changelog/unreleased_13/5288.rst
@@ -0,0 +1,8 @@
+.. change::
+    :tags: bug, orm
+    :tickets: 5288
+
+    Fixed bug where using :func:`.with_polymorphic` as the target of a join via
+    :meth:`.RelationshipComparator.of_type` on a mapper that already has a
+    subquery-based with_polymorphic setting that's equivalent to the one
+    requested would not correctly alias the ON clause in the join.
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index ab49a4dcc..98e997078 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -2762,7 +2762,6 @@ class Query(Generative):
             elif prop:
                 # joining to selectable with a mapper property given
                 # as the ON clause
-
                 if not right_selectable.is_derived_from(
                     right_mapper.persist_selectable
                 ):
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 6ac56a324..85f23d159 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -2377,7 +2377,10 @@ class RelationshipProperty(StrategizedProperty):
             if self._is_self_referential and source_selectable is None:
                 dest_selectable = dest_selectable._anonymous_fromclause()
                 aliased = True
-        elif dest_selectable is not self.mapper._with_polymorphic_selectable:
+        elif (
+            dest_selectable is not self.mapper._with_polymorphic_selectable
+            or self.mapper.with_polymorphic
+        ):
             aliased = True
 
         dest_mapper = of_type_mapper or self.mapper
diff --git a/test/orm/inheritance/_poly_fixtures.py b/test/orm/inheritance/_poly_fixtures.py
index c9cde7a81..5d23e7801 100644
--- a/test/orm/inheritance/_poly_fixtures.py
+++ b/test/orm/inheritance/_poly_fixtures.py
@@ -151,6 +151,16 @@ class _PolymorphicFixtureBase(fixtures.MappedTest, AssertsCompiledSQL):
         )
 
     @classmethod
+    def setup_classes(cls):
+        cls.classes["Engineer"] = Engineer
+        cls.classes["Person"] = Person
+        cls.classes["Manager"] = Manager
+        cls.classes["Machine"] = Machine
+        cls.classes["Boss"] = Boss
+        cls.classes["Company"] = Company
+        cls.classes["Paperwork"] = Paperwork
+
+    @classmethod
     def insert_data(cls, connection):
 
         cls.e1 = e1 = Engineer(
diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py
index 8be25f2b9..db36e36b0 100644
--- a/test/orm/inheritance/test_polymorphic_rel.py
+++ b/test/orm/inheritance/test_polymorphic_rel.py
@@ -1365,6 +1365,55 @@ class _PolymorphicTestBase(object):
             expected,
         )
 
+    def _join_to_poly_wp_one(self, sess):
+        wp = with_polymorphic(self.classes.Person, "*")
+        return (
+            sess.query(wp.name, self.classes.Company.name)
+            .join(self.classes.Company.employees.of_type(wp))
+            .order_by(wp.person_id)
+        )
+
+    def _join_to_poly_wp_two(self, sess):
+        wp = with_polymorphic(self.classes.Person, "*", aliased=True)
+        return (
+            sess.query(wp.name, self.classes.Company.name)
+            .join(self.classes.Company.employees.of_type(wp))
+            .order_by(wp.person_id)
+        )
+
+    def _join_to_poly_wp_three(self, sess):
+        wp = with_polymorphic(
+            self.classes.Person, "*", aliased=True, flat=True
+        )
+        return (
+            sess.query(wp.name, self.classes.Company.name)
+            .join(self.classes.Company.employees.of_type(wp))
+            .order_by(wp.person_id)
+        )
+
+    @testing.combinations(
+        lambda self, sess: (
+            sess.query(self.classes.Person.name, self.classes.Company.name)
+            .join(self.classes.Company.employees)
+            .order_by(self.classes.Person.person_id)
+        ),
+        _join_to_poly_wp_one,
+        _join_to_poly_wp_two,
+        _join_to_poly_wp_three,
+    )
+    def test_mixed_entities_join_to_poly(self, q):
+        sess = create_session()
+        expected = [
+            ("dilbert", "MegaCorp, Inc."),
+            ("wally", "MegaCorp, Inc."),
+            ("pointy haired boss", "MegaCorp, Inc."),
+            ("dogbert", "MegaCorp, Inc."),
+            ("vlad", "Elbonia, Inc."),
+        ]
+        eq_(
+            q(self, sess).all(), expected,
+        )
+
     def test_mixed_entities_two(self):
         sess = create_session()
         expected = [