:released: May 5, 2024

 .. change::
     :tags: bug, typing, regression
     :tickets: 11200

     Fixed typing regression caused by :ticket:`11055` in version 2.0.29 that
     added ``ParamSpec`` to the asyncio ``run_sync()`` methods, where using
     :meth:`_asyncio.AsyncConnection.run_sync` with
     :meth:`_schema.MetaData.reflect` would fail on mypy due to a mypy issue.
     Pull request courtesy of Francisco R. Del Roio.

 .. change::
     :tags: bug, engine
     :tickets: 11210

     Fixed issue in the
     :paramref:`_engine.Connection.execution_options.logging_token` option,
     where changing the value of ``logging_token`` on a connection that has
     already logged messages would not be updated to reflect the new logging
     token.  This in particular prevented the use of
     :meth:`_orm.Session.connection` to change the option on the connection,
     since the BEGIN logging message would already have been emitted.

 .. change::
     :tags: bug, orm
     :tickets: 11220

     Added new attribute :attr:`_orm.ORMExecuteState.is_from_statement` to
     detect statements created using :meth:`_sql.Select.from_statement`, and
     enhanced ``FromStatement`` to set :attr:`_orm.ORMExecuteState.is_select`,
     :attr:`_orm.ORMExecuteState.is_insert`,
     :attr:`_orm.ORMExecuteState.is_update`, and
     :attr:`_orm.ORMExecuteState.is_delete` according to the element that is
     sent to the :meth:`_sql.Select.from_statement` method itself.

 .. change::
     :tags: bug, test
     :tickets: 11268

     Ensure the ``PYTHONPATH`` variable is properly initialized when
     using ``subprocess.run`` in the tests.

 .. change::
     :tags: bug, orm
     :tickets: 11291

     Fixed issue in  :func:`_orm.selectin_polymorphic` loader option where
     attributes defined with :func:`_orm.composite` on a superclass would cause
     an internal exception on load.


 .. change::
     :tags: bug, orm, regression
     :tickets: 11292

     Fixed regression from 1.4 where using :func:`_orm.defaultload` in
     conjunction with a non-propagating loader like :func:`_orm.contains_eager`
     would nonetheless propagate the :func:`_orm.contains_eager` to a lazy load
     operation, causing incorrect queries as this option is only intended to
     come from an original load.



 .. change::
     :tags: bug, orm
     :tickets: 11305

     Fixed issue in ORM Annotated Declarative where typing issue where literals
     defined using :pep:`695` type aliases would not work with inference of
     :class:`.Enum` datatypes. Pull request courtesy of Alc-Alc.

 .. change::
     :tags: bug, engine
     :tickets: 11306

     Fixed issue in cursor handling which affected handling of duplicate
     :class:`_sql.Column` or similar objcts in the columns clause of
     :func:`_sql.select`, both in combination with arbitary :func:`_sql.text()`
     clauses in the SELECT list, as well as when attempting to retrieve
     :meth:`_engine.Result.mappings` for the object, which would lead to an
     internal error.



 .. change::
     :tags: bug, orm
     :tickets: 11327

     Fixed issue in :func:`_orm.selectin_polymorphic` loader option where the
     SELECT emitted would only accommodate for the child-most class among the
     result rows that were returned, leading intermediary-class attributes to be
     unloaded if there were no concrete instances of that intermediary-class
     present in the result.   This issue only presented itself for multi-level
     inheritance hierarchies.

 .. change::
     :tags: bug, orm
     :tickets: 11332

     Fixed issue in :meth:`_orm.Session.bulk_save_objects` where the form of the
     identity key produced when using ``return_defaults=True`` would be
     incorrect. This could lead to an errors during pickling as well as identity
     map mismatches.

 .. change::
     :tags: bug, installation
     :tickets: 11334

     Fixed an internal class that was testing for unexpected attributes to work
     correctly under upcoming Python 3.13.   Pull request courtesy Edgar
     Ramírez-Mondragón.

 .. change::
     :tags: bug, orm
     :tickets: 11347

     Fixed issue where attribute key names in :class:`_orm.Bundle` would not be
     correct when using ORM enabled :class:`_sql.select` vs.
     :class:`_orm.Query`, when the statement contained duplicate column names.

 .. change::
     :tags: bug, typing

     Fixed issue in typing for :class:`_orm.Bundle` where creating a nested
     :class:`_orm.Bundle` structure were not allowed.

.. changelog::

