Open up check for relationships that write to the same column

Enhanced logic that tracks if relationships will be conflicting with each
other when they write to the same column to include simple cases of two
relationships that should have a "backref" between them.   This means that
if two relationships are not viewonly, are not linked with back_populates
and are not otherwise in an inheriting sibling/overriding arrangement, and
will populate the same foreign key column, a warning is emitted at mapper
configuration time warning that a conflict may arise.  A new parameter
:paramref:`.relationship.overlaps` is added to suit those very rare cases
where such an overlapping persistence arrangement may be unavoidable.

Fixes: #5171
Change-Id: Ifae5998fc1c7e49ce059aec8a67c80cabee768ad

19 files changed, 268 insertions, 30 deletions

diff --git a/doc/build/changelog/unreleased_14/5171.rst b/doc/build/changelog/unreleased_14/5171.rst
new file mode 100644
index 000000000..65824a0a6
--- /dev/null
+++ b/doc/build/changelog/unreleased_14/5171.rst
@@ -0,0 +1,14 @@
+.. change::
+    :tags: usecase, orm
+    :tickets: 5171
+
+    Enhanced logic that tracks if relationships will be conflicting with each
+    other when they write to the same column to include simple cases of two
+    relationships that should have a "backref" between them.   This means that
+    if two relationships are not viewonly, are not linked with back_populates
+    and are not otherwise in an inheriting sibling/overriding arrangement, and
+    will populate the same foreign key column, a warning is emitted at mapper
+    configuration time warning that a conflict may arise.  A new parameter
+    :paramref:`.relationship.overlaps` is added to suit those very rare cases
+    where such an overlapping persistence arrangement may be unavoidable.
+
diff --git a/lib/sqlalchemy/orm/mapper.py b/lib/sqlalchemy/orm/mapper.py
index b84d41260..0d87a9c40 100644
--- a/lib/sqlalchemy/orm/mapper.py
+++ b/lib/sqlalchemy/orm/mapper.py
@@ -2557,6 +2557,17 @@ class Mapper(sql_base.HasCacheKey, InspectionAttr):
 
         return self.base_mapper is other.base_mapper
 
+    def is_sibling(self, other):
+        """return true if the other mapper is an inheriting sibling to this
+        one.  common parent but different branch
+
+        """
+        return (
+            self.base_mapper is other.base_mapper
+            and not self.isa(other)
+            and not other.isa(self)
+        )
+
     def _canload(self, state, allow_subtypes):
         s = self.primary_mapper()
         if self.polymorphic_on is not None or allow_subtypes:
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 5573f7c9a..b82a3d271 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -16,6 +16,7 @@ and `secondaryjoin` aspects of :func:`.relationship`.
 from __future__ import absolute_import
 
 import collections
+import re
 import weakref
 
 from . import attributes
@@ -131,6 +132,7 @@ class RelationshipProperty(StrategizedProperty):
         order_by=False,
         backref=None,
         back_populates=None,
+        overlaps=None,
         post_update=False,
         cascade=False,
         viewonly=False,
@@ -320,6 +322,18 @@ class RelationshipProperty(StrategizedProperty):
             :paramref:`~.relationship.backref` - alternative form
             of backref specification.
 
+        :param overlaps:
+           A string name or comma-delimited set of names of other relationships
+           on either this mapper, a descendant mapper, or a target mapper with
+           which this relationship may write to the same foreign keys upon
+           persistence.   The only effect this has is to eliminate the
+           warning that this relationship will conflict with another upon
+           persistence.   This is used for such relationships that are truly
+           capable of conflicting with each other on write, but the application
+           will ensure that no such conflicts occur.
+
+           .. versionadded:: 1.4
+
         :param bake_queries=True:
           Use the :class:`.BakedQuery` cache to cache the construction of SQL
           used in lazy loads.  True by default.   Set to False if the
@@ -916,6 +930,10 @@ class RelationshipProperty(StrategizedProperty):
         self.strategy_key = (("lazy", self.lazy),)
 
         self._reverse_property = set()
