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, 12 insertions, 0 deletions

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