:released: March 23, 2024

 .. change::
     :tags: bug, orm
     :tickets: 10611

     Fixed Declarative issue where typing a relationship using
     :class:`_orm.Relationship` rather than :class:`_orm.Mapped` would
     inadvertently pull in the "dynamic" relationship loader strategy for that
     attribute.

 .. change::
     :tags: postgresql, usecase
     :tickets: 10693

     The PostgreSQL dialect now returns :class:`_postgresql.DOMAIN` instances
     when reflecting a column that has a domain as type. Previously, the domain
     data type was returned instead. As part of this change, the domain
     reflection was improved to also return the collation of the text types.
     Pull request courtesy of Thomas Stephenson.

 .. change::
     :tags: bug, typing
     :tickets: 11055

     Fixed typing issue allowing asyncio ``run_sync()`` methods to correctly
     type the parameters according to the callable that was passed, making use
     of :pep:`612` ``ParamSpec`` variables.  Pull request courtesy Francisco R.
     Del Roio.

 .. change::
     :tags: bug, orm
     :tickets: 11091

     Fixed issue in ORM annotated declarative where using
     :func:`_orm.mapped_column()` with an :paramref:`_orm.mapped_column.index`
     or :paramref:`_orm.mapped_column.unique` setting of False would be
     overridden by an incoming ``Annotated`` element that featured that
     parameter set to ``True``, even though the immediate
     :func:`_orm.mapped_column()` element is more specific and should take
     precedence.  The logic to reconcile the booleans has been enhanced to
     accommodate a local value of ``False`` as still taking precedence over an
     incoming ``True`` value from the annotated element.

 .. change::
     :tags: usecase, orm
     :tickets: 11130

     Added support for the :pep:`695` ``TypeAliasType`` construct as well as the
     python 3.12 native ``type`` keyword to work with ORM Annotated Declarative
     form when using these constructs to link to a :pep:`593` ``Annotated``
     container, allowing the resolution of the ``Annotated`` to proceed when
     these constructs are used in a :class:`_orm.Mapped` typing container.

 .. change::
     :tags: bug, engine
     :tickets: 11157

     Fixed issue in :ref:`engine_insertmanyvalues` feature where using a primary
     key column with an "inline execute" default generator such as an explicit
     :class:`.Sequence` with an explcit schema name, while at the same time
     using the
     :paramref:`_engine.Connection.execution_options.schema_translate_map`
     feature would fail to render the sequence or the parameters properly,
     leading to errors.

 .. change::
     :tags: bug, engine
     :tickets: 11160

     Made a change to the adjustment made in version 2.0.10 for :ticket:`9618`,
     which added the behavior of reconciling RETURNING rows from a bulk INSERT
     to the parameters that were passed to it.  This behavior included a
     comparison of already-DB-converted bound parameter values against returned
     row values that was not always "symmetrical" for SQL column types such as
     UUIDs, depending on specifics of how different DBAPIs receive such values
     versus how they return them, necessitating the need for additional
     "sentinel value resolver" methods on these column types.  Unfortunately
     this broke third party column types such as UUID/GUID types in libraries
     like SQLModel which did not implement this special method, raising an error
     "Can't match sentinel values in result set to parameter sets".  Rather than
     attempt to further explain and document this implementation detail of the
     "insertmanyvalues" feature including a public version of the new
     method, the approach is intead revised to no longer need this extra
     conversion step, and the logic that does the comparison now works on the
     pre-converted bound parameter value compared to the post-result-processed
     value, which should always be of a matching datatype.  In the unusual case
     that a custom SQL column type that also happens to be used in a "sentinel"
     column for bulk INSERT is not receiving and returning the same value type,
     the "Can't match" error will be raised, however the mitigation is
     straightforward in that the same Python datatype should be passed as that
     returned.

 .. change::
     :tags: bug, orm, regression
     :tickets: 11173

     Fixed regression from version 2.0.28 caused by the fix for :ticket:`11085`
     where the newer method of adjusting post-cache bound parameter values would
     interefere with the implementation for the :func:`_orm.subqueryload` loader
     option, which has some more legacy patterns in use internally, when
     the additional loader criteria feature were used with this loader option.

 .. change::
     :tags: bug, sql, regression
     :tickets: 11176

     Fixed regression from the 1.4 series where the refactor of the
     :meth:`_types.TypeEngine.with_variant` method introduced at
     :ref:`change_6980` failed to accommodate for the ``.copy()`` method, which
     will lose the variant mappings that are set up. This becomes an issue for
     the very specific case of a "schema" type, which includes types such as
     :class:`.Enum` and :class:`_types.ARRAY`, when they are then used in the context
     of an ORM Declarative mapping with mixins where copying of types comes into
     play.  The variant mapping is now copied as well.

 .. change::
     :tags: bug, tests
     :tickets: 11187

     Backported to SQLAlchemy 2.0 an improvement to the test suite with regards
     to how asyncio related tests are run, now using the newer Python 3.11
     ``asyncio.Runner`` or a backported equivalent, rather than relying on the
     previous implementation based on ``asyncio.get_running_loop()``.  This
     should hopefully prevent issues with large suite runs on CPU loaded
     hardware where the event loop seems to become corrupted, leading to
     cascading failures.


.. changelog::

:released: March 4, 2024

 .. change::
     :tags: engine, usecase
     :tickets: 10974

     Added new core execution option
     :paramref:`_engine.Connection.execution_options.preserve_rowcount`. When
     set, the ``cursor.rowcount`` attribute from the DBAPI cursor will be
     unconditionally memoized at statement execution time, so that whatever
     value the DBAPI offers for any kind of statement will be available using
     the :attr:`_engine.CursorResult.rowcount` attribute from the
     :class:`_engine.CursorResult`.  This allows the rowcount to be accessed for
     statements such as INSERT and SELECT, to the degree supported by the DBAPI
     in use. The :ref:`engine_insertmanyvalues` also supports this option and
     will ensure :attr:`_engine.CursorResult.rowcount` is correctly set for a
     bulk INSERT of rows when set.

 .. change::
     :tags: bug, orm, regression
     :tickets: 11010

     Fixed regression caused by :ticket:`9779` where using the "secondary" table
     in a relationship ``and_()`` expression would fail to be aliased to match
     how the "secondary" table normally renders within a
     :meth:`_sql.Select.join` expression, leading to an invalid query.

 .. change::
     :tags: bug, orm, performance, regression
     :tickets: 11085

     Adjusted the fix made in :ticket:`10570`, released in 2.0.23, where new
     logic was added to reconcile possibly changing bound parameter values
     across cache key generations used within the :func:`_orm.with_expression`
     construct.  The new logic changes the approach by which the new bound
     parameter values are associated with the statement, avoiding the need to
     deep-copy the statement which can result in a significant performance
     penalty for very deep / complex SQL constructs.  The new approach no longer
     requires this deep-copy step.

 .. change::
     :tags: bug, asyncio
     :tickets: 8771

     An error is raised if a :class:`.QueuePool` or other non-asyncio pool class
     is passed to :func:`_asyncio.create_async_engine`.  This engine only
     accepts asyncio-compatible pool classes including
     :class:`.AsyncAdaptedQueuePool`. Other pool classes such as
     :class:`.NullPool` are compatible with both synchronous and asynchronous
     engines as they do not perform any locking.

     .. seealso::

         :ref:`pool_api`


 .. change::
     :tags: change, tests

     pytest support in the tox.ini file has been updated to support pytest 8.1.

.. changelog::

:released: February 13, 2024

 .. change::
     :tags: bug, postgresql, regression
     :tickets: 11005

     Fixed regression caused by just-released fix for :ticket:`10863` where an
     invalid exception class were added to the "except" block, which does not
     get exercised unless such a catch actually happens.   A mock-style test has
     been added to ensure this catch is exercised in unit tests.


.. changelog::