+        if overlaps:
+            self._overlaps = set(re.split(r"\s*,\s*", overlaps))
+        else:
+            self._overlaps = ()
 
         if cascade is not False:
             self.cascade = cascade
@@ -3120,8 +3138,6 @@ class JoinCondition(object):
             # if multiple relationships overlap foreign() directly, but
             # we're going to assume it's typically a ForeignKeyConstraint-
             # level configuration that benefits from this warning.
-            if len(to_.foreign_keys) < 2:
-                continue
 
             if to_ not in self._track_overlapping_sync_targets:
                 self._track_overlapping_sync_targets[
@@ -3134,12 +3150,15 @@ class JoinCondition(object):
                 for pr, fr_ in prop_to_from.items():
                     if (
                         pr.mapper in mapperlib._mapper_registry
+                        and pr not in self.prop._reverse_property
+                        and pr.key not in self.prop._overlaps
+                        and self.prop.key not in pr._overlaps
+                        and not self.prop.parent.is_sibling(pr.parent)
+                        and not self.prop.mapper.is_sibling(pr.mapper)
                         and (
-                            self.prop._persists_for(pr.parent)
-                            or pr._persists_for(self.prop.parent)
+                            self.prop.key != pr.key
+                            or not self.prop.parent.common_parent(pr.parent)
                         )
-                        and fr_ is not from_
-                        and pr not in self.prop._reverse_property
                     ):
 
                         other_props.append((pr, fr_))
@@ -3148,10 +3167,16 @@ class JoinCondition(object):
                     util.warn(
                         "relationship '%s' will copy column %s to column %s, "
                         "which conflicts with relationship(s): %s. "
-                        "Consider applying "
-                        "viewonly=True to read-only relationships, or provide "
-                        "a primaryjoin condition marking writable columns "
-                        "with the foreign() annotation."
+                        "If this is not the intention, consider if these "
+                        "relationships should be linked with "
+                        "back_populates, or if viewonly=True should be "
+                        "applied to one or more if they are read-only. "
+                        "For the less common case that foreign key "
+                        "constraints are partially overlapping, the "
+                        "orm.foreign() "
+                        "annotation can be used to isolate the columns that "
+                        "should be written towards.   The 'overlaps' "
+                        "parameter may be used to remove this warning."
                         % (
                             self.prop,
                             from_,
diff --git a/test/ext/declarative/test_inheritance.py b/test/ext/declarative/test_inheritance.py
index 083fdb0db..d33dbd4be 100644
--- a/test/ext/declarative/test_inheritance.py
+++ b/test/ext/declarative/test_inheritance.py
@@ -1917,7 +1917,7 @@ class ConcreteExtensionConfigTest(
             @declared_attr
             def something_else(cls):
                 counter(cls, "something_else")
-                return relationship("Something")
+                return relationship("Something", viewonly=True)
 
         class ConcreteConcreteAbstraction(AbstractConcreteAbstraction):
             __tablename__ = "cca"
diff --git a/test/ext/test_associationproxy.py b/test/ext/test_associationproxy.py
index e7cc6251b..ddca2f78e 100644
--- a/test/ext/test_associationproxy.py
+++ b/test/ext/test_associationproxy.py
@@ -101,6 +101,9 @@ class AutoFlushTest(fixtures.TablesTest):
             Column("name", String(50)),
         )
 
+    def teardown(self):
+        clear_mappers()
+
     def _fixture(self, collection_class, is_dict=False):
         class Parent(object):
             collection = association_proxy("_collection", "child")
@@ -1596,8 +1599,10 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
             Keyword,
             keywords,
             properties={
-                "user_keyword": relationship(UserKeyword, uselist=False),
-                "user_keywords": relationship(UserKeyword),
+                "user_keyword": relationship(
+                    UserKeyword, uselist=False, back_populates="keyword"
+                ),
+                "user_keywords": relationship(UserKeyword, viewonly=True),
             },
         )
 
@@ -1606,7 +1611,9 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
             userkeywords,
             properties={
                 "user": relationship(User, backref="user_keywords"),
-                "keyword": relationship(Keyword),
+                "keyword": relationship(
+                    Keyword, back_populates="user_keyword"
+                ),
             },
         )
         mapper(
@@ -3426,7 +3433,7 @@ class ScopeBehaviorTest(fixtures.DeclarativeMappedTest):
             data = Column(String(50))
             bs = relationship("B")
 
-            b_dyn = relationship("B", lazy="dynamic")
+            b_dyn = relationship("B", lazy="dynamic", viewonly=True)
 
             b_data = association_proxy("bs", "data")
 
diff --git a/test/orm/inheritance/test_basic.py b/test/orm/inheritance/test_basic.py
index 831781330..9b896b59a 100644
--- a/test/orm/inheritance/test_basic.py
+++ b/test/orm/inheritance/test_basic.py
@@ -1229,7 +1229,9 @@ class EagerLazyTest(fixtures.MappedTest):
         foos = mapper(Foo, foo)
         bars = mapper(Bar, bar, inherits=foos)
         bars.add_property("lazy", relationship(foos, bar_foo, lazy="select"))
-        bars.add_property("eager", relationship(foos, bar_foo, lazy="joined"))
+        bars.add_property(
+            "eager", relationship(foos, bar_foo, lazy="joined", viewonly=True)
+        )
 
         foo.insert().execute(data="foo1")
         bar.insert().execute(id=1, data="bar1")
diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py
index 9426847ba..3f3718190 100644
--- a/test/orm/inheritance/test_single.py
+++ b/test/orm/inheritance/test_single.py
@@ -1142,7 +1142,9 @@ class RelationshipToSingleTest(
         mapper(
             Company,
             companies,
-            properties={"engineers": relationship(Engineer)},
+            properties={
+                "engineers": relationship(Engineer, back_populates="company")
+            },
         )
         mapper(
             Employee,
diff --git a/test/orm/test_cycles.py b/test/orm/test_cycles.py
index 7dd349a74..22a26e617 100644
--- a/test/orm/test_cycles.py
+++ b/test/orm/test_cycles.py
@@ -71,13 +71,16 @@ class SelfReferentialTest(fixtures.MappedTest):
             C1,
             t1,
             properties={
-                "c1s": relationship(C1, cascade="all"),
+                "c1s": relationship(
+                    C1, cascade="all", back_populates="parent"
+                ),
                 "parent": relationship(
                     C1,
                     primaryjoin=t1.c.parent_c1 == t1.c.c1,
                     remote_side=t1.c.c1,
                     lazy="select",
                     uselist=False,
+                    back_populates="c1s",
                 ),
             },
         )
diff --git a/test/orm/test_deferred.py b/test/orm/test_deferred.py
index 5acfa3f79..9226580ea 100644
--- a/test/orm/test_deferred.py
+++ b/test/orm/test_deferred.py
@@ -1296,7 +1296,9 @@ class InheritanceTest(_Polymorphic):
         super(InheritanceTest, cls).setup_mappers()
         from sqlalchemy import inspect
 
-        inspect(Company).add_property("managers", relationship(Manager))
+        inspect(Company).add_property(
+            "managers", relationship(Manager, viewonly=True)
+        )
 
     def test_load_only_subclass(self):
         s = Session()
diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py
index bf39b25a6..0a97c5246 100644
--- a/test/orm/test_eager_relations.py
+++ b/test/orm/test_eager_relations.py
@@ -771,6 +771,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="joined",
                     order_by=open_mapper.id,
+                    viewonly=True,
                 ),
                 closed_orders=relationship(
                     closed_mapper,
@@ -780,6 +781,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="joined",
                     order_by=closed_mapper.id,
+                    viewonly=True,
                 ),
             ),
         )
