Sqlalchemy

:released: March 13, 2023

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

Fixed regression where the fix for :ticket:`8098`, which was released in
the 1.4 series and provided a layer of concurrency-safe checks for the
lambda SQL API, included additional fixes in the patch that failed to be
applied to the main branch. These additional fixes have been applied.

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

Fixed typing issue where :meth:`.ColumnElement.cast` did not allow a
:class:`.TypeEngine` argument independent of the type of the
:class:`.ColumnElement` itself, which is the purpose of
:meth:`.ColumnElement.cast`.

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

Fixed bug where the "active history" feature was not fully
implemented for composite attributes, making it impossible to receive
events that included the "old" value. This seems to have been the case
with older SQLAlchemy versions as well, where "active_history" would
be propagated to the underlying column-based attributes, but an event
handler listening to the composite attribute itself would not be given
the "old" value being replaced, even if the composite() were set up
with active_history=True.

Additionally, fixed a regression that's local to 2.0 which disallowed
active_history on composite from being assigned to the impl with
``attr.impl.active_history=True``.


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

Fixed reflection bug where Oracle "name normalize" would not work correctly
for reflection of symbols that are in the "PUBLIC" schema, such as
synonyms, meaning the PUBLIC name could not be indicated as lower case on
the Python side for the :paramref:`_schema.Table.schema` argument. Using
uppercase "PUBLIC" would work, but would then lead to awkward SQL queries
including a quoted ``"PUBLIC"`` name as well as indexing the table under
uppercase "PUBLIC", which was inconsistent.

.. change::
:tags: bug, typing

Fixed issues to allow typing tests to pass under Mypy 1.1.1.

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

Fixed regression where the :func:`_sql.select` construct would not be able
to render if it were given no columns and then used in the context of an
EXISTS, raising an internal exception instead. While an empty "SELECT" is
not typically valid SQL, in the context of EXISTS databases such as
PostgreSQL allow it, and in any case the condition now no longer raises
an internal exception.


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

Fixed regression involving pickling of Python rows between the cython and
pure Python implementations of :class:`.Row`, which occurred as part of
refactoring code for version 2.0 with typing. A particular constant were
turned into a string based ``Enum`` for the pure Python version of
:class:`.Row` whereas the cython version continued to use an integer
constant, leading to deserialization failures.

.. changelog::

:released: March 5, 2023

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

Added constructor arguments to the built-in mapping collection types
including :class:`.KeyFuncDict`, :func:`_orm.attribute_keyed_dict`,
:func:`_orm.column_keyed_dict` so that these dictionary types may be
constructed in place given the data up front; this provides further
compatibility with tools such as Python dataclasses ``.asdict()`` which
relies upon invoking these classes directly as ordinary dictionary classes.

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

Fixed multiple regressions due to :ticket:`8372`, involving
:func:`_orm.attribute_mapped_collection` (now called
:func:`_orm.attribute_keyed_dict`).

First, the collection was no longer usable with "key" attributes that were
not themselves ordinary mapped attributes; attributes linked to descriptors
and/or association proxy attributes have been fixed.

Second, if an event or other operation needed access to the "key" in order
to populate the dictionary from an mapped attribute that was not
loaded, this also would raise an error inappropriately, rather than
trying to load the attribute as was the behavior in 1.4. This is also
fixed.

For both cases, the behavior of :ticket:`8372` has been expanded.
:ticket:`8372` introduced an error that raises when the derived key that
would be used as a mapped dictionary key is effectively unassigned. In this
change, a warning only is emitted if the effective value of the ".key"
attribute is ``None``, where it cannot be unambiguously determined if this
``None`` was intentional or not. ``None`` will be not supported as mapped
collection dictionary keys going forward (as it typically refers to NULL
which means "unknown"). Setting
:paramref:`_orm.attribute_keyed_dict.ignore_unpopulated_attribute` will now
cause such ``None`` keys to be ignored as well.

.. change::
:tags: engine, performance
:tickets: 9343

A small optimization to the Cython implementation of :class:`.Result`
using a cdef for a particular int value to avoid Python overhead. Pull
request courtesy Matus Valo.


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

Fixed issue in the new :class:`.Uuid` datatype which prevented it from
working with the pymssql driver. As pymssql seems to be maintained again,
restored testing support for pymssql.