:released: February 11, 2024

 .. change::
     :tags: usecase, postgresql, reflection
     :tickets: 10777

     Added support for reflection of PostgreSQL CHECK constraints marked with
     "NO INHERIT", setting the key ``no_inherit=True`` in the reflected data.
     Pull request courtesy Ellis Valentiner.

 .. change::
     :tags: bug, sql
     :tickets: 10843

     Fixed issues in :func:`_sql.case` where the logic for determining the
     type of the expression could result in :class:`.NullType` if the last
     element in the "whens" had no type, or in other cases where the type
     could resolve to ``None``.  The logic has been updated to scan all
     given expressions so that the first non-null type is used, as well as
     to always ensure a type is present.  Pull request courtesy David Evans.

 .. change::
     :tags: bug, mysql
     :tickets: 10850

     Fixed issue where NULL/NOT NULL would not be properly reflected from a
     MySQL column that also specified the VIRTUAL or STORED directives.  Pull
     request courtesy Georg Wicke-Arndt.

 .. change::
     :tags: bug, regression, postgresql
     :tickets: 10863

     Fixed regression in the asyncpg dialect caused by :ticket:`10717` in
     release 2.0.24 where the change that now attempts to gracefully close the
     asyncpg connection before terminating would not fall back to
     ``terminate()`` for other potential connection-related exceptions other
     than a timeout error, not taking into account cases where the graceful
     ``.close()`` attempt fails for other reasons such as connection errors.


 .. change::
     :tags: oracle, bug, performance
     :tickets: 10877

     Changed the default arraysize of the Oracle dialects so that the value set
     by the driver is used, that is 100 at the time of writing for both
     cx_oracle and oracledb. Previously the value was set to 50 by default. The
     setting of 50 could cause significant performance regressions compared to
     when using cx_oracle/oracledb alone to fetch many hundreds of rows over
     slower networks.

 .. change::
     :tags: bug, mysql
     :tickets: 10893

     Fixed issue in asyncio dialects asyncmy and aiomysql, where their
     ``.close()`` method is apparently not a graceful close.  replace with
     non-standard ``.ensure_closed()`` method that's awaitable and move
     ``.close()`` to the so-called "terminate" case.

 .. change::
     :tags: bug, orm
     :tickets: 10896

     Replaced the "loader depth is excessively deep" warning with a shorter
     message added to the caching badge within SQL logging, for those statements
     where the ORM disabled the cache due to a too-deep chain of loader options.
     The condition which this warning highlights is difficult to resolve and is
     generally just a limitation in the ORM's application of SQL caching. A
     future feature may include the ability to tune the threshold where caching
     is disabled, but for now the warning will no longer be a nuisance.

 .. change::
     :tags: bug, orm
     :tickets: 10899

     Fixed issue where it was not possible to use a type (such as an enum)
     within a :class:`_orm.Mapped` container type if that type were declared
     locally within the class body.  The scope of locals used for the eval now
     includes that of the class body itself.  In addition, the expression within
     :class:`_orm.Mapped` may also refer to the class name itself, if used as a
     string or with future annotations mode.

 .. change::
     :tags: usecase, postgresql
     :tickets: 10904

     Support the ``USING <method>`` option for PostgreSQL ``CREATE TABLE`` to
     specify the access method to use to store the contents for the new table.
     Pull request courtesy Edgar Ramírez-Mondragón.

     .. seealso::

         :ref:`postgresql_table_options`

 .. change::
     :tags: bug, examples
     :tickets: 10920

     Fixed regression in history_meta example where the use of
     :meth:`_schema.MetaData.to_metadata` to make a copy of the history table
     would also copy indexes (which is a good thing), but causing naming
     conflicts indexes regardless of naming scheme used for those indexes. A
     "_history" suffix is now added to these indexes in the same way as is
     achieved for the table name.


 .. change::
     :tags: bug, orm
     :tickets: 10967

     Fixed issue where using :meth:`_orm.Session.delete` along with the
     :paramref:`_orm.Mapper.version_id_col` feature would fail to use the
     correct version identifier in the case that an additional UPDATE were
     emitted against the target object as a result of the use of
     :paramref:`_orm.relationship.post_update` on the object.  The issue is
     similar to :ticket:`10800` just fixed in version 2.0.25 for the case of
     updates alone.

 .. change::
     :tags: bug, orm
     :tickets: 10990

     Fixed issue where an assertion within the implementation for
     :func:`_orm.with_expression` would raise if a SQL expression that was not
     cacheable were used; this was a 2.0 regression since 1.4.

 .. change::
     :tags: postgresql, usecase
     :tickets: 9736

     Correctly type PostgreSQL RANGE and MULTIRANGE types as ``Range[T]``
     and ``Sequence[Range[T]]``.
     Introduced utility sequence :class:`_postgresql.MultiRange` to allow better
     interoperability of MULTIRANGE types.

 .. change::
     :tags: postgresql, usecase

     Differentiate between INT4 and INT8 ranges and multi-ranges types when
     inferring the database type from a :class:`_postgresql.Range` or
     :class:`_postgresql.MultiRange` instance, preferring INT4 if the values
     fit into it.

 .. change::
     :tags: bug, typing

     Fixed the type signature for the :meth:`.PoolEvents.checkin` event to
     indicate that the given :class:`.DBAPIConnection` argument may be ``None``
     in the case where the connection has been invalidated.

 .. change::
     :tags: bug, examples

     Fixed the performance example scripts in examples/performance to mostly
     work with the Oracle database, by adding the :class:`.Identity` construct
     to all the tables and allowing primary generation to occur on this backend.
     A few of the "raw DBAPI" cases still are not compatible with Oracle.


 .. change::
     :tags: bug, mssql

     Fixed an issue regarding the use of the :class:`.Uuid` datatype with the
     :paramref:`.Uuid.as_uuid` parameter set to False, when using the pymssql
     dialect. ORM-optimized INSERT statements (e.g. the "insertmanyvalues"
     feature) would not correctly align primary key UUID values for bulk INSERT
     statements, resulting in errors.  Similar issues were fixed for the
     PostgreSQL drivers as well.


 .. change::
     :tags: bug, postgresql

     Fixed an issue regarding the use of the :class:`.Uuid` datatype with the
     :paramref:`.Uuid.as_uuid` parameter set to False, when using PostgreSQL
     dialects. ORM-optimized INSERT statements (e.g. the "insertmanyvalues"
     feature) would not correctly align primary key UUID values for bulk INSERT
     statements, resulting in errors.  Similar issues were fixed for the
     pymssql driver as well.

.. changelog::

:released: January 2, 2024

 .. change::
     :tags: oracle, asyncio
     :tickets: 10679

     Added support for :ref:`oracledb` in asyncio mode, using the newly released
     version of the ``oracledb`` DBAPI that includes asyncio support. For the
     2.0 series, this is a preview release, where the current implementation
     does not yet have include support for
     :meth:`_asyncio.AsyncConnection.stream`. Improved support is planned for
     the 2.1 release of SQLAlchemy.

 .. change::
     :tags: bug, orm
     :tickets: 10800

     Fixed issue where when making use of the
     :paramref:`_orm.relationship.post_update` feature at the same time as using
     a mapper version_id_col could lead to a situation where the second UPDATE
     statement emitted by the post-update feature would fail to make use of the
     correct version identifier, assuming an UPDATE was already emitted in that
     flush which had already bumped the version counter.

 .. change::
     :tags: bug, typing
     :tickets: 10801, 10818

     Fixed regressions caused by typing added to the ``sqlalchemy.sql.functions``
     module in version 2.0.24, as part of :ticket:`6810`:

     * Further enhancements to pep-484 typing to allow SQL functions from
       :attr:`_sql.func` derived elements to work more effectively with ORM-mapped
       attributes (:ticket:`10801`)

     * Fixed the argument types passed to functions so that literal expressions
       like strings and ints are again interpreted correctly (:ticket:`10818`)


 .. change::
     :tags: usecase, orm
     :tickets: 10807

     Added preliminary support for Python 3.12 pep-695 type alias structures,
     when resolving custom type maps for ORM Annotated Declarative mappings.


 .. change::
     :tags: bug, orm
     :tickets: 10815

     Fixed issue where ORM Annotated Declarative would mis-interpret the left
     hand side of a relationship without any collection specified as
     uselist=True if the left type were given as a class and not a string,
     without using future-style annotations.

 .. change::
     :tags: bug, sql
     :tickets: 10817

     Improved compilation of :func:`_sql.any_` / :func:`_sql.all_` in the
     context of a negation of boolean comparison, will now render ``NOT (expr)``
     rather than reversing the equality operator to not equals, allowing
     finer-grained control of negations for these non-typical operators.