@@ -906,6 +908,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="joined",
                     order_by=orders.c.id,
+                    viewonly=True,
                 ),
                 closed_orders=relationship(
                     Order,
@@ -914,9 +917,11 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="joined",
                     order_by=orders.c.id,
+                    viewonly=True,
                 ),
             ),
         )
+
         self._run_double_test()
 
     def _run_double_test(self, no_items=False):
@@ -3119,7 +3124,7 @@ class InnerJoinSplicingTest(fixtures.MappedTest, testing.AssertsCompiledSQL):
         b_np = aliased(B, weird_selectable, flat=True)
 
         a_mapper = inspect(A)
-        a_mapper.add_property("bs_np", relationship(b_np))
+        a_mapper.add_property("bs_np", relationship(b_np, viewonly=True))
 
         s = Session()
 
diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py
index 08b68232b..2425cd756 100644
--- a/test/orm/test_froms.py
+++ b/test/orm/test_froms.py
@@ -3123,6 +3123,7 @@ class CustomJoinTest(QueryTest):
                         orders.c.isopen == 1, users.c.id == orders.c.user_id
                     ),
                     lazy="select",
+                    viewonly=True,
                 ),
                 closed_orders=relationship(
                     Order,
@@ -3130,6 +3131,7 @@ class CustomJoinTest(QueryTest):
                         orders.c.isopen == 0, users.c.id == orders.c.user_id
                     ),
                     lazy="select",
