don't invoke fromclause.c when creating an annotated

The ``aliased()`` constructor calls upon ``__clause_element__()``,
which internally annotates a ``FromClause``, like a subquery.
This became expensive as ``AnnotatedFromClause`` has for
many years called upon ``element.c`` so that the full ``.c``
collection is transferred to the Annotated.

Taking this out proved to be challenging. A straight remove
seemed to not break any tests except for the one that
tested the exact condition.  Nevertheless this seemed
"spooky" so I instead moved the get of ``.c`` to be in a
memoized proxy method.   However, that then exposed
a recursion issue related to loader_criteria; so the
source of that behavior, which was an accidental behavioral
artifact, is now made into an explcicit option that
loader_criteria uses directly.

The accidental behavioral artifact in question is still
kind of strange since I was not able to fully trace out
how it works, but the end result is that fixing the
artifact to be "correct" causes loader_criteria, within
the particular test for #7491, creates a select/
subquery structure with a cycle in it, so compilation fails
with recursion overflow.
The "solution" is to cause the artifact to occur in this
case, which is that the ``AnnotatedFromClause`` will have a
different ``.c`` collection than its element, which is a
subquery.  It's not totally clear how a cycle is generated
when this is not done.

This is commit one of two, which goes through
some hoops to make essentially a one-line change.

The next commit will rework ColumnCollection to optimize
the corresponding_column() method significantly.

Fixes: #8796
Change-Id: Id58ae6554db62139462c11a8be7313a3677456ad

1 files changed, 239 insertions, 0 deletions

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()