.. changelog::

:released: December 28, 2023

 .. change::
     :tags: bug, orm
     :tickets: 10597

     Fixed issue where use of :func:`_orm.foreign` annotation on a
     non-initialized :func:`_orm.mapped_column` construct would produce an
     expression without a type, which was then not updated at initialization
     time of the actual column, leading to issues such as relationships not
     determining ``use_get`` appropriately.


 .. change::
     :tags: bug, schema
     :tickets: 10654

     Fixed issue where error reporting for unexpected schema item when creating
     objects like :class:`_schema.Table` would incorrectly handle an argument
     that was itself passed as a tuple, leading to a formatting error.  The
     error message has been modernized to use f-strings.

 .. change::
     :tags: bug, engine
     :tickets: 10662

     Fixed URL-encoding of the username and password components of
     :class:`.engine.URL` objects when converting them to string using the
     :meth:`_engine.URL.render_as_string` method, by using Python standard
     library ``urllib.parse.quote`` while allowing for plus signs and spaces to
     remain unchanged as supported by SQLAlchemy's non-standard URL parsing,
     rather than the legacy home-grown routine from many years ago. Pull request
     courtesy of Xavier NUNN.

 .. change::
     :tags: bug, orm
     :tickets: 10668

     Improved the error message produced when the unit of work process sets the
     value of a primary key column to NULL due to a related object with a
     dependency rule on that column being deleted, to include not just the
     destination object and column name but also the source column from which
     the NULL value is originating.  Pull request courtesy Jan Vollmer.

 .. change::
     :tags: bug, postgresql
     :tickets: 10717

     Adjusted the asyncpg dialect such that when the ``terminate()`` method is
     used to discard an invalidated connection, the dialect will first attempt
     to gracefully close the connection using ``.close()`` with a timeout, if
     the operation is proceeding within an async event loop context only. This
     allows the asyncpg driver to attend to finalizing a ``TimeoutError``
     including being able to close a long-running query server side, which
     otherwise can keep running after the program has exited.

 .. change::
     :tags: bug, orm
     :tickets: 10732

     Modified the ``__init_subclass__()`` method used by
     :class:`_orm.MappedAsDataclass`, :class:`_orm.DeclarativeBase` and
     :class:`_orm.DeclarativeBaseNoMeta` to accept arbitrary ``**kw`` and to
     propagate them to the ``super()`` call, allowing greater flexibility in
     arranging custom superclasses and mixins which make use of
     ``__init_subclass__()`` keyword arguments.  Pull request courtesy Michael
     Oliver.


 .. change::
     :tags: bug, tests
     :tickets: 10747

     Improvements to the test suite to further harden its ability to run
     when Python ``greenlet`` is not installed.   There is now a tox
     target that includes the token "nogreenlet" that will run the suite
     with greenlet not installed (note that it still temporarily installs
     greenlet as part of the tox config, however).

 .. change::
     :tags: bug, sql
     :tickets: 10753

     Fixed issue in stringify for SQL elements, where a specific dialect is not
     passed,  where a dialect-specific element such as the PostgreSQL "on
     conflict do update" construct is encountered and then fails to provide for
     a stringify dialect with the appropriate state to render the construct,
     leading to internal errors.

 .. change::
     :tags: bug, sql

     Fixed issue where stringifying or compiling a :class:`.CTE` that was
     against a DML construct such as an :func:`_sql.insert` construct would fail
     to stringify, due to a mis-detection that the statement overall is an
     INSERT, leading to internal errors.

 .. change::
     :tags: bug, orm
     :tickets: 10776

     Ensured the use case of :class:`.Bundle` objects used in the
     ``returning()`` portion of ORM-enabled INSERT, UPDATE and DELETE statements
     is tested and works fully.   This was never explicitly implemented or
     tested previously and did not work correctly in the 1.4 series; in the 2.0
     series, ORM UPDATE/DELETE with WHERE criteria was missing an implementation
     method preventing :class:`.Bundle` objects from working.

 .. change::
     :tags: bug, orm
     :tickets: 10784

     Fixed 2.0 regression in :class:`.MutableList` where a routine that detects
     sequences would not correctly filter out string or bytes instances, making
     it impossible to assign a string value to a specific index (while
     non-sequence values would work fine).

 .. change::
     :tags: change, asyncio

     The ``async_fallback`` dialect argument is now deprecated, and will be
     removed in SQLAlchemy 2.1.   This flag has not been used for SQLAlchemy's
     test suite for some time.   asyncio dialects can still run in a synchronous
     style by running code within a greenlet using :func:`_util.greenlet_spawn`.

 .. change::
    :tags: bug, typing
    :tickets: 6810

    Completed pep-484 typing for the ``sqlalchemy.sql.functions`` module.
    :func:`_sql.select` constructs made against ``func`` elements should now
    have filled-in return types.

.. changelog::