+                    viewonly=True,
                 ),
             ),
         )
diff --git a/test/orm/test_instrumentation.py b/test/orm/test_instrumentation.py
index ddf735e4f..16ccff936 100644
--- a/test/orm/test_instrumentation.py
+++ b/test/orm/test_instrumentation.py
@@ -6,6 +6,7 @@ from sqlalchemy import MetaData
 from sqlalchemy import util
 from sqlalchemy.orm import attributes
 from sqlalchemy.orm import class_mapper
+from sqlalchemy.orm import clear_mappers
 from sqlalchemy.orm import create_session
 from sqlalchemy.orm import instrumentation
 from sqlalchemy.orm import mapper
@@ -791,6 +792,8 @@ class MiscTest(fixtures.ORMTest):
             session.add(b)
             assert a in session, "base is %s" % base
 
+            clear_mappers()
+
     def test_compileonattr_rel_backref_b(self):
         m = MetaData()
         t1 = Table(
@@ -832,3 +835,4 @@ class MiscTest(fixtures.ORMTest):
             session = create_session()
             session.add(a)
             assert b in session, "base: %s" % base
+            clear_mappers()
diff --git a/test/orm/test_lazy_relations.py b/test/orm/test_lazy_relations.py
index ca7a58024..1c2720898 100644
--- a/test/orm/test_lazy_relations.py
+++ b/test/orm/test_lazy_relations.py
@@ -645,6 +645,7 @@ class LazyTest(_fixtures.FixtureTest):
                         users.c.id == open_mapper.user_id,
                     ),
                     lazy="select",
+                    overlaps="closed_orders",
                 ),
                 closed_orders=relationship(
                     closed_mapper,
@@ -653,6 +654,7 @@ class LazyTest(_fixtures.FixtureTest):
                         users.c.id == closed_mapper.user_id,
                     ),
                     lazy="select",
+                    overlaps="open_orders",
                 ),
             ),
         )
diff --git a/test/orm/test_options.py b/test/orm/test_options.py
index 97c00b3c6..00e1d232b 100644
--- a/test/orm/test_options.py
+++ b/test/orm/test_options.py
@@ -242,7 +242,7 @@ class OfTypePathingTest(PathTest, QueryTest):
             inherits=Address,
             properties={
                 "sub_attr": column_property(address_table.c.email_address),
-                "dings": relationship(Dingaling),
+                "dings": relationship(Dingaling, viewonly=True),
             },
         )
 
@@ -585,7 +585,7 @@ class OptionsTest(PathTest, QueryTest):
         mapper(
             SubAddr,
             inherits=Address,
-            properties={"flub": relationship(Dingaling)},
+            properties={"flub": relationship(Dingaling, viewonly=True)},
         )
 
         q = sess.query(Address)