.. change::
:tags: bug, mssql

Tweaked the pymssql dialect to take better advantage of
RETURNING for INSERT statements in order to retrieve last inserted primary
key values, in the same way as occurs for the mssql+pyodbc dialect right
now.

.. change::
:tags: bug, orm

Identified that the ``sqlite`` and ``mssql+pyodbc`` dialects are now
compatible with the SQLAlchemy ORM's "versioned rows" feature, since
SQLAlchemy now computes rowcount for a RETURNING statement in this specific
case by counting the rows returned, rather than relying upon
``cursor.rowcount``. In particular, the ORM versioned rows use case
(documented at :ref:`mapper_version_counter`) should now be fully
supported with the SQL Server pyodbc dialect.


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

Fixed issue in PostgreSQL :class:`_postgresql.ExcludeConstraint` where
literal values were being compiled as bound parameters and not direct
inline values as is required for DDL.

.. change::
:tags: bug, typing

Fixed bug where the :meth:`_engine.Connection.scalars` method was not typed
as allowing a multiple-parameters list, which is now supported using
insertmanyvalues operations.

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

Improved typing for the mapping passed to :meth:`.Insert.values` and
:meth:`.Update.values` to be more open-ended about collection type, by
indicating read-only ``Mapping`` instead of writeable ``Dict`` which would
error out on too limited of a key type.

.. change::
:tags: schema

Validate that when provided the :paramref:`_schema.MetaData.schema`
argument of :class:`_schema.MetaData` is a string.

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

Exported the type returned by
:meth:`_orm.scoped_session.query_property` using a new public type
:class:`.orm.QueryPropertyDescriptor`.

.. change::
:tags: bug, mysql, postgresql
:tickets: 5648

The support for pool ping listeners to receive exception events via the
:meth:`.DialectEvents.handle_error` event added in 2.0.0b1 for
:ticket:`5648` failed to take into account dialect-specific ping routines
such as that of MySQL and PostgreSQL. The dialect feature has been reworked
so that all dialects participate within event handling. Additionally,
a new boolean element :attr:`.ExceptionContext.is_pre_ping` is added
which identifies if this operation is occurring within the pre-ping
operation.

For this release, third party dialects which implement a custom
:meth:`_engine.Dialect.do_ping` method can opt in to the newly improved
behavior by having their method no longer catch exceptions or check
exceptions for "is_disconnect", instead just propagating all exceptions
outwards. Checking the exception for "is_disconnect" is now done by an
enclosing method on the default dialect, which ensures that the event hook
is invoked for all exception scenarios before testing the exception as a
"disconnect" exception. If an existing ``do_ping()`` method continues to
catch exceptions and check "is_disconnect", it will continue to work as it
did previously, but ``handle_error`` hooks will not have access to the
exception if it isn't propagated outwards.

.. change::
:tags: bug, ext
:tickets: 9367

Fixed issue in automap where calling :meth:`_automap.AutomapBase.prepare`
from a specific mapped class, rather than from the
:class:`_automap.AutomapBase` directly, would not use the correct base
class when automap detected new tables, instead using the given class,
leading to mappers trying to configure inheritance. While one should
normally call :meth:`_automap.AutomapBase.prepare` from the base in any
case, it shouldn't misbehave that badly when called from a subclass.


.. change::
:tags: bug, sqlite, regression
:tickets: 9379

Fixed regression for SQLite connections where use of the ``deterministic``
parameter when establishing database functions would fail for older SQLite
versions, those prior to version 3.8.3. The version checking logic has been
improved to accommodate for this case.

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

Added missing init overload to the :class:`_types.Numeric` type object so
that pep-484 type checkers may properly resolve the complete type, deriving
from the :paramref:`_types.Numeric.asdecimal` parameter whether ``Decimal``
or ``float`` objects will be represented.

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

Fixed typing bug where :meth:`_sql.Select.from_statement` would not accept
:func:`_sql.text` or :class:`.TextualSelect` objects as a valid type.
Additionally repaired the :class:`.TextClause.columns` method to have a
return type, which was missing.

.. change::
:tags: bug, orm declarative
:tickets: 9332