:released: November 2, 2023

 .. change::
     :tags: bug, oracle
     :tickets: 10509

     Fixed issue in :class:`.Interval` datatype where the Oracle implementation
     was not being used for DDL generation, leading to the ``day_precision`` and
     ``second_precision`` parameters to be ignored, despite being supported by
     this dialect.  Pull request courtesy Indivar.

 .. change::
     :tags: bug, orm
     :tickets: 10516

     Fixed issue where the ``__allow_unmapped__`` directive failed to allow for
     legacy :class:`.Column` / :func:`.deferred` mappings that nonetheless had
     annotations such as ``Any`` or a specific type without ``Mapped[]`` as
     their type, without errors related to locating the attribute name.

 .. change::
     :tags: bug, mariadb
     :tickets: 10056

     Adjusted the MySQL / MariaDB dialects to default a generated column to NULL
     when using MariaDB, if :paramref:`_schema.Column.nullable` was not
     specified with an explicit ``True`` or ``False`` value, as MariaDB does not
     support the "NOT NULL" phrase with a generated column.  Pull request
     courtesy Indivar.


 .. change::
     :tags: bug, mariadb, regression
     :tickets: 10505

     Established a workaround for what seems to be an intrinsic issue across
     MySQL/MariaDB drivers where a RETURNING result for DELETE DML which returns
     no rows using SQLAlchemy's "empty IN" criteria fails to provide a
     cursor.description, which then yields result that returns no rows,
     leading to regressions for the ORM that in the 2.0 series uses RETURNING
     for bulk DELETE statements for the "synchronize session" feature. To
     resolve, when the specific case of "no description when RETURNING was
     given" is detected, an "empty result" with a correct cursor description is
     generated and used in place of the non-working cursor.

 .. change::
     :tags: bug, orm
     :tickets: 10570

     Fixed caching bug where using the :func:`_orm.with_expression` construct in
     conjunction with loader options :func:`_orm.selectinload`,
     :func:`_orm.lazyload` would fail to substitute bound parameter values
     correctly on subsequent caching runs.

 .. change::
     :tags: usecase, mssql
     :tickets: 6521

     Added support for the ``aioodbc`` driver implemented for SQL Server,
     which builds on top of the pyodbc and general aio* dialect architecture.

     .. seealso::

         :ref:`mssql_aioodbc` - in the SQL Server dialect documentation.



 .. change::
     :tags: bug, sql
     :tickets: 10535

     Added compiler-level None/NULL handling for the "literal processors" of all
     datatypes that include literal processing, that is, where a value is
     rendered inline within a SQL statement rather than as a bound parameter,
     for all those types that do not feature explicit "null value" handling.
     Previously this behavior was undefined and inconsistent.

 .. change::
     :tags: usecase, orm
     :tickets: 10575

     Implemented the :paramref:`_orm.Session.bulk_insert_mappings.render_nulls`
     parameter for new style bulk ORM inserts, allowing ``render_nulls=True`` as
     an execution option.   This allows for bulk ORM inserts with a mixture of
     ``None`` values in the parameter dictionaries to use a single batch of rows
     for a given set of dicationary keys, rather than breaking up into batches
     that omit the NULL columns from each INSERT.

     .. seealso::

         :ref:`orm_queryguide_insert_null_params`

 .. change::
     :tags: bug, postgresql
     :tickets: 10479

     Fixed 2.0 regression caused by :ticket:`7744` where chains of expressions
     involving PostgreSQL JSON operators combined with other operators such as
     string concatenation would lose correct parenthesization, due to an
     implementation detail specific to the PostgreSQL dialect.

 .. change::
     :tags: bug, postgresql
     :tickets: 10532

     Fixed SQL handling for "insertmanyvalues" when using the
     :class:`.postgresql.BIT` datatype with the asyncpg backend.  The
     :class:`.postgresql.BIT` on asyncpg apparently requires the use of an
     asyncpg-specific ``BitString`` type which is currently exposed when using
     this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all
     work with plain bitstrings here.  A future fix in version 2.1 will
     normalize this datatype across all PG backends.   Pull request courtesy
     Sören Oldag.


 .. change::
     :tags: usecase, sql
     :tickets: 9737

     Implemented "literal value processing" for the :class:`.Interval` datatype
     for both the PostgreSQL and Oracle dialects, allowing literal rendering of
     interval values.  Pull request courtesy Indivar Mishra.

 .. change::
     :tags: bug, oracle
     :tickets: 10470

     Fixed issue where the cx_Oracle dialect claimed to support a lower
     cx_Oracle version (7.x) than was actually supported in practice within the
     2.0 series of SQLAlchemy. The dialect imports symbols that are only in
     cx_Oracle 8 or higher, so runtime dialect checks as well as setup.cfg
     requirements have been updated to reflect this compatibility.

 .. change::
     :tags: sql

     Removed unused placeholder method :meth:`.TypeEngine.compare_against_backend`
     This method was used by very old versions of Alembic.
     See https://github.com/sqlalchemy/alembic/issues/1293 for details.

 .. change::
     :tags: bug, orm
     :tickets: 10472

     Fixed bug in ORM annotated declarative where using a ``ClassVar`` that
     nonetheless referred in some way to an ORM mapped class name would fail to
     be interpreted as a ``ClassVar`` that's not mapped.

 .. change::
     :tags: bug, asyncio
     :tickets: 10421

     Fixed bug with method :meth:`_asyncio.AsyncSession.close_all`
     that was not working correctly.
     Also added function :func:`_asyncio.close_all_sessions` that's
     the equivalent of :func:`_orm.close_all_sessions`.
     Pull request courtesy of Bryan不可思议.

.. changelog::