@@ -604,7 +604,7 @@ class OptionsTest(PathTest, QueryTest):
         mapper(
             SubAddr,
             inherits=Address,
-            properties={"flub": relationship(Dingaling)},
+            properties={"flub": relationship(Dingaling, viewonly=True)},
         )
 
         q = sess.query(SubAddr)
@@ -623,7 +623,7 @@ class OptionsTest(PathTest, QueryTest):
         mapper(
             SubAddr,
             inherits=Address,
-            properties={"flub": relationship(Dingaling)},
+            properties={"flub": relationship(Dingaling, viewonly=True)},
         )
 
         q = sess.query(Address)
@@ -708,7 +708,7 @@ class OptionsTest(PathTest, QueryTest):
         mapper(
             SubAddr,
             inherits=Address,
-            properties={"flub": relationship(Dingaling)},
+            properties={"flub": relationship(Dingaling, viewonly=True)},
         )
 
         q = sess.query(User)
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index aabee82ad..2fb79604a 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -5083,6 +5083,7 @@ class WithTransientOnNone(_fixtures.FixtureTest, AssertsCompiledSQL):
                         users.c.id == addresses.c.user_id,
                         users.c.name == addresses.c.email_address,
                     ),
+                    viewonly=True,
                 ),
             },
         )
diff --git a/test/orm/test_relationships.py b/test/orm/test_relationships.py
index 78e9d77e8..53295c688 100644
--- a/test/orm/test_relationships.py
+++ b/test/orm/test_relationships.py
@@ -13,6 +13,7 @@ from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import testing
 from sqlalchemy.ext.declarative import declarative_base
+from sqlalchemy.orm import aliased
 from sqlalchemy.orm import attributes
 from sqlalchemy.orm import backref
 from sqlalchemy.orm import clear_mappers
@@ -649,6 +650,7 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
         add_b_amember=False,
         add_bsub1_a=False,
         add_bsub2_a_viewonly=False,
+        add_b_a_overlaps=None,
     ):
 
         Base = declarative_base(metadata=self.metadata)
@@ -689,7 +691,9 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
             # writes to B.a_id, which conflicts with BSub2.a_member,
             # so should warn
             if add_b_a:
-                a = relationship("A", viewonly=add_b_a_viewonly)
+                a = relationship(
+                    "A", viewonly=add_b_a_viewonly, overlaps=add_b_a_overlaps
+                )
 
             # if added, this relationship writes to B.a_id, which conflicts
             # with BSub1.a
@@ -719,6 +723,88 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
 
         return A, AMember, B, BSub1, BSub2
 
+    def _fixture_two(self, setup_backrefs=False, setup_overlaps=False):
+
+        Base = declarative_base(metadata=self.metadata)
+
+        # purposely using the comma to make sure parsing the comma works
+
+        class Parent(Base):
+            __tablename__ = "parent"
+            id = Column(Integer, primary_key=True)
+            children = relationship(
+                "Child",
+                back_populates=("parent" if setup_backrefs else None),
+                overlaps="foo, bar, parent" if setup_overlaps else None,
+            )
+
+        class Child(Base):
+            __tablename__ = "child"
+            id = Column(Integer, primary_key=True)
+            num = Column(Integer)
+            parent_id = Column(
+                Integer, ForeignKey("parent.id"), nullable=False
+            )
+            parent = relationship(
+                "Parent",
+                back_populates=("children" if setup_backrefs else None),
+                overlaps="bar, bat, children" if setup_overlaps else None,
+            )
+
+        configure_mappers()
+
+    def _fixture_three(self, use_same_mappers, setup_overlaps):
+        Base = declarative_base(metadata=self.metadata)
+
+        class Child(Base):
+            __tablename__ = "child"
+            id = Column(Integer, primary_key=True)
+            num = Column(Integer)
+            parent_id = Column(
+                Integer, ForeignKey("parent.id"), nullable=False
+            )
+
+        if not use_same_mappers:
+            c1 = aliased(Child)
+            c2 = aliased(Child)
+
+        class Parent(Base):
+            __tablename__ = "parent"
+            id = Column(Integer, primary_key=True)
+            if use_same_mappers:
+                child1 = relationship(
+                    Child,
+                    primaryjoin=lambda: and_(
+                        Child.parent_id == Parent.id, Child.num == 1
+                    ),
+                    overlaps="child2" if setup_overlaps else None,
+                )
+                child2 = relationship(
+                    Child,
+                    primaryjoin=lambda: and_(
+                        Child.parent_id == Parent.id, Child.num == 2
+                    ),
+                    overlaps="child1" if setup_overlaps else None,
+                )
+            else:
+                child1 = relationship(
+                    c1,
+                    primaryjoin=lambda: and_(
+                        c1.parent_id == Parent.id, c1.num == 1
+                    ),
+                    overlaps="child2" if setup_overlaps else None,
+                )
+
+                child2 = relationship(
+                    c2,
+                    primaryjoin=lambda: and_(
+                        c2.parent_id == Parent.id, c2.num == 1
+                    ),
+                    overlaps="child1" if setup_overlaps else None,
+                )
+
+        configure_mappers()
+
     @testing.provide_metadata
     def _test_fixture_one_run(self, **kw):
         A, AMember, B, BSub1, BSub2 = self._fixture_one(**kw)