Fixed issue where new :paramref:`_orm.mapped_column.use_existing_column`
feature would not work if the two same-named columns were mapped under
attribute names that were differently-named from an explicit name given to
the column itself. The attribute names can now be differently named when
using this parameter.

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

Added support for the :paramref:`_orm.Mapper.polymorphic_load` parameter to
be applied to each mapper in an inheritance hierarchy more than one level
deep, allowing columns to load for all classes in the hierarchy that
indicate ``"selectin"`` using a single statement, rather than ignoring
elements on those intermediary classes that nonetheless indicate they also
would participate in ``"selectin"`` loading and were not part of the
base-most SELECT statement.

.. change::
:tags: bug, orm
:tickets: 8853, 9335

Continued the fix for :ticket:`8853`, allowing the :class:`_orm.Mapped`
name to be fully qualified regardless of whether or not
``from __annotations__ import future`` were present. This issue first fixed
in 2.0.0b3 confirmed that this case worked via the test suite, however the
test suite apparently was not testing the behavior for the name
:class:`_orm.Mapped` not being locally present at all; string resolution
has been updated to ensure the :class:`_orm.Mapped` symbol is locatable as
applies to how the ORM uses these functions.

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

Fixed typing issue where :func:`_orm.with_polymorphic` would not
record the class type correctly.

.. change::
:tags: bug, ext, regression
:tickets: 9380

Fixed regression caused by typing added to ``sqlalchemy.ext.mutable`` for
:ticket:`8667`, where the semantics of the ``.pop()`` method changed such
that the method was non-working. Pull request courtesy Nils Philippsen.

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

Restore the :func:`.nullslast` and :func:`.nullsfirst` legacy functions
into the ``sqlalchemy`` import namespace. Previously, the newer
:func:`.nulls_last` and :func:`.nulls_first` functions were available, but
the legacy ones were inadvertently removed.

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

Fixed issue where the PostgreSQL :class:`_postgresql.ExcludeConstraint`
construct would not be copyable within operations such as
:meth:`_schema.Table.to_metadata` as well as within some Alembic scenarios,
if the constraint contained textual expression elements.

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

Fixed bug where :class:`_engine.Row` objects could not be reliably unpickled
across processes due to an accidental reliance on an unstable hash value.

.. changelog::

:released: February 17, 2023

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

Fixed regression introduced in version 2.0.2 due to :ticket:`9217` where
using DML RETURNING statements, as well as
:meth:`_sql.Select.from_statement` constructs as was "fixed" in
:ticket:`9217`, in conjunction with ORM mapped classes that used
expressions such as with :func:`_orm.column_property`, would lead to an
internal error within Core where it would attempt to match the expression
by name. The fix repairs the Core issue, and also adjusts the fix in
:ticket:`9217` to not take effect for the DML RETURNING use case, where it
adds unnecessary overhead.

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

Improved the typing support for the :ref:`hybrids_toplevel`
extension, updated all documentation to use ORM Annotated Declarative
mappings, and added a new modifier called :attr:`.hybrid_property.inplace`.
This modifier provides a way to alter the state of a :class:`.hybrid_property`
**in place**, which is essentially what very early versions of hybrids
did, before SQLAlchemy version 1.2.0 :ticket:`3912` changed this to
remove in-place mutation. This in-place mutation is now restored on an
**opt-in** basis to allow a single hybrid to have multiple methods
set up, without the need to name all the methods the same and without the
need to carefully "chain" differently-named methods in order to maintain
the composition. Typing tools such as Mypy and Pyright do not allow
same-named methods on a class, so with this change a succinct method
of setting up hybrids with typing support is restored.

.. seealso::

:ref:`hybrid_pep484_naming`

.. change::
:tags: bug, orm

Marked the internal ``EvaluatorCompiler`` module as private to the ORM, and
renamed it to ``_EvaluatorCompiler``. For users that may have been relying
upon this, the name ``EvaluatorCompiler`` is still present, however this
use is not supported and will be removed in a future release.

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

To accommodate a change in column ordering used by ORM Declarative in
SQLAlchemy 2.0, a new parameter :paramref:`_orm.mapped_column.sort_order`
has been added that can be used to control the order of the columns defined
in the table by the ORM, for common use cases such as mixins with primary
key columns that should appear first in tables. The change notes at
:ref:`change_9297` illustrate the default change in ordering behavior
(which is part of all SQLAlchemy 2.0 releases) as well as use of the
:paramref:`_orm.mapped_column.sort_order` to control column ordering when
using mixins and multiple classes (new in 2.0.4).