:released: October 12, 2023

 .. change::
     :tags: bug, orm
     :tickets: 10369, 10046

     Fixed a wide range of :func:`_orm.mapped_column` parameters that were not
     being transferred when using the :func:`_orm.mapped_column` object inside
     of a pep-593 ``Annotated`` object, including
     :paramref:`_orm.mapped_column.sort_order`,
     :paramref:`_orm.mapped_column.deferred`,
     :paramref:`_orm.mapped_column.autoincrement`,
     :paramref:`_orm.mapped_column.system`, :paramref:`_orm.mapped_column.info`
     etc.

     Additionally, it remains not supported to have dataclass arguments, such as
     :paramref:`_orm.mapped_column.kw_only`,
     :paramref:`_orm.mapped_column.default_factory` etc. indicated within the
     :func:`_orm.mapped_column` received by ``Annotated``, as this is not
     supported with pep-681 Dataclass Transforms.  A warning is now emitted when
     these parameters are used within ``Annotated`` in this way (and they
     continue to be ignored).

 .. change::
     :tags: bug, orm
     :tickets: 10459

     Fixed issue where calling :meth:`_engine.Result.unique` with a new-style
     :func:`.select` query in the ORM, where one or more columns yields values
     that are of "unknown hashability", typically when using JSON functions like
     ``func.json_build_object()`` without providing a type, would fail
     internally when the returned values were not actually hashable. The
     behavior is repaired to test the objects as they are received for
     hashability in this case, raising an informative error message if not. Note
     that for values of "known unhashability", such as when the
     :class:`_types.JSON` or :class:`_types.ARRAY` types are used directly, an
     informative error message was already raised.

     The "hashabiltiy testing" fix here is applied to legacy :class:`.Query` as
     well, however in the legacy case, :meth:`_engine.Result.unique` is used for
     nearly all queries, so no new warning is emitted here; the legacy behavior
     of falling back to using ``id()`` in this case is maintained, with the
     improvement that an unknown type that turns out to be hashable will now be
     uniqufied, whereas previously it would not.

 .. change::
     :tags: bug, orm
     :tickets: 10453

     Fixed regression in recently revised "insertmanyvalues" feature (likely
     issue :ticket:`9618`) where the ORM would inadvertently attempt to
     interpret a non-RETURNING result as one with RETURNING, in the case where
     the ``implicit_returning=False`` parameter were applied to the mapped
     :class:`.Table`, indicating that "insertmanyvalues" cannot be used if the
     primary key values are not provided.

 .. change::
     :tags: bug, engine

     Fixed issue within some dialects where the dialect could incorrectly return
     an empty result set for an INSERT statement that does not actually return
     rows at all, due to artfacts from pre- or post-fetching the primary key of
     the row or rows still being present.  Affected dialects included asyncpg,
     all mssql dialects.

 .. change::
     :tags: bug, typing
     :tickets: 10451

     Fixed typing issue where the argument list passed to :class:`.Values` was
     too-restrictively tied to ``List`` rather than ``Sequence``.  Pull request
     courtesy Iuri de Silvio.

 .. change::
     :tags: bug, orm
     :tickets: 10365

     Fixed bug where ORM :func:`_orm.with_loader_criteria` would not apply
     itself to a :meth:`_sql.Select.join` where the ON clause were given as a
     plain SQL comparison, rather than as a relationship target or similar.

 .. change::
     :tags: bug, sql
     :tickets: 10408

     Fixed issue where referring to a FROM entry in the SET clause of an UPDATE
     statement would not include it in the FROM clause of the UPDATE statement,
     if that entry were nowhere else in the statement; this occurs currently for
     CTEs that were added using :meth:`.Update.add_cte` to provide the desired
     CTE at the top of the statement.

 .. change::
     :tags: bug, mariadb
     :tickets: 10396

     Modified the mariadb-connector driver to pre-load the ``cursor.rowcount``
     value for all queries, to suit tools such as Pandas that hardcode to
     calling :attr:`.Result.rowcount` in this way. SQLAlchemy normally pre-loads
     ``cursor.rowcount`` only for UPDATE/DELETE statements and otherwise passes
     through to the DBAPI where it can return -1 if no value is available.
     However, mariadb-connector does not support invoking ``cursor.rowcount``
     after the cursor itself is closed, raising an error instead.  Generic test
     support has been added to ensure all backends support the allowing
     :attr:`.Result.rowcount` to succceed (that is, returning an integer
     value with -1 for "not available") after the result is closed.



 .. change::
     :tags: bug, mariadb

     Additional fixes for the mariadb-connector dialect to support UUID data
     values in the result in INSERT..RETURNING statements.

 .. change::
     :tags: bug, mssql
     :tickets: 10458

     Fixed bug where the rule that prevents ORDER BY from emitting within
     subqueries on SQL Server was not being disabled in the case where the
     :meth:`.select.fetch` method were used to limit rows in conjunction with
     WITH TIES or PERCENT, preventing valid subqueries with TOP / ORDER BY from
     being used.



 .. change::
     :tags: bug, sql
     :tickets: 10443

     Fixed 2.0 regression where the :class:`.DDL` construct would no longer
     ``__repr__()`` due to the removed ``on`` attribute not being accommodated.
     Pull request courtesy Iuri de Silvio.

 .. change::
     :tags: orm, usecase
     :tickets: 10202

     Added method :meth:`_orm.Session.get_one` that behaves like
     :meth:`_orm.Session.get` but raises an exception instead of returning
     ``None`` if no instance was found with the provided primary key.
     Pull request courtesy of Carlos Sousa.


 .. change::
     :tags: asyncio, bug

     Fixed the :paramref:`_asyncio.AsyncSession.get.execution_options` parameter
     which was not being propagated to the underlying :class:`_orm.Session` and
     was instead being ignored.

 .. change::
     :tags: bug, orm
     :tickets: 10412

     Fixed issue where :class:`.Mapped` symbols like :class:`.WriteOnlyMapped`
     and :class:`.DynamicMapped` could not be correctly resolved when referenced
     as an element of a sub-module in the given annotation, assuming
     string-based or "future annotations" style annotations.

 .. change::
     :tags: bug, engine
     :tickets: 10414

     Fixed issue where under some garbage collection / exception scenarios the
     connection pool's cleanup routine would raise an error due to an unexpected
     set of state, which can be reproduced under specific conditions.

 .. change::
     :tags: bug, typing

     Updates to the codebase to support Mypy 1.6.0.

 .. change::
     :tags: usecase, orm
     :tickets: 7787

     Added an option to permanently close sessions.
     Set to ``False`` the new parameter :paramref:`_orm.Session.close_resets_only`
     will prevent a :class:`_orm.Session` from performing any other
     operation after :meth:`_orm.Session.close` has been called.

     Added new method :meth:`_orm.Session.reset` that will reset a :class:`_orm.Session`
     to its initial state. This is an alias of :meth:`_orm.Session.close`,
     unless :paramref:`_orm.Session.close_resets_only` is set to ``False``.

 .. change::
     :tags: orm, bug
     :tickets: 10385

     Fixed issue with ``__allow_unmapped__`` declarative option
     where types that were declared using collection types such as
     ``list[SomeClass]`` vs. the typing construct ``List[SomeClass]``
     would fail to be recognized correctly.  Pull request courtesy
     Pascal Corpet.

.. changelog::