@@ -748,6 +834,8 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
 
         session.commit()
         assert bsub1.a is a2  # because bsub1.a_member is not a relationship
+
+        assert BSub2.__mapper__.attrs.a.viewonly
         assert bsub2.a is a1  # because bsub2.a is viewonly=True
 
         # everyone has a B.a relationship
@@ -757,6 +845,46 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
         )
 
     @testing.provide_metadata
+    def test_simple_warn(self):
+        assert_raises_message(
+            exc.SAWarning,
+            r"relationship '(?:Child.parent|Parent.children)' will copy "
+            r"column parent.id to column child.parent_id, which conflicts "
+            r"with relationship\(s\): '(?:Parent.children|Child.parent)' "
+            r"\(copies parent.id to child.parent_id\).",
+            self._fixture_two,
+            setup_backrefs=False,
+        )
+
+    @testing.provide_metadata
+    def test_simple_backrefs_works(self):
+        self._fixture_two(setup_backrefs=True)
+
+    @testing.provide_metadata
+    def test_simple_overlaps_works(self):
+        self._fixture_two(setup_overlaps=True)
+
+    @testing.provide_metadata
+    def test_double_rel_same_mapper_warns(self):
+        assert_raises_message(
+            exc.SAWarning,
+            r"relationship 'Parent.child[12]' will copy column parent.id to "
+            r"column child.parent_id, which conflicts with relationship\(s\): "
+            r"'Parent.child[12]' \(copies parent.id to child.parent_id\)",
+            self._fixture_three,
+            use_same_mappers=True,
+            setup_overlaps=False,
+        )
+
+    @testing.provide_metadata
+    def test_double_rel_same_mapper_overlaps_works(self):
+        self._fixture_three(use_same_mappers=True, setup_overlaps=True)
+
+    @testing.provide_metadata
+    def test_double_rel_aliased_mapper_works(self):
+        self._fixture_three(use_same_mappers=False, setup_overlaps=False)
+
+    @testing.provide_metadata
     def test_warn_one(self):
         assert_raises_message(
             exc.SAWarning,
@@ -791,6 +919,17 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
         )
 
     @testing.provide_metadata