.. seealso::

:ref:`change_9297`

.. change::
:tags: sql
:tickets: 9277

Added public property :attr:`_schema.Table.autoincrement_column` that
returns the column identified as autoincrementing in the column.

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

Adjusted the behavior of the ``thick_mode`` parameter for the
:ref:`oracledb` dialect to correctly accept ``False`` as a value.
Previously, only ``None`` would indicate that thick mode should be
disabled.

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

The :meth:`_orm.Session.refresh` method will now immediately load a
relationship-bound attribute that is explicitly named within the
:paramref:`_orm.Session.refresh.attribute_names` collection even if it is
currently linked to the "select" loader, which normally is a "lazy" loader
that does not fire off during a refresh. The "lazy loader" strategy will
now detect that the operation is specifically a user-initiated
:meth:`_orm.Session.refresh` operation which named this attribute
explicitly, and will then call upon the "immediateload" strategy to
actually emit SQL to load the attribute. This should be helpful in
particular for some asyncio situations where the loading of an unloaded
lazy-loaded attribute must be forced, without using the actual lazy-loading
attribute pattern not supported in asyncio.


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

Fixed issue where element types of a tuple value would be hardcoded to take
on the types from a compared-to tuple, when the comparison were using the
:meth:`.ColumnOperators.in_` operator. This was inconsistent with the usual
way that types are determined for a binary expression, which is that the
actual element type on the right side is considered first before applying
the left-hand-side type.

.. change::
:tags: usecase, orm declarative
:tickets: 9266

Added new parameter ``dataclasses_callable`` to both the
:class:`_orm.MappedAsDataclass` class as well as the
:meth:`_orm.registry.mapped_as_dataclass` method which allows an
alternative callable to Python ``dataclasses.dataclass`` to be used in
order to produce dataclasses. The use case here is to drop in Pydantic's
dataclass function instead. Adjustments have been made to the mixin support
added for :ticket:`9179` in version 2.0.1 so that the ``__annotations__``
collection of the mixin is rewritten to not include the
:class:`_orm.Mapped` container, in the same way as occurs with mapped
classes, so that the Pydantic dataclasses constructor is not exposed to
unknown types.

.. seealso::

:ref:`dataclasses_pydantic`


.. changelog::

:released: February 9, 2023

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

Remove ``typing.Self`` workaround, now using :pep:`673` for most methods
that return ``Self``. As a consequence of this change ``mypy>=1.0.0`` is
now required to type check SQLAlchemy code.
Pull request courtesy Yurii Karabas.

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

Fixed critical regression in SQL expression formulation in the 2.0 series
due to :ticket:`7744` which improved support for SQL expressions that
contained many elements against the same operator repeatedly; parenthesis
grouping would be lost with expression elements beyond the first two
elements.


.. changelog::

:released: February 6, 2023

.. change::
:tags: bug, orm declarative
:tickets: 9249

Fixed regression caused by the fix for :ticket:`9171`, which itself was
fixing a regression, involving the mechanics of ``__init__()`` on classes
that extend from :class:`_orm.DeclarativeBase`. The change made it such
that ``__init__()`` was applied to the user-defined base if there were no
``__init__()`` method directly on the class. This has been adjusted so that
``__init__()`` is applied only if no other class in the hierarchy of the
user-defined base has an ``__init__()`` method. This again allows
user-defined base classes based on :class:`_orm.DeclarativeBase` to include
mixins that themselves include a custom ``__init__()`` method.

.. change::
:tags: bug, mysql, regression
:tickets: 9251

Fixed regression caused by issue :ticket:`9058` which adjusted the MySQL
dialect's ``has_table()`` to again use "DESCRIBE", where the specific error
code raised by MySQL version 8 when using a non-existent schema name was
unexpected and failed to be interpreted as a boolean result.



.. change::
:tags: bug, sqlite
:tickets: 9251

Fixed the SQLite dialect's ``has_table()`` function to correctly report
False for queries that include a non-None schema name for a schema that
doesn't exist; previously, a database error was raised.