:released: September 18, 2023

 .. change::
     :tags: bug, sql
     :tickets: 9610

     Adjusted the operator precedence for the string concatenation operator to
     be equal to that of string matching operators, such as
     :meth:`.ColumnElement.like`, :meth:`.ColumnElement.regexp_match`,
     :meth:`.ColumnElement.match`, etc., as well as plain ``==`` which has the
     same precedence as string comparison operators, so that parenthesis will be
     applied to a string concatenation expression that follows a string match
     operator. This provides for backends such as PostgreSQL where the "regexp
     match" operator is apparently of higher precedence than the string
     concatenation operator.

 .. change::
     :tags: bug, sql
     :tickets: 10342

     Qualified the use of ``hashlib.md5()`` within the DDL compiler, which is
     used to generate deterministic four-character suffixes for long index and
     constraint names in DDL statements, to include the Python 3.9+
     ``usedforsecurity=False`` parameter so that Python interpreters built for
     restricted environments such as FIPS do not consider this call to be
     related to security concerns.

 .. change::
     :tags: bug, postgresql
     :tickets: 10226

     Fixed regression which appeared in 2.0 due to :ticket:`8491` where the
     revised "ping" used for PostgreSQL dialects when the
     :paramref:`_sa.create_engine.pool_pre_ping` parameter is in use would
     interfere with the use of asyncpg with PGBouncer "transaction" mode, as the
     multiple PostgreSQL commands emitted by asnycpg could be broken out among
     multiple connections leading to errors, due to the lack of any transaction
     around this newly revised "ping".   The ping is now invoked within a
     transaction, in the same way that is implicit with all other backends that
     are based on the pep-249 DBAPI; this guarantees that the series of PG
     commands sent by asyncpg for this command are invoked on the same backend
     connection without it jumping to a different connection mid-command.  The
     transaction is not used if the asyncpg dialect is used in "AUTOCOMMIT"
     mode, which remains incompatible with pgbouncer transaction mode.


 .. change::
     :tags: bug, orm
     :tickets: 10279

     Adjusted the ORM's interpretation of the "target" entity used within
     :class:`.Update` and :class:`.Delete` to not interfere with the target
     "from" object passed to the statement, such as when passing an ORM-mapped
     :class:`_orm.aliased` construct that should be maintained within a phrase
     like "UPDATE FROM".  Cases like ORM session synchonize using "SELECT"
     statements such as with MySQL/ MariaDB will still have issues with
     UPDATE/DELETE of this form so it's best to disable synchonize_session when
     using DML statements of this type.

 .. change::
     :tags: bug, orm
     :tickets: 10348

     Added new capability to the :func:`_orm.selectin_polymorphic` loader option
     which allows other loader options to be bundled as siblings, referring to
     one of its subclasses, within the sub-options of parent loader option.
     Previously, this pattern was only supported if the
     :func:`_orm.selectin_polymorphic` were at the top level of the options for
     the query.   See new documentation section for example.

     As part of this change, improved the behavior of the
     :meth:`_orm.Load.selectin_polymorphic` method / loader strategy so that the
     subclass load does not load most already-loaded columns from the parent
     table, when the option is used against a class that is already being
     relationship-loaded.  Previously, the logic to load only the subclass
     columns worked only for a top level class load.

     .. seealso::

         :ref:`polymorphic_selectin_as_loader_option_target_plus_opts`

 .. change::
     :tags: bug, typing
     :tickets: 10264, 9284

     Fixed regression introduced in 2.0.20 via :ticket:`9600` fix which
     attempted to add more formal typing to
     :paramref:`_schema.MetaData.naming_convention`. This change prevented basic
     naming convention dictionaries from passing typing and has been adjusted so
     that a plain dictionary of strings for keys as well as dictionaries that
     use constraint types as keys or a mix of both, are again accepted.

     As part of this change, lesser used forms of the naming convention
     dictionary are also typed, including that it currently allows for
     ``Constraint`` type objects as keys as well.

 .. change::
     :tags: usecase, typing
     :tickets: 10288

     Made the contained type for :class:`.Mapped` covariant; this is to allow
     greater flexibility for end-user typing scenarios, such as the use of
     protocols to represent particular mapped class structures that are passed
     to other functions. As part of this change, the contained type was also
     made covariant for dependent and related types such as
     :class:`_orm.base.SQLORMOperations`, :class:`_orm.WriteOnlyMapped`, and
     :class:`_sql.SQLColumnExpression`. Pull request courtesy Roméo Després.


 .. change::
     :tags: bug, engine
     :tickets: 10275

     Fixed a series of reflection issues affecting the PostgreSQL,
     MySQL/MariaDB, and SQLite dialects when reflecting foreign key constraints
     where the target column contained parenthesis in one or both of the table
     name or column name.


 .. change::
     :tags: bug, sql
     :tickets: 10280

     The :class:`.Values` construct will now automatically create a proxy (i.e.
     a copy) of a :class:`_sql.column` if the column were already associated
     with an existing FROM clause.  This allows that an expression like
     ``values_obj.c.colname`` will produce the correct FROM clause even in the
     case that ``colname`` was passed as a :class:`_sql.column` that was already
     used with a previous :class:`.Values` or other table construct.
     Originally this was considered to be a candidate for an error condition,
     however it's likely this pattern is already in widespread use so it's
     now added to support.

 .. change::
     :tags: bug, setup
     :tickets: 10321

     Fixed very old issue where the full extent of SQLAlchemy modules, including
     ``sqlalchemy.testing.fixtures``, could not be imported outside of a pytest
     run. This suits inspection utilities such as ``pkgutil`` that attempt to
     import all installed modules in all packages.

 .. change::
     :tags: usecase, sql
     :tickets: 10269

     Adjusted the :class:`_types.Enum` datatype to accept an argument of
     ``None`` for the :paramref:`_types.Enum.length` parameter, resulting in a
     VARCHAR or other textual type with no length in the resulting DDL. This
     allows for new elements of any length to be added to the type after it
     exists in the schema.  Pull request courtesy Eugene Toder.


 .. change::
     :tags: bug, typing
     :tickets: 9878

     Fixed the type annotation for ``__class_getitem__()`` as applied to the
     ``Visitable`` class at the base of expression constructs to accept ``Any``
     for a key, rather than ``str``, which helps with some IDEs such as PyCharm
     when attempting to write typing annotations for SQL constructs which
     include generic selectors.  Pull request courtesy Jordan Macdonald.


 .. change::
     :tags: bug, typing
     :tickets: 10353

     Repaired the core "SQL element" class ``SQLCoreOperations`` to support the
     ``__hash__()`` method from a typing perspective, as objects like
     :class:`.Column` and ORM :class:`.InstrumentedAttribute` are hashable and
     are used as dictionary keys in the public API for the :class:`_dml.Update`
     and :class:`_dml.Insert` constructs.  Previously, type checkers were not
     aware the root SQL element was hashable.

 .. change::
     :tags: bug, typing
     :tickets: 10337

     Fixed typing issue with :meth:`_sql.Existing.select_from` that
     prevented its use with ORM classes.

 .. change::
     :tags: usecase, sql
     :tickets: 9873

     Added new generic SQL function :class:`_functions.aggregate_strings`, which
     accepts a SQL expression and a decimeter, concatenating strings on multiple
     rows into a single aggregate value. The function is compiled on a
     per-backend basis, into functions such as ``group_concat(),``
     ``string_agg()``, or ``LISTAGG()``.
     Pull request courtesy Joshua Morris.

 .. change::
     :tags: typing, bug
     :tickets: 10131

     Update type annotations for ORM loading options, restricting them to accept
     only `"*"` instead of any string for string arguments.  Pull request
     courtesy Janek Nouvertné.

.. changelog::