+    def test_warn_four(self):
+        assert_raises_message(
+            exc.SAWarning,
+            r"relationship '(?:B.a|BSub2.a_member|B.a)' will copy column "
+            r"(?:a.id|a_member.a_id) to column b.a_id",
+            self._fixture_one,
+            add_bsub2_a_viewonly=True,
+            add_b_a=True,
+        )
+
+    @testing.provide_metadata
     def test_works_one(self):
         self._test_fixture_one_run(
             add_b_a=True, add_b_a_viewonly=True, add_bsub1_a=True
@@ -798,7 +937,10 @@ class OverlappingFksSiblingTest(fixtures.TestBase):
 
     @testing.provide_metadata
     def test_works_two(self):
-        self._test_fixture_one_run(add_b_a=True, add_bsub2_a_viewonly=True)
+        # doesn't actually work with real FKs beacuse it creates conflicts :)
+        self._fixture_one(
+            add_b_a=True, add_b_a_overlaps="a_member", add_bsub1_a=True
+        )
 
 
 class CompositeSelfRefFKTest(fixtures.MappedTest, AssertsCompiledSQL):
diff --git a/test/orm/test_selectin_relations.py b/test/orm/test_selectin_relations.py
index 4eecc4be6..8453a2606 100644
--- a/test/orm/test_selectin_relations.py
+++ b/test/orm/test_selectin_relations.py
@@ -833,10 +833,16 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     Address, lazy="selectin", order_by=addresses.c.id
                 ),
                 open_orders=relationship(
-                    open_mapper, lazy="selectin", order_by=open_mapper.id
+                    open_mapper,
+                    lazy="selectin",
+                    order_by=open_mapper.id,
+                    overlaps="closed_orders",
                 ),
                 closed_orders=relationship(
-                    closed_mapper, lazy="selectin", order_by=closed_mapper.id
+                    closed_mapper,
+                    lazy="selectin",
+                    order_by=closed_mapper.id,
+                    overlaps="open_orders",
                 ),
             ),
         )
@@ -900,6 +906,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="selectin",
                     order_by=open_mapper.id,
+                    viewonly=True,
                 ),
                 closed_orders=relationship(
                     closed_mapper,
@@ -909,6 +916,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="selectin",
                     order_by=closed_mapper.id,
+                    viewonly=True,
                 ),
             ),
         )
@@ -969,6 +977,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="selectin",
                     order_by=orders.c.id,
+                    overlaps="closed_orders",
                 ),
                 closed_orders=relationship(
                     Order,
@@ -977,6 +986,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="selectin",
                     order_by=orders.c.id,
+                    overlaps="open_orders",
                 ),
             ),
         )
@@ -3108,7 +3118,7 @@ class M2OWDegradeTest(
             id = Column(Integer, primary_key=True)
             b_id = Column(ForeignKey("b.id"))
             b = relationship("B")
-            b_no_omit_join = relationship("B", omit_join=False)
+            b_no_omit_join = relationship("B", omit_join=False, overlaps="b")
             q = Column(Integer)
 
         class B(fixtures.ComparableEntity, Base):
diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py
index 4c68d154e..8bc146f18 100644
--- a/test/orm/test_subquery_relations.py
+++ b/test/orm/test_subquery_relations.py
@@ -919,6 +919,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="subquery",
                     order_by=open_mapper.id,
+                    overlaps="closed_orders",
                 ),
                 closed_orders=relationship(
                     closed_mapper,
@@ -928,6 +929,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="subquery",
                     order_by=closed_mapper.id,
+                    overlaps="open_orders",
                 ),
             ),
         )
@@ -988,6 +990,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="subquery",
                     order_by=orders.c.id,
+                    viewonly=True,
                 ),
                 closed_orders=relationship(
                     Order,
@@ -996,6 +999,7 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
                     ),
                     lazy="subquery",
                     order_by=orders.c.id,
+                    viewonly=True,
                 ),
             ),
         )
diff --git a/test/orm/test_unitofwork.py b/test/orm/test_unitofwork.py
index 58eb62339..235817db9 100644
--- a/test/orm/test_unitofwork.py
+++ b/test/orm/test_unitofwork.py
@@ -1786,6 +1786,7 @@ class OneToManyTest(_fixtures.FixtureTest):
                         users.c.id == addresses.c.user_id,
                         addresses.c.email_address.like("%boston%"),
                     ),
+                    overlaps="newyork_addresses",
                 ),
                 "newyork_addresses": relationship(
                     m2,
@@ -1793,6 +1794,7 @@ class OneToManyTest(_fixtures.FixtureTest):
                         users.c.id == addresses.c.user_id,
                         addresses.c.email_address.like("%newyork%"),
                     ),
+                    overlaps="boston_addresses",
                 ),
             },
         )