.. change::
:tags: bug, orm declarative
:tickets: 9226

Fixed issue in ORM Declarative Dataclass mappings related to newly added
support for mixins added in 2.0.1 via :ticket:`9179`, where a combination
of using mixins plus ORM inheritance would mis-classify fields in some
cases leading to field-level dataclass arguments such as ``init=False`` being
lost.

.. change::
:tags: bug, orm, ression
:tickets: 9232

Fixed obscure ORM inheritance issue caused by :ticket:`8705` where some
scenarios of inheriting mappers that indicated groups of columns from the
local table and the inheriting table together under a
:func:`_orm.column_property` would nonetheless warn that properties of the
same name were being combined implicitly.

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

Fixed regression where using the :paramref:`_orm.Mapper.version_id_col`
feature with a regular Python-side incrementing column would fail to work
for SQLite and other databases that don't support "rowcount" with
"RETURNING", as "RETURNING" would be assumed for such columns even though
that's not what actually takes place.

.. change::
:tags: bug, orm declarative
:tickets: 9240

Repaired ORM Declarative mappings to allow for the
:paramref:`_orm.Mapper.primary_key` parameter to be specified within
``__mapper_args__`` when using :func:`_orm.mapped_column`. Despite this
usage being directly in the 2.0 documentation, the :class:`_orm.Mapper` was
not accepting the :func:`_orm.mapped_column` construct in this context. Ths
feature was already working for the :paramref:`_orm.Mapper.version_id_col`
and :paramref:`_orm.Mapper.polymorphic_on` parameters.

As part of this change, the ``__mapper_args__`` attribute may be specified
without using :func:`_orm.declared_attr` on a non-mapped mixin class,
including a ``"primary_key"`` entry that refers to :class:`_schema.Column`
or :func:`_orm.mapped_column` objects locally present on the mixin;
Declarative will also translate these columns into the correct ones for a
particular mapped class. This again was working already for the
:paramref:`_orm.Mapper.version_id_col` and
:paramref:`_orm.Mapper.polymorphic_on` parameters. Additionally,
elements within ``"primary_key"`` may be indicated as string names of
existing mapped properties.

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

Added a full suite of new SQL bitwise operators, for performing
database-side bitwise expressions on appropriate data values such as
integers, bit-strings, and similar. Pull request courtesy Yegor Statkevich.

.. seealso::

:ref:`operators_bitwise`


.. change::
:tags: bug, orm declarative
:tickets: 9211

An explicit error is raised if a mapping attempts to mix the use of
:class:`_orm.MappedAsDataclass` with
:meth:`_orm.registry.mapped_as_dataclass` within the same class hierarchy,
as this produces issues with the dataclass function being applied at the
wrong time to the mapped class, leading to errors during the mapping
process.

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

Fixed regression when using :meth:`_sql.Select.from_statement` in an ORM
context, where matching of columns to SQL labels based on name alone was
disabled for ORM-statements that weren't fully textual. This would prevent
arbitrary SQL expressions with column-name labels from matching up to the
entity to be loaded, which previously would work within the 1.4
and previous series, so the previous behavior has been restored.

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

Repaired a regression caused by the fix for :ticket:`8419` which caused
asyncpg connections to be reset (i.e. transaction ``rollback()`` called)
and returned to the pool normally in the case that the connection were not
explicitly returned to the connection pool and was instead being
intercepted by Python garbage collection, which would fail if the garbage
collection operation were being called outside of the asyncio event loop,
leading to a large amount of stack trace activity dumped into logging
and standard output.

The correct behavior is restored, which is that all asyncio connections
that are garbage collected due to not being explicitly returned to the
connection pool are detached from the pool and discarded, along with a
warning, rather than being returned the pool, as they cannot be reliably
reset. In the case of asyncpg connections, the asyncpg-specific
``terminate()`` method will be used to end the connection more gracefully
within this process as opposed to just dropping it.

This change includes a small behavioral change that is hoped to be useful
for debugging asyncio applications, where the warning that's emitted in the
case of asyncio connections being unexpectedly garbage collected has been
made slightly more aggressive by moving it outside of a ``try/except``
block and into a ``finally:`` block, where it will emit unconditionally
regardless of whether the detach/termination operation succeeded or not. It
will also have the effect that applications or test suites which promote
Python warnings to exceptions will see this as a full exception raise,
whereas previously it was not possible for this warning to actually
propagate as an exception. Applications and test suites which need to
tolerate this warning in the interim should adjust the Python warnings
filter to allow these warnings to not raise.

