Remove ``typing.Self`` workaround, now using :pep:`673` for most methods
that return ``Self``. Pull request courtesy Yurii Karabas.

Fixes: #9254
Closes: #9255
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/9255
Pull-request-sha: 2947df8ada79f5c3afe9c838e65993302199c2f7

Change-Id: Ic32015ad52e95a61f3913d43ea436aa9402804df

Change-Id: I625af65b3fb1815b1af17dc2ef47dd697fdc3fb1

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

We can cache the annotated cache key for Table, but
for selectables it's not safe, as it fails to pass the
anon_map along and creates many redudant structures in
observed test scenario.  It is likely safe for a
Column that's mapped to a Table also, however this is
not implemented here.   Will have to see if that part
needs adjusting.

Fixed critical memory issue identified in cache key generation, where for
very large and complex ORM statements that make use of lots of ORM aliases
with subqueries, cache key generation could produce excessively large keys
that were orders of magnitude bigger than the statement itself. Much thanks
to Rollo Konig Brock for their very patient, long term help in finally
identifying this issue.

Also within TypeEngine objects, when we generate elements
for instance variables, skip the None elements at least.
this also saves on tuple complexity.

Fixes: #8790
Change-Id: I448ddbfb45ae0a648815be8dad4faad7d1977427

* ORM Insert now includes "bulk" mode that will run
  essentially the same process as session.bulk_insert_mappings;
  interprets the given list of values as ORM attributes for
  key names
* ORM UPDATE has a similar feature, without RETURNING support,
  for session.bulk_update_mappings
* Added support for upserts to do RETURNING ORM objects as well
* ORM UPDATE/DELETE with list of parameters + WHERE criteria
  is a not implemented; use connection
* ORM UPDATE/DELETE defaults to "auto" synchronize_session;
  use fetch if RETURNING is present, evaluate if not, as
  "fetch" is much more efficient (no expired object SELECT problem)
  and less error prone if RETURNING is available
  UPDATE: howver this is inefficient!   please continue to
  use evaluate for simple cases, auto can move to fetch
  if criteria not evaluable
* "Evaluate" criteria will now not preemptively
  unexpire and SELECT attributes that were individually
  expired. Instead, if evaluation of the criteria indicates that
  the necessary attrs were expired, we expire the object
  completely (delete) or expire the SET attrs unconditionally
  (update). This keeps the object in the same unloaded state
  where it will refresh those attrs on the next pass, for
  this generally unusual case.  (originally #5664)
* Core change! update/delete rowcount comes from len(rows)
  if RETURNING was used.  SQLite at least otherwise did not
  support this.  adjusted test_rowcount accordingly
* ORM DELETE with a list of parameters at all is also a not
  implemented as this would imply "bulk", and there is no
  bulk_delete_mappings (could be, but we dont have that)
* ORM insert().values() with single or multi-values translates
  key names based on ORM attribute names
* ORM returning() implemented for insert, update, delete;
  explcit returning clauses now interpret rows in an ORM
  context, with support for qualifying loader options as well
* session.bulk_insert_mappings() assigns polymorphic identity
  if not set.
* explicit RETURNING + synchronize_session='fetch' is now
  supported with UPDATE and DELETE.
* expanded return_defaults() to work with DELETE also.
* added support for composite attributes to be present
  in the dictionaries used by bulk_insert_mappings and
  bulk_update_mappings, which is also the new ORM bulk
  insert/update feature, that will expand the composite
  values into their individual mapped attributes the way they'd
  be on a mapped instance.
* bulk UPDATE supports "synchronize_session=evaluate", is the
  default.  this does not apply to session.bulk_update_mappings,
  just the new version
* both bulk UPDATE and bulk INSERT, the latter with or without
  RETURNING, support *heterogenous* parameter sets.
  session.bulk_insert/update_mappings did this, so this feature
  is maintained.  now cursor result can be both horizontally
  and vertically spliced :)

This is now a long story with a lot of options, which in
itself is a problem to be able to document all of this
in some way that makes sense.  raising exceptions for
use cases we haven't supported is pretty important here
too, the tradition of letting unsupported things just not work
is likely not a good idea at this point, though there
are still many cases that aren't easily avoidable

Fixes: #8360
Fixes: #7864
Fixes: #7865
Change-Id: Idf28379f8705e403a3c6a937f6a798a042ef2540

Fixed event listening issue where event listeners added to a superclass
would be lost if a subclass were created which then had its own listeners
associated. The practical example is that of the :class:`.sessionmaker`
class created after events have been associated with the
:class:`_orm.Session` class.

Fixes: #8467
Change-Id: I9bdba8769147e30110a09900d4a577e833ac3af9

* extract the inner type from Annotated when the outer type
  isn't present in the type map, to allow for arbitrary Annotated
* allow _IntrospectsAnnotations objects to be directly present
  in an Annotated and resolve the mapper property from that.
  Currently implemented for mapped_column(), with message for
  others.  Can work for composite() and likely some
  relationship() as well at some point

References: https://twitter.com/zzzeek/status/1536693554621341697 and
replies

Change-Id: I04657050a8785f194bf8f63291faf3475af88781

trying to get remaining must-haves for ORM

Change-Id: I66a3ecbbb8e5ba37c818c8a92737b576ecf012f7

the pep484 task becomes more intense as there is mounting
pressure to come up with a consistency in how data moves
from end-user to instance variable.

current thinking is coming into:

1. there are _typing._XYZArgument objects that represent "what the
   user sent"
2. there's the roles, which represent a kind of "filter" for different
   kinds of objects.   These are mostly important as the argument
   we pass to coerce().
3. there's the thing that coerce() returns, which should be what the
   construct uses as its internal representation of the thing.
   This is _typing._XYZElement.

but there's some controversy over whether or
not we should pass actual ClauseElements around by their role
or not.   I think we shouldn't at the moment, but this makes the
"role-ness" of something a little less portable. Like, we have
to set DMLTableRole for TableClause, Join, and Alias, but then
also we have to repeat those three types in order to set up
_DMLTableElement.

Other change introduced here, there was a deannotate=True
for the left/right of a sql.join().    All tests pass without that.
I'd rather not have that there as if we have a join(A, B) where
A, B are mapped classes, we want them inside of the _annotations.
The rationale seems to be performance, but this performance can
be illustrated to be on the compile side which we hope is cached
in the normal case.

CTEs now accommodate for text selects including recursive.

Get typing to accommodate "util.preloaded" cleanly; add "preloaded"
as a real module.  This seemed like we would have needed
pep562 `__getattr__()` but we don't, just set names in
globals() as we import them.

References: #6810
Change-Id: I34d17f617de2fe2c086fc556bd55748dc782faf0