:released: August 15, 2023

 .. change::
     :tags: bug, orm
     :tickets: 10169

     Fixed issue where the ORM's generation of a SELECT from a joined
     inheritance model with same-named columns in superclass and subclass would
     somehow not send the correct list of column names to the :class:`.CTE`
     construct, when the RECURSIVE column list were generated.


 .. change::
     :tags: bug, typing
     :tickets: 9185

     Typing improvements:

     * :class:`.CursorResult` is returned for some forms of
       :meth:`_orm.Session.execute` where DML without RETURNING is used
     * fixed type for :paramref:`_orm.Query.with_for_update.of` parameter within
       :meth:`_orm.Query.with_for_update`
     * improvements to ``_DMLColumnArgument`` type used by some DML methods to
       pass column expressions
     * Add overload to :func:`_sql.literal` so that it is inferred that the
       return type is ``BindParameter[NullType]`` where
       :paramref:`_sql.literal.type_` param is None
     * Add overloads to :meth:`_sql.ColumnElement.op` so that the inferred
       type when :paramref:`_sql.ColumnElement.op.return_type` is not provided
       is ``Callable[[Any], BinaryExpression[Any]]``
     * Add missing overload to :meth:`_sql.ColumnElement.__add__`

     Pull request courtesy Mehdi Gmira.


 .. change::
     :tags: usecase, orm
     :tickets: 10192

     Implemented the "RETURNING '*'" use case for ORM enabled DML statements.
     This will render in as many cases as possible and return the unfiltered
     result set, however is not supported for multi-parameter "ORM bulk INSERT"
     statements that have specific column rendering requirements.


 .. change::
     :tags: bug, typing
     :tickets: 10182

     Fixed issue in :class:`_orm.Session` and :class:`_asyncio.AsyncSession`
     methods such as :meth:`_orm.Session.connection` where the
     :paramref:`_orm.Session.connection.execution_options` parameter were
     hardcoded to an internal type that is not user-facing.

 .. change::
     :tags: orm, bug
     :tickets: 10231

     Fixed fairly major issue where execution options passed to
     :meth:`_orm.Session.execute`, as well as execution options local to the ORM
     executed statement itself, would not be propagated along to eager loaders
     such as that of :func:`_orm.selectinload`, :func:`_orm.immediateload`, and
     :meth:`_orm.subqueryload`, making it impossible to do things such as
     disabling the cache for a single statement or using
     ``schema_translate_map`` for a single statement, as well as the use of
     user-custom execution options.   A change has been made where **all**
     user-facing execution options present for :meth:`_orm.Session.execute` will
     be propagated along to additional loaders.

     As part of this change, the warning for "excessively deep" eager loaders
     leading to caching being disabled can be silenced on a per-statement
     basis by sending ``execution_options={"compiled_cache": None}`` to
     :meth:`_orm.Session.execute`, which will disable caching for the full
     series of statements within that scope.

 .. change::
     :tags: usecase, asyncio
     :tickets: 9698

     Added new methods :meth:`_asyncio.AsyncConnection.aclose` as a synonym for
     :meth:`_asyncio.AsyncConnection.close` and
     :meth:`_asyncio.AsyncSession.aclose` as a synonym for
     :meth:`_asyncio.AsyncSession.close` to the
     :class:`_asyncio.AsyncConnection` and :class:`_asyncio.AsyncSession`
     objects, to provide compatibility with Python standard library
     ``contextlib.aclosing`` construct. Pull request courtesy Grigoriev Semyon.

 .. change::
     :tags: bug, orm
     :tickets: 10124

     Fixed issue where internal cloning used by the ORM for expressions like
     :meth:`_orm.relationship.Comparator.any` to produce correlated EXISTS
     constructs would interfere with the "cartesian product warning" feature of
     the SQL compiler, leading the SQL compiler to warn when all elements of the
     statement were correctly joined.

 .. change::
     :tags: orm, bug
     :tickets: 10139

     Fixed issue where the ``lazy="immediateload"`` loader strategy would place
     an internal loading token into the ORM mapped attribute under circumstances
     where the load should not occur, such as in a recursive self-referential
     load.   As part of this change, the ``lazy="immediateload"`` strategy now
     honors the :paramref:`_orm.relationship.join_depth` parameter for
     self-referential eager loads in the same way as that of other eager
     loaders, where leaving it unset or set at zero will lead to a
     self-referential immediateload not occurring, setting it to a value of one
     or greater will immediateload up until that given depth.


 .. change::
     :tags: bug, orm
     :tickets: 10175

     Fixed issue where dictionary-based collections such as
     :func:`_orm.attribute_keyed_dict` did not fully pickle/unpickle correctly,
     leading to issues when attempting to mutate such a collection after
     unpickling.


 .. change::
     :tags: bug, orm
     :tickets: 10125

     Fixed issue where chaining :func:`_orm.load_only` or other wildcard use of
     :func:`_orm.defer` from another eager loader using a :func:`_orm.aliased`
     against a joined inheritance subclass would fail to take effect for columns
     local to the superclass.


 .. change::
     :tags: bug, orm
     :tickets: 10167

     Fixed issue where an ORM-enabled :func:`_sql.select` construct would not
     render any CTEs added only via the :meth:`_sql.Select.add_cte` method that
     were not otherwise referenced in the statement.

 .. change::
     :tags: bug, examples

     The dogpile_caching examples have been updated for 2.0 style queries.
     Within the "caching query" logic itself there is one conditional added to
     differentiate between ``Query`` and ``select()`` when performing an
     invalidation operation.

 .. change::
     :tags: typing, usecase
     :tickets: 10173

     Added new typing only utility functions :func:`.Nullable` and
     :func:`.NotNullable` to type a column or ORM class as, respectively,
     nullable or not nullable.
     These function are no-op at runtime, returning the input unchanged.

 .. change::
     :tags: bug, engine
     :tickets: 10147

     Fixed critical issue where setting
     :paramref:`_sa.create_engine.isolation_level` to ``AUTOCOMMIT`` (as opposed
     to using the :meth:`_engine.Engine.execution_options` method) would fail to
     restore "autocommit" to a pooled connection if an alternate isolation level
     were temporarily selected using
     :paramref:`_engine.Connection.execution_options.isolation_level`.

.. changelog::

:released: July 15, 2023

 .. change::
     :tags: bug, orm
     :tickets: 10089

     Fixed issue where setting a relationship collection directly, where an
     object in the new collection were already present, would not trigger a
     cascade event for that object, leading to it not being added to the
     :class:`_orm.Session` if it were not already present.  This is similar in
     nature to :ticket:`6471` and is a more apparent issue due to the removal of
     ``cascade_backrefs`` in the 2.0 series.  The
     :meth:`_orm.AttributeEvents.append_wo_mutation` event added as part of
     :ticket:`6471` is now also emitted for existing members of a collection
     that are present in a bulk set of that same collection.

 .. change::
     :tags: bug, engine
     :tickets: 10093

     Renamed :attr:`_result.Row.t` and :meth:`_result.Row.tuple` to
     :attr:`_result.Row._t` and :meth:`_result.Row._tuple`; this is to suit the
     policy that all methods and pre-defined attributes on :class:`.Row` should
     be in the style of Python standard library ``namedtuple`` where all fixed
     names have a leading underscore, to avoid name conflicts with existing
     column names.   The previous method/attribute is now deprecated and will
     emit a deprecation warning.

 .. change::
     :tags: bug, postgresql
     :tickets: 10069

     Fixed regression caused by improvements to PostgreSQL URL parsing in
     :ticket:`10004` where "host" query string arguments that had colons in
     them, to support various third party proxy servers and/or dialects, would
     not parse correctly as these were evaluted as ``host:port`` combinations.
     Parsing has been updated to consider a colon as indicating a ``host:port``
     value only if the hostname contains only alphanumeric characters with dots
     or dashes only (e.g. no slashes), followed by exactly one colon followed by
     an all-integer token of zero or more integers.  In all other cases, the
     full string is taken as a host.

 .. change::
     :tags: bug, engine
     :tickets: 10079

     Added detection for non-string, non-:class:`_engine.URL` objects to the
     :func:`_engine.make_url` function, allowing ``ArgumentError`` to be thrown
     immediately, rather than causing failures later on.  Special logic ensures
     that mock forms of :class:`_engine.URL` are allowed through.  Pull request
     courtesy Grigoriev Semyon.

 .. change::
     :tags: bug, orm
     :tickets: 10090

     Fixed issue where objects that were associated with an unloaded collection
     via backref, but were not merged into the :class:`_orm.Session` due to the
     removal of ``cascade_backrefs`` in the 2.0 series, would not emit a warning
     that these objects were not being included in a flush, even though they
     wer