The behavior for traditional sync connections remains unchanged, that
garbage collected connections continue to be returned to the pool normally
without emitting a warning. This will likely be changed in a future major
release to at least emit a similar warning as is emitted for asyncio
drivers, as it is a usage error for pooled connections to be intercepted by
garbage collection without being properly returned to the pool.

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

Added new event hook :meth:`_orm.MapperEvents.after_mapper_constructed`,
which supplies an event hook to take place right as the
:class:`_orm.Mapper` object has been fully constructed, but before the
:meth:`_orm.registry.configure` call has been called. This allows code that
can create additional mappings and table structures based on the initial
configuration of a :class:`_orm.Mapper`, which also integrates within
Declarative configuration. Previously, when using Declarative, where the
:class:`_orm.Mapper` object is created within the class creation process,
there was no documented means of running code at this point. The change
is to immediately benefit custom mapping schemes such as that
of the :ref:`examples_versioned_history` example, which generate additional
mappers and tables in response to the creation of mapped classes.


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

The infrequently used :attr:`_orm.Mapper.iterate_properties` attribute and
:meth:`_orm.Mapper.get_property` method, which are primarily used
internally, no longer implicitly invoke the :meth:`_orm.registry.configure`
process. Public access to these methods is extremely rare and the only
benefit to having :meth:`_orm.registry.configure` would have been allowing
"backref" properties be present in these collections. In order to support
the new :meth:`_orm.MapperEvents.after_mapper_constructed` event, iteration
and access to the internal :class:`_orm.MapperProperty` objects is now
possible without triggering an implicit configure of the mapper itself.

The more-public facing route to iteration of all mapper attributes, the
:attr:`_orm.Mapper.attrs` collection and similar, will still implicitly
invoke the :meth:`_orm.registry.configure` step thus making backref
attributes available.

In all cases, the :meth:`_orm.registry.configure` is always available to
be called directly.

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

Reworked the :ref:`examples_versioned_history` to work with
version 2.0, while at the same time improving the overall working of
this example to use newer APIs, including a newly added hook
:meth:`_orm.MapperEvents.after_mapper_constructed`.



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

Added support for MySQL 8's new ``AS <name> ON DUPLICATE KEY`` syntax when
using :meth:`_mysql.Insert.on_duplicate_key_update`, which is required for
newer versions of MySQL 8 as the previous syntax using ``VALUES()`` now
emits a deprecation warning with those versions. Server version detection
is employed to determine if traditional MariaDB / MySQL < 8 ``VALUES()``
syntax should be used, vs. the newer MySQL 8 required syntax. Pull request
courtesy Caspar Wylie.

.. changelog::

:released: February 1, 2023

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

Opened up typing on :paramref:`.Select.with_for_update.of` to also accept
table and mapped class arguments, as seems to be available for the MySQL
dialect.

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

Fixed regression where ORM models that used joined table inheritance with a
composite foreign key would encounter an internal error in the mapper
internals.



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

Corrected the fix for :ticket:`7664`, released in version 2.0.0, to also
include :class:`.DropSchema` which was inadvertently missed in this fix,
allowing stringification without a dialect. The fixes for both constructs
is backported to the 1.4 series as of 1.4.47.


.. change::
:tags: bug, orm declarative
:tickets: 9175

Added support for :pep:`484` ``NewType`` to be used in the
:paramref:`_orm.registry.type_annotation_map` as well as within
:class:`.Mapped` constructs. These types will behave in the same way as
custom subclasses of types right now; they must appear explicitly within
the :paramref:`_orm.registry.type_annotation_map` to be mapped.

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

Fixed typing for limit/offset methods including :meth:`.Select.limit`,
:meth:`.Select.offset`, :meth:`_orm.Query.limit`, :meth:`_orm.Query.offset`
to allow ``None``, which is the documented API to "cancel" the current
limit/offset.



.. change::
:tags: bug, orm declarative
:tickets: 9179

