Merge "don't invoke fromclause.c when creating an annotated" into main

9 files changed, 471 insertions, 74 deletions

diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 6cd98f5ea..50eba5d4c 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -1317,7 +1317,10 @@ class LoaderCriteriaOption(CriteriaOption):
             crit = self.where_criteria  # type: ignore
         assert isinstance(crit, ColumnElement)
         return sql_util._deep_annotate(
-            crit, {"for_loader_criteria": self}, detect_subquery_cols=True
+            crit,
+            {"for_loader_criteria": self},
+            detect_subquery_cols=True,
+            ind_cols_on_fromclause=True,
         )
 
     def process_compile_state_replaced_entities(
@@ -1416,6 +1419,8 @@ class Bundle(
 
     _propagate_attrs: _PropagateAttrsType = util.immutabledict()
 
+    proxy_set = util.EMPTY_SET  # type: ignore
+
     exprs: List[_ColumnsClauseElement]
 
     def __init__(
diff --git a/lib/sqlalchemy/sql/annotation.py b/lib/sqlalchemy/sql/annotation.py
index 43ca84abb..3ce524447 100644
--- a/lib/sqlalchemy/sql/annotation.py
+++ b/lib/sqlalchemy/sql/annotation.py
@@ -421,6 +421,7 @@ def _deep_annotate(
     annotations: _AnnotationDict,
     exclude: Optional[Sequence[SupportsAnnotations]] = None,
     detect_subquery_cols: bool = False,
+    ind_cols_on_fromclause: bool = False,
 ) -> _SA:
     """Deep copy the given ClauseElement, annotating each element
     with the given annotations dictionary.
@@ -435,6 +436,16 @@ def _deep_annotate(
     cloned_ids: Dict[int, SupportsAnnotations] = {}
 
     def clone(elem: SupportsAnnotations, **kw: Any) -> SupportsAnnotations:
+
+        # ind_cols_on_fromclause means make sure an AnnotatedFromClause
+        # has its own .c collection independent of that which its proxying.
+        # this is used specifically by orm.LoaderCriteriaOption to break
+        # a reference cycle that it's otherwise prone to building,
+        # see test_relationship_criteria->
+        # test_loader_criteria_subquery_w_same_entity.  logic here was
+        # changed for #8796 and made explicit; previously it occurred
+        # by accident
+
         kw["detect_subquery_cols"] = detect_subquery_cols
         id_ = id(elem)
 
@@ -454,7 +465,11 @@ def _deep_annotate(
                 newelem = elem._annotate(annotations)
         else:
             newelem = elem
-        newelem._copy_internals(clone=clone)
+
+        newelem._copy_internals(
+            clone=clone, ind_cols_on_fromclause=ind_cols_on_fromclause
+        )
+
         cloned_ids[id_] = newelem
         return newelem
 
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 23260a985..08d01c883 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -6753,8 +6753,28 @@ TextAsFrom = TextualSelect
 
 
 class AnnotatedFromClause(Annotated):
-    def __init__(self, element, values):
-        # force FromClause to generate their internal
-        # collections into __dict__
-        element.c
-        Annotated.__init__(self, element, values)
+    def _copy_internals(self, **kw):
+        super()._copy_internals(**kw)
+        if kw.get("ind_cols_on_fromclause", False):
+            ee = self._Annotated__element  # type: ignore
+
+            self.c = ee.__class__.c.fget(self)  # type: ignore
+
+    @util.ro_memoized_property
+    def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:
+        """proxy the .c collection of the underlying FromClause.
+
+        Originally implemented in 2008 as a simple load of the .c collection
+        when the annotated construct was created (see d3621ae961a), in modern
+        SQLAlchemy versions this can be expensive for statements constructed
+        with ORM aliases.   So for #8796 SQLAlchemy 2.0 we instead proxy
+        it, which works just as well.
+
+        Two different use cases seem to require the collection either copied
+        from the underlying one, or unique to this AnnotatedFromClause.
+
+        See test_selectable->test_annotated_corresponding_column
+
+        """
+        ee = self._Annotated__element  # type: ignore
+        return ee.c  # type: ignore
diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py
index 1f9944529..ec8ea757f 100644
--- a/lib/sqlalchemy/sql/util.py
+++ b/lib/sqlalchemy/sql/util.py
@@ -1203,7 +1203,9 @@ class ClauseAdapter(visitors.ReplacingExternalTraversal):
         elif self.exclude_fn and self.exclude_fn(col):
             return None
         else:
-            return self._corresponding_column(col, True)  # type: ignore
+            return self._corresponding_column(  # type: ignore
+                col, require_embedded=True
+            )
 
 
 class _ColumnLookup(Protocol):
diff --git a/lib/sqlalchemy/testing/fixtures.py b/lib/sqlalchemy/testing/fixtures.py
index 5fb547cbc..dcee3f18b 100644
--- a/lib/sqlalchemy/testing/fixtures.py
+++ b/lib/sqlalchemy/testing/fixtures.py
@@ -27,6 +27,7 @@ from .util import drop_all_tables_from_metadata
 from .. import event
 from .. import util
 from ..orm import DeclarativeBase
+from ..orm import events as orm_events
 from ..orm import MappedAsDataclass
 from ..orm import registry
 from ..schema import sort_tables_and_constraints
@@ -618,6 +619,17 @@ class RemovesEvents:
             event.remove(*key)
 
 
+class RemoveORMEventsGlobally:
+    @config.fixture(autouse=True)
+    def _remove_listeners(self):
+        yield
+        orm_events.MapperEvents._clear()
+        orm_events.InstanceEvents._clear()
+        orm_events.SessionEvents._clear()
+        orm_events.InstrumentationEvents._clear()
+        orm_events.QueryEvents._clear()
+
+
 _fixture_sessions = set()
 
 
diff --git a/lib/sqlalchemy/util/preloaded.py b/lib/sqlalchemy/util/preloaded.py
index 6332b3c94..ae700b77d 100644
--- a/lib/sqlalchemy/util/preloaded.py
+++ b/lib/sqlalchemy/util/preloaded.py
@@ -35,7 +35,7 @@ if TYPE_CHECKING:
     from sqlalchemy.orm import decl_base as _orm_decl_base
     from sqlalchemy.orm import dependency as _orm_dependency
     from sqlalchemy.orm import descriptor_props as _orm_descriptor_props
-    from sqlalchemy.orm import mapper as _orm_mapper
+    from sqlalchemy.orm import mapperlib as _orm_mapper
     from sqlalchemy.orm import properties as _orm_properties
     from sqlalchemy.orm import relationships as _orm_relationships
     from sqlalchemy.orm import session as _orm_session
diff --git a/test/aaa_profiling/test_misc.py b/test/aaa_profiling/test_misc.py
index 13848a7bd..a0f56ef25 100644
--- a/test/aaa_profiling/test_misc.py
+++ b/test/aaa_profiling/test_misc.py
@@ -2,12 +2,15 @@ import sqlalchemy
 from sqlalchemy import Column
 from sqlalchemy import Enum
 from sqlalchemy import ForeignKey
+from sqlalchemy import inspect
 from sqlalchemy import Integer
 from sqlalchemy import MetaData
 from sqlalchemy import select
 from sqlalchemy import String
 from sqlalchemy import Table
 from sqlalchemy import testing
+from sqlalchemy.ext.declarative import ConcreteBase
+from sqlalchemy.orm import aliased
 from sqlalchemy.orm import join as ormjoin
 from sqlalchemy.orm import relationship
 from sqlalchemy.testing import eq_
@@ -146,3 +149,239 @@ class CacheKeyTest(fixtures.TestBase):
                     current_key = key
 
         go()
+
+
+class CCLookupTest(fixtures.RemoveORMEventsGlobally, fixtures.TestBase):
+    __requires__ = ("cpython", "python_profiling_backend")
+
+    @testing.fixture
+    def t1(self, metadata):
+        return Table(
+            "t1",
+            metadata,
+            Column("id", Integer, primary_key=True),
+            Column("x1", Integer),
+            Column("x2", Integer),
+            Column("x3", Integer),
+            Column("x4", Integer),
+            Column("x5", Integer),
+            Column("x6", Integer),
+            Column("x7", Integer),
+            Column("x8", Integer),
+            Column("x9", Integer),
+            Column("x10", Integer),
+        )
+
+    @testing.fixture
+    def inheritance_model(self, decl_base):
+        class Employee(ConcreteBase, decl_base):
+            __tablename__ = "employee"
+            id = Column(Integer, primary_key=True)
+            name = Column(String(50))
+
+            x1 = Column(Integer)
+            x2 = Column(Integer)
+            x3 = Column(Integer)
+            x4 = Column(Integer)
+            x5 = Column(Integer)
+            x6 = Column(Integer)
+            x7 = Column(Integer)
+            x8 = Column(Integer)
+            x9 = Column(Integer)
+            x10 = Column(Integer)
+            x11 = Column(Integer)
+            x12 = Column(Integer)
+            x13 = Column(Integer)
+            x14 = Column(Integer)
+            x15 = Column(Integer)
+            x16 = Column(Integer)
+
+            __mapper_args__ = {
+                "polymorphic_identity": "employee",
+                "concrete": True,
+            }
+
+        class Manager(Employee):
+            __tablename__ = "manager"
+            id = Column(Integer, primary_key=True)
+            name = Column(String(50))
+            manager_data = Column(String(40))
+
+            m1 = Column(Integer)
+            m2 = Column(Integer)
+            m3 = Column(Integer)
+            m4 = Column(Integer)
+            m5 = Column(Integer)
+            m6 = Column(Integer)
+            m7 = Column(Integer)
+            m8 = Column(Integer)
+            m9 = Column(Integer)
+            m10 = Column(Integer)
+            m11 = Column(Integer)
+            m12 = Column(Integer)
+            m13 = Column(Integer)
+            m14 = Column(Integer)
+            m15 = Column(Integer)
+            m16 = Column(Integer)
+
+            __mapper_args__ = {
+                "polymorphic_identity": "manager",
+                "concrete": True,
+            }
+
+        class Engineer(Employee):
+            __tablename__ = "engineer"
+            id = Column(Integer, primary_key=True)
+            name = Column(String(50))
+            engineer_info = Column(String(40))
+
+            e1 = Column(Integer)
+            e2 = Column(Integer)
+            e3 = Column(Integer)
+            e4 = Column(Integer)
+            e5 = Column(Integer)
+            e6 = Column(Integer)
+            e7 = Column(Integer)
+            e8 = Column(Integer)
+            e9 = Column(Integer)
+            e10 = Column(Integer)
+            e11 = Column(Integer)
+            e12 = Column(Integer)
+            e13 = Column(Integer)
+            e14 = Column(Integer)
+            e15 = Column(Integer)
+            e16 = Column(Integer)
+
+            __mapper_args__ = {
+                "polymorphic_identity": "engineer",
+                "concrete": True,
+            }
+
+        decl_base.registry.configure()
+
+        return Employee
+
+    @testing.combinations(
+        ("require_embedded",), ("no_embedded",), argnames="require_embedded"
+    )
+    def test_corresponding_column_isolated(self, t1, require_embedded):
+
+        subq = select(t1).union_all(select(t1)).subquery()
+
+        target = subq.c.x7
+        src = t1.c.x7
+
+        subq.c
+
+        require_embedded = require_embedded == "require_embedded"
+
+        @profiling.function_call_count(variance=0.15, warmup=1)
+        def go():
+            assert (
+                subq.corresponding_column(
+                    src, require_embedded=require_embedded
+                )
+                is target
+            )
+
+        go()
+
+    @testing.combinations(
+        ("require_embedded",), ("no_embedded",), argnames="require_embedded"
+    )
+    def test_gen_subq_to_table_single_corresponding_column(
+        self, t1, require_embedded
+    ):
+
+        src = t1.c.x7
+
+        require_embedded = require_embedded == "require_embedded"
+
+        @profiling.function_call_count(variance=0.15, warmup=1)
+        def go():
+            subq = select(t1).union_all(select(t1)).subquery()
+
+            target = subq.c.x7
+            assert (
+                subq.corresponding_column(
+                    src, require_embedded=require_embedded
+                )
+                is target
+            )
+
+        go()
+
+    @testing.combinations(
+        ("require_embedded",), ("no_embedded",), argnames="require_embedded"
+    )
+    def test_gen_subq_to_table_many_corresponding_column(
+        self, t1, require_embedded
+    ):
+
+        require_embedded = require_embedded == "require_embedded"
+
+        @profiling.function_call_count(variance=0.15, warmup=1)
+        def go():
+            subq = select(t1).union_all(select(t1)).subquery()
+
+            for name in ("x%d" % i for i in range(1, 10)):
+
+                target = subq.c[name]
+                src = t1.c[name]
+
+                assert (
+                    subq.corresponding_column(
+                        src, require_embedded=require_embedded
+                    )
+                    is target
+                )
+
+        go()
+
+    @testing.combinations(
+        ("require_embedded",), ("no_embedded",), argnames="require_embedded"
+    )
+    def test_gen_subq_aliased_class_select(
+        self, t1, require_embedded, inheritance_model
+    ):
+
+        A = inheritance_model
+
+        require_embedded = require_embedded == "require_embedded"
+
+        @profiling.function_call_count(variance=0.15, warmup=1)
+        def go():
+
+            a1a1 = aliased(A)
+            a1a2 = aliased(A)
+            subq = select(a1a1).union_all(select(a1a2)).subquery()
+
+            a1 = aliased(A, subq)
+
+            inspect(a1).__clause_element__()
+
+        go()
+
+    @testing.combinations(
+        ("require_embedded",), ("no_embedded",), argnames="require_embedded"
+    )
+    def test_gen_subq_aliased_class_select_cols(
+        self, t1, require_embedded, inheritance_model
+    ):
+
+        A = inheritance_model
+
+        require_embedded = require_embedded == "require_embedded"
+
+        @profiling.function_call_count(variance=0.15, warmup=1)
+        def go():
+
+            a1a1 = aliased(A)
+            a1a2 = aliased(A)
+            subq = select(a1a1).union_all(select(a1a2)).subquery()
+
+            a1 = aliased(A, subq)
+
+            select(a1.x1, a1.x2, a1.x3, a1.x4)
+
+        go()
diff --git a/test/profiles.txt b/test/profiles.txt
index 0b7c5c3e3..d54496b0e 100644
--- a/test/profiles.txt
+++ b/test/profiles.txt
@@ -1,15 +1,15 @@
 # /home/classic/dev/sqlalchemy/test/profiles.txt
 # This file is written out on a per-environment basis.
-# For each test in aaa_profiling, the corresponding function and
+# For each test in aaa_profiling, the corresponding function and 
 # environment is located within this file.  If it doesn't exist,
 # the test is skipped.
-# If a callcount does exist, it is compared to what we received.
+# If a callcount does exist, it is compared to what we received. 
 # assertions are raised if the counts do not match.
-#
-# To add a new callcount test, apply the function_call_count
-# decorator and re-run the tests using the --write-profiles
+# 
+# To add a new callcount test, apply the function_call_count 
+# decorator and re-run the tests using the --write-profiles 
 # option - this file will be rewritten including the new count.
-#
+# 
 
 # TEST: test.aaa_profiling.test_compiler.CompileTest.test_insert
 
@@ -81,6 +81,68 @@ test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linu
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 180
 test.aaa_profiling.test_compiler.CompileTest.test_update_whereclause x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 180
 
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached
+
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 44
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 44
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_cached[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 70
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 78
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 78
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 104
+test.aaa_profiling.test_misc.CCLookupTest.test_corresponding_column_isolated[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 104
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 13336
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 13354
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 13336
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 13354
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 81494
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 88011
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 89084
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_aliased_class_select_cols[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 83019
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1716
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1785
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1734
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_many_corresponding_column[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1803
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[no_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1735
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[no_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1804
+
+# TEST: test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[require_embedded]
+
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1737
+test.aaa_profiling.test_misc.CCLookupTest.test_gen_subq_to_table_single_corresponding_column[require_embedded] x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1806
+
 # TEST: test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_cached
 
 test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 303
@@ -88,58 +150,58 @@ test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_cached x86_64_li
 
 # TEST: test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_not_cached
 
-test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_not_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 5103
-test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_not_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 6203
+test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_not_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 5003
+test.aaa_profiling.test_misc.CacheKeyTest.test_statement_key_is_not_cached x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 6103
 
 # TEST: test.aaa_profiling.test_misc.EnumTest.test_create_enum_from_pep_435_w_expensive_members
 
-test.aaa_profiling.test_misc.EnumTest.test_create_enum_from_pep_435_w_expensive_members x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 922
-test.aaa_profiling.test_misc.EnumTest.test_create_enum_from_pep_435_w_expensive_members x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 922
+test.aaa_profiling.test_misc.EnumTest.test_create_enum_from_pep_435_w_expensive_members x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 924
+test.aaa_profiling.test_misc.EnumTest.test_create_enum_from_pep_435_w_expensive_members x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 924
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_w_annotation
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_w_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 53630
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_w_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 63940
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_w_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 54230
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_w_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 64540
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_wo_annotation
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_wo_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 51830
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_wo_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 62140
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_wo_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 52530
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_bundle_wo_annotation x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 62840
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_w_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 56130
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 64540
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 57130
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 65540
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_wo_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 55130
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 63540
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 56230
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 64640
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 47330
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 50640
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 47930
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 51240
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_w_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 50830
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 58640
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 51430
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 59240
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_wo_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 49830
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 57640
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 50530
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_bundle_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 58340
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_w_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 35605
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 38805
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 36205
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_w_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 39405
 
 # TEST: test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_wo_annotations
 
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 34605
-test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 37805
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 35305
+test.aaa_profiling.test_orm.AnnotatedOverheadTest.test_no_entity_wo_annotations x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 38505
 
 # TEST: test.aaa_profiling.test_orm.AttributeOverheadTest.test_attribute_set
 
@@ -163,18 +225,18 @@ test.aaa_profiling.test_orm.BranchedOptionTest.test_query_opts_unbound_branching
 
 # TEST: test.aaa_profiling.test_orm.DeferOptionsTest.test_baseline
 
-test.aaa_profiling.test_orm.DeferOptionsTest.test_baseline x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 15324
-test.aaa_profiling.test_orm.DeferOptionsTest.test_baseline x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 26343
+test.aaa_profiling.test_orm.DeferOptionsTest.test_baseline x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 15331
+test.aaa_profiling.test_orm.DeferOptionsTest.test_baseline x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 26350
 
 # TEST: test.aaa_profiling.test_orm.DeferOptionsTest.test_defer_many_cols
 
-test.aaa_profiling.test_orm.DeferOptionsTest.test_defer_many_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 21378
-test.aaa_profiling.test_orm.DeferOptionsTest.test_defer_many_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 26397
+test.aaa_profiling.test_orm.DeferOptionsTest.test_defer_many_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 21409
+test.aaa_profiling.test_orm.DeferOptionsTest.test_defer_many_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 26428
 
 # TEST: test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased
 
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 10854
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 11004
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 10954
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 11204
 
 # TEST: test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased_select_join
 
@@ -183,18 +245,18 @@ test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_aliased_select_join x8
 
 # TEST: test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_plain
 
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_plain x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 4204
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_plain x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 4354
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_plain x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 4304
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_b_plain x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 4554
 
 # TEST: test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d
 
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 101006
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 106756
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 101506
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 107756
 
 # TEST: test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d_aliased
 
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 99074
-test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 104824
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 99556
+test.aaa_profiling.test_orm.JoinConditionTest.test_a_to_d_aliased x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 105806
 
 # TEST: test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results
 
@@ -203,8 +265,8 @@ test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results x86_64_linux_
 
 # TEST: test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated
 
-test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 29440,1011,95853
-test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 29847,1195,114253
+test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 29284,1030,97753
+test.aaa_profiling.test_orm.JoinedEagerLoadTest.test_fetch_results_integrated x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 29480,1216,116353
 
 # TEST: test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_identity
 
@@ -213,33 +275,33 @@ test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_
 
 # TEST: test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_no_identity
 
-test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_no_identity x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 110202
-test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_no_identity x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 118459
+test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_no_identity x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 112135
+test.aaa_profiling.test_orm.LoadManyToOneFromIdentityTest.test_many_to_one_load_no_identity x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 120392
 
 # TEST: test.aaa_profiling.test_orm.MergeBackrefsTest.test_merge_pending_with_all_pks
 
-test.aaa_profiling.test_orm.MergeBackrefsTest.test_merge_pending_with_all_pks x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 20432
-test.aaa_profiling.test_orm.MergeBackrefsTest.test_merge_pending_with_all_pks x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 21842
+test.aaa_profiling.test_orm.MergeBackrefsTest.test_merge_pending_with_all_pks x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 20564
+test.aaa_profiling.test_orm.MergeBackrefsTest.test_merge_pending_with_all_pks x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 21974
 
 # TEST: test.aaa_profiling.test_orm.MergeTest.test_merge_load
 
-test.aaa_profiling.test_orm.MergeTest.test_merge_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1413
-test.aaa_profiling.test_orm.MergeTest.test_merge_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1502
+test.aaa_profiling.test_orm.MergeTest.test_merge_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 1428
+test.aaa_profiling.test_orm.MergeTest.test_merge_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 1517
 
 # TEST: test.aaa_profiling.test_orm.MergeTest.test_merge_no_load
 
-test.aaa_profiling.test_orm.MergeTest.test_merge_no_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 104,19
-test.aaa_profiling.test_orm.MergeTest.test_merge_no_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 104,19
+test.aaa_profiling.test_orm.MergeTest.test_merge_no_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 110,20
+test.aaa_profiling.test_orm.MergeTest.test_merge_no_load x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 110,20
 
 # TEST: test.aaa_profiling.test_orm.QueryTest.test_query_cols
 
-test.aaa_profiling.test_orm.QueryTest.test_query_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 6442
-test.aaa_profiling.test_orm.QueryTest.test_query_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 7262
+test.aaa_profiling.test_orm.QueryTest.test_query_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 6504
+test.aaa_profiling.test_orm.QueryTest.test_query_cols x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 7324
 
 # TEST: test.aaa_profiling.test_orm.SelectInEagerLoadTest.test_round_trip_results
 
-test.aaa_profiling.test_orm.SelectInEagerLoadTest.test_round_trip_results x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 266105
-test.aaa_profiling.test_orm.SelectInEagerLoadTest.test_round_trip_results x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 288405
+test.aaa_profiling.test_orm.SelectInEagerLoadTest.test_round_trip_results x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_cextensions 270705
+test.aaa_profiling.test_orm.SelectInEagerLoadTest.test_round_trip_results x86_64_linux_cpython_3.10_sqlite_pysqlite_dbapiunicode_nocextensions 292105
 
 # TEST: test.aaa_profiling.test_orm.SessionTest.test_expire_lots
 
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index 05847b1d0..897d6c5ff 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -3167,33 +3167,75 @@ class AnnotationsTest(fixtures.TestBase):
         binary_2 = col_anno == 5
         eq_(binary_2.left._annotations, {"foo": "bar"})
 
-    def test_annotated_corresponding_column(self):
+    @testing.combinations(
+        ("plain",),
+        ("annotated",),
+        ("deep_annotated",),
+        ("deep_annotated_w_ind_col",),
+        argnames="testcase",
+    )
+    def test_annotated_corresponding_column(self, testcase):
+        """ensures the require_embedded case remains when an inner statement
+        was copied out for annotations.
+
+        First implemented in 2008 in d3621ae961a, the implementation is
+        updated for #8796 as a performance improvement as well as to
+        establish a discovered implicit behavior where clone() would break
+        the contract of corresponding_column() into an explicit option,
+        fixing the implicit behavior.
+
+        """
         table1 = table("table1", column("col1"))
 
         s1 = select(table1.c.col1).subquery()
-        t1 = s1._annotate({})
-        t2 = s1
+
+        expect_same = True
+
+        if testcase == "plain":
+            t1 = s1
+        elif testcase == "annotated":
+            t1 = s1._annotate({})
+        elif testcase == "deep_annotated":
+            # was failing prior to #8796
+            t1 = sql_util._deep_annotate(s1, {"foo": "bar"})
+        elif testcase == "deep_annotated_w_ind_col":
+            # was implicit behavior w/ annotate prior to #8796
+            t1 = sql_util._deep_annotate(
+                s1, {"foo": "bar"}, ind_cols_on_fromclause=True
+            )
+            expect_same = False
+        else:
+            assert False
 
         # t1 needs to share the same _make_proxy() columns as t2, even
         # though it's annotated.  otherwise paths will diverge once they
         # are corresponded against "inner" below.
 
-        assert t1.c is t2.c
-        assert t1.c.col1 is t2.c.col1
+        if expect_same:
+            assert t1.c is s1.c
+            assert t1.c.col1 is s1.c.col1
+        else:
+            assert t1.c is not s1.c
+            assert t1.c.col1 is not s1.c.col1
 
         inner = select(s1).subquery()
 
         assert (
-            inner.corresponding_column(t2.c.col1, require_embedded=False)
-            is inner.corresponding_column(t2.c.col1, require_embedded=True)
-            is inner.c.col1
-        )
-        assert (
             inner.corresponding_column(t1.c.col1, require_embedded=False)
-            is inner.corresponding_column(t1.c.col1, require_embedded=True)
             is inner.c.col1
         )
 
+        if expect_same:
+            assert (
+                inner.corresponding_column(t1.c.col1, require_embedded=True)
+                is inner.c.col1
+            )
+        else:
+            assert (
+                inner.corresponding_column(t1.c.col1, require_embedded=True)
+                is not inner.c.col1
+            )
+
     def test_annotated_visit(self):
         table1 = table("table1", column("col1"), column("col2"))