When using the :class:`.MappedAsDataclass` superclass, all classes within
the hierarchy that are subclasses of this class will now be run through the
``dataclasses.dataclass`` function whether or not they are actually
mapped, so that non-ORM fields declared on non-mapped classes within the
hierarchy will be used when mapped subclasses are turned into dataclasses.
This behavior applies both to intermediary classes mapped with
``__abstract__ = True`` as well as to the user-defined declarative base
itself, assuming :class:`.MappedAsDataclass` is present as a superclass for
these classes.

This allows non-mapped attributes such as ``InitVar`` declarations on
superclasses to be used, without the need to run the
``dataclasses.dataclass`` decorator explicitly on each non-mapped class.
The new behavior is considered as correct as this is what the :pep:`681`
implementation expects when using a superclass to indicate dataclass
behavior.

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

Fixed typing issue where :func:`_orm.mapped_column` objects typed as
:class:`_orm.Mapped` wouldn't be accepted in schema constraints such as
:class:`_schema.ForeignKey`, :class:`_schema.UniqueConstraint` or
:class:`_schema.Index`.

.. change::
:tags: bug, orm declarative
:tickets: 9187

Added support for :pep:`586` ``Literal[]`` to be used in the
:paramref:`_orm.registry.type_annotation_map` as well as within
:class:`.Mapped` constructs. To use custom types such as these, they must
appear explicitly within the :paramref:`_orm.registry.type_annotation_map`
to be mapped. Pull request courtesy Frederik Aalund.

As part of this change, the support for :class:`.sqltypes.Enum` in the
:paramref:`_orm.registry.type_annotation_map` has been expanded to include
support for ``Literal[]`` types consisting of string values to be used,
in addition to ``enum.Enum`` datatypes. If a ``Literal[]`` datatype
is used within ``Mapped[]`` that is not linked in
:paramref:`_orm.registry.type_annotation_map` to a specific datatype,
a :class:`.sqltypes.Enum` will be used by default.

.. seealso::

:ref:`orm_declarative_mapped_column_enums`


.. change::
:tags: bug, orm declarative
:tickets: 9200

Fixed issue involving the use of :class:`.sqltypes.Enum` within the
:paramref:`_orm.registry.type_annotation_map` where the
:paramref:`_sqltypes.Enum.native_enum` parameter would not be correctly
copied to the mapped column datatype, if it were overridden
as stated in the documentation to set this parameter to False.



.. change::
:tags: bug, orm declarative, regression
:tickets: 9171

Fixed regression in :class:`.DeclarativeBase` class where the registry's
default constructor would not be applied to the base itself, which is
different from how the previous :func:`_orm.declarative_base` construct
works. This would prevent a mapped class with its own ``__init__()`` method
from calling ``super().__init__()`` in order to access the registry's
default constructor and automatically populate attributes, instead hitting
``object.__init__()`` which would raise a ``TypeError`` on any arguments.




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

Fixed regression related to the implementation for the new
"insertmanyvalues" feature where an internal ``TypeError`` would occur in
arrangements where a :func:`_sql.insert` would be referenced inside
of another :func:`_sql.insert` via a CTE; made additional repairs for this
use case for positional dialects such as asyncpg when using
"insertmanyvalues".



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

Fixed typing for :meth:`_expression.ColumnElement.cast` to accept
both ``Type[TypeEngine[T]]`` and ``TypeEngine[T]``; previously
only ``TypeEngine[T]`` was accepted. Pull request courtesy Yurii Karabas.

.. change::
:tags: bug, orm declarative
:tickets: 9177

Improved the ruleset used to interpret :pep:`593` ``Annotated`` types when
used with Annotated Declarative mapping, the inner type will be checked for
"Optional" in all cases which will be added to the criteria by which the
column is set as "nullable" or not; if the type within the ``Annotated``
container is optional (or unioned with ``None``), the column will be
considered nullable if there are no explicit
:paramref:`_orm.mapped_column.nullable` parameters overriding it.

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

Improved the error reporting when linking strategy options from a base
class to another attribute that's off a subclass, where ``of_type()``
should be used. Previously, when :meth:`.Load.options` is used, the message
would lack informative detail that ``of_type()`` should be used, which was
not the case when linking the options directly. The informative detail now
emits even if :meth:`.Load.options` is used.



.. changelog::