Sqlalchemy

:released: May 6, 2021

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

Fixed regression involving ``lazy='dynamic'`` loader in conjunction with a
detached object. The previous behavior was that the dynamic loader upon
calling methods like ``.all()`` returns empty lists for detached objects
without error, this has been restored; however a warning is now emitted as
this is not the correct result. Other dynamic loader scenarios correctly
raise ``DetachedInstanceError``.

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

Fixed regression caused by the "empty in" change just made in
:ticket:`6397` 1.4.12 where the expression needs to be parenthesized for
the "not in" use case, otherwise the condition will interfere with the
other filtering criteria.


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

The :class:`.TypeDecorator` class will now emit a warning when used in SQL
compilation with caching unless the ``.cache_ok`` flag is set to ``True``
or ``False``. A new class-level attribute :attr:`.TypeDecorator.cache_ok`
may be set which will be used as an indication that all the parameters
passed to the object are safe to be used as a cache key if set to ``True``,
``False`` means they are not.

.. change::
:tags: engine, bug, regression
:tickets: 6427

Established a deprecation path for calling upon the
:meth:`_cursor.CursorResult.keys` method for a statement that returns no
rows to provide support for legacy patterns used by the "records" package
as well as any other non-migrated applications. Previously, this would
raise :class:`.ResourceClosedException` unconditionally in the same way as
it does when attempting to fetch rows. While this is the correct behavior
going forward, the ``LegacyCursorResult`` object will now in
this case return an empty list for ``.keys()`` as it did in 1.3, while also
emitting a 2.0 deprecation warning. The :class:`_cursor.CursorResult`, used
when using a 2.0-style "future" engine, will continue to raise as it does
now.

.. change::
:tags: usecase, engine, orm
:tickets: 6288

Applied consistent behavior to the use case of
calling ``.commit()`` or ``.rollback()`` inside of an existing
``.begin()`` context manager, with the addition of potentially
emitting SQL within the block subsequent to the commit or rollback.
This change continues upon the change first added in
:ticket:`6155` where the use case of calling "rollback" inside of
a ``.begin()`` contextmanager block was proposed:

* calling ``.commit()`` or ``.rollback()`` will now be allowed
without error or warning within all scopes, including
that of legacy and future :class:`_engine.Engine`, ORM
:class:`_orm.Session`, asyncio :class:`.AsyncEngine`. Previously,
the :class:`_orm.Session` disallowed this.

* The remaining scope of the context manager is then closed;
when the block ends, a check is emitted to see if the transaction
was already ended, and if so the block returns without action.

* It will now raise **an error** if subsequent SQL of any kind
is emitted within the block, **after** ``.commit()`` or
``.rollback()`` is called. The block should be closed as
the state of the executable object would otherwise be undefined
in this state.

.. changelog::

:released: May 3, 2021

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

Fixed regression in ``selectinload`` loader strategy that would cause it to
cache its internal state incorrectly when handling relationships that join
across more than one column, such as when using a composite foreign key.
The invalid caching would then cause other unrelated loader operations to
fail.


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

Fixed regression where :meth:`_orm.Query.filter_by` would not work if the
lead entity were a SQL function or other expression derived from the
primary entity in question, rather than a simple entity or column of that
entity. Additionally, improved the behavior of
:meth:`_sql.Select.filter_by` overall to work with column expressions even
in a non-ORM context.

.. change::
:tags: bug, engine, regression
:tickets: 6408

Restored a legacy transactional behavior that was inadvertently removed
from the :class:`_engine.Connection` as it was never tested as a known use
case in previous versions, where calling upon the
:meth:`_engine.Connection.begin_nested` method, when no transaction is
present, does not create a SAVEPOINT at all and instead starts an outer
transaction, returning a :class:`.RootTransaction` object instead of a
:class:`.NestedTransaction` object. This :class:`.RootTransaction` then
will emit a real COMMIT on the database connection when committed.
Previously, the 2.0 style behavior was present in all cases that would
autobegin a transaction but not commit it, which is a behavioral change.

When using a :term:`2.0 style` connection object, the behavior is unchanged
from previous 1.4 versions; calling :meth:`_engine.Connection.begin_nested`
will "autobegin" the outer transaction if not already present, and then as
instructed emit a SAVEPOINT, returning the :class:`.NestedTransaction`
object. The outer transaction is committed by calling upon
:meth:`_engine.Connection.commit`, as is "commit-as-you-go" style usage.

In non-"future" mode, while the old behavior is restored, it also
emits a 2.0 deprecation warning as this is a legacy behavior.


.. change::
:tags: bug, asyncio, regression
:tickets: 6409

Fixed a regression introduced by :ticket:`6337` that would create an
``asyncio.Lock`` which could be attached to the wrong loop when
instantiating the async engine before any asyncio loop was started, leading
to an asyncio error message when attempting to use the engine under certain
circumstances.

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

Fixed regression where using :func:`_orm.selectinload` and
:func:`_orm.subqueryload` to load a two-level-deep path would lead to an
attribute error.

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

Fixed regression where using the :func:`_orm.noload` loader strategy in
conjunction with a "dynamic" relationship would lead to an attribute error
as the noload strategy would attempt to apply itself to the dynamic loader.

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

Add support for server side cursors in the pg8000 dialect for PostgreSQL.
This allows use of the
:paramref:`.Connection.execution_options.stream_results` option.

.. changelog::

:released: April 29, 2021

.. change::
:tags: bug, orm, regression, caching
:tickets: 6391

Fixed critical regression where bound parameter tracking as used in the SQL
caching system could fail to track all parameters for the case where the
same SQL expression containing a parameter were used in an ORM-related
query using a feature such as class inheritance, which was then embedded in
an enclosing expression which would make use of that same expression
multiple times, such as a UNION. The ORM would individually copy the
individual SELECT statements as part of compilation with class inheritance,
which then embedded in the enclosing statement would fail to accommodate
for all parameters. The logic that tracks this condition has been adjusted
to work for multiple copies of a parameter.

.. change::
:tags: bug, sql
:tickets: 6258, 6397

Revised the "EMPTY IN" expression to no longer rely upon using a subquery,
as this was causing some compatibility and performance problems. The new
approach for selected databases takes advantage of using a NULL-returning
IN expression combined with the usual "1 != 1" or "1 = 1" expression
appended by AND or OR. The expression is now the default for all backends
other than SQLite, which still had some compatibility issues regarding
tuple "IN" for older SQLite versions.

Third party dialects can still override how the "empty set" expression
renders by implementing a new compiler method
``def visit_empty_set_op_expr(self, type_, expand_op)``, which takes
precedence over the existing
``def visit_empty_set_expr(self, element_types)`` which remains in place.


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

Fixed two distinct issues mostly affecting
:class:`_hybrid.hybrid_property`, which would come into play under common
mis-configuration scenarios that were silently ignored in 1.3, and now
failed in 1.4, where the "expression" implementation would return a non
:class:`_sql.ClauseElement` such as a boolean value. For both issues, 1.3's
behavior was to silently ignore the mis-configuration and ultimately
attempt to interpret the value as a SQL expression, which would lead to an
incorrect query.

* Fixed issue regarding interaction of the attribute system with
hybrid_property, where if the ``__clause_element__()`` method of the
attribute returned a non-:class:`_sql.ClauseElement` object, an internal
``AttributeError`` would lead the attribute to return the ``expression``
function on the hybrid_property itself, as the attribute error was
against the name ``.expression`` which would invoke the ``__getattr__()``
method as a fallback. This now raises explicitly. In 1.3 the
non-:class:`_sql.ClauseElement` was returned directly.

* Fixed issue in SQL argument coercions system where passing the wrong
kind of object to methods that expect column expressions would fail if
the object were altogether not a SQLAlchemy object, such as a Python
function, in cases where the object were not just coerced into a bound
value. Again 1.3 did not have a comprehensive argument coercion system
so this case would also pass silently.


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

Fixed issue where using a :class:`_sql.Select` as a subquery in an ORM
context would modify the :class:`_sql.Select` in place to disable
eagerloads on that object, which would then cause that same
:class:`_sql.Select` to not eagerload if it were then re-used in a
top-level execution context.


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

Fixed regression where usage of the :func:`_sql.text` construct inside the
columns clause of a :class:`_sql.Select` construct, which is better handled
by using a :func:`_sql.literal_column` construct, would nonetheless prevent
constructs like :func:`_sql.union` from working correctly. Other use cases,
such as constructing subqueries, continue to work the same as in prior
versions where the :func:`_sql.text` construct is silently omitted from the
collection of exported columns. Also repairs similar use within the
ORM.


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

Fixed regression involving legacy methods such as
:meth:`_sql.Select.append_column` where internal assertions would fail.

.. change::
:tags: usecase, sqlite
:tickets: 6379

Default to using ``SingletonThreadPool`` for in-memory SQLite databases
created using URI filenames. Previously the default pool used was the
``NullPool`` that precented sharing the same database between multiple
engines.

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

Fixed regression caused by :ticket:`5395` where tuning back the check for
sequences in :func:`_sql.select` now caused failures when doing 2.0-style
querying with a mapped class that also happens to have an ``__iter__()``
method. Tuned the check some more to accommodate this as well as some other
interesting ``__iter__()`` scenarios.


.. change::
:tags: bug, mssql, schema
:tickets: 6345

Add :meth:`_types.TypeEngine.as_generic` support for
:class:`sqlalchemy.dialects.mysql.BIT` columns, mapping
them to :class:`_sql.sqltypes.Boolean`.

.. change::
:tags: bug, orm, regression
:tickets: 6360, 6359

Fixed issue where the new :ref:`autobegin <session_autobegin>` behavior
failed to "autobegin" in the case where an existing persistent object has
an attribute change, which would then impact the behavior of
:meth:`_orm.Session.rollback` in that no snapshot was created to be rolled
back. The "attribute modify" mechanics have been updated to ensure
"autobegin", which does not perform any database work, does occur when
persistent attributes change in the same manner as when
:meth:`_orm.Session.add` is called. This is a regression as in 1.3, the
rollback() method always had a transaction to roll back and would expire
every time.

.. change::
:tags: bug, mssql, regression
:tickets: 6366

Fixed regression caused by :ticket:`6306` which added support for
``DateTime(timezone=True)``, where the previous behavior of the pyodbc
driver of implicitly dropping the tzinfo from a timezone-aware date when
INSERTing into a timezone-naive DATETIME column were lost, leading to a SQL
Server error when inserting timezone-aware datetime objects into
timezone-native database columns.

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

Fixed regression in ORM where using hybrid property to indicate an
expression from a different entity would confuse the column-labeling logic
in the ORM and attempt to derive the name of the hybrid from that other
class, leading to an attribute error. The owning class of the hybrid
attribute is now tracked along with the name.

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

Fixed regression in hybrid_property where a hybrid against a SQL function
would generate an ``AttributeError`` when attempting to generate an entry
for the ``.c`` collection of a subquery in some cases; among other things
this would impact its use in cases like that of ``Query.count()``.


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

Fixed very old issue where the :class:`_types.Enum` datatype would not
inherit the :paramref:`_schema.MetaData.schema` parameter of a
:class:`_schema.MetaData` object when that object were passed to the
:class:`_types.Enum` using :paramref:`_types.Enum.metadata`.

.. change::
:tags: bug, orm, dataclasses
:tickets: 6346

Adjusted the declarative scan for dataclasses so that the inheritance
behavior of :func:`_orm.declared_attr` established on a mixin, when using
the new form of having it inside of a ``dataclasses.field()`` construct and
not actually a descriptor attribute on the class, correctly accommodates
the case when the target class to be mapped is a subclass of an existing
mapped class which has already mapped that :func:`_orm.declared_attr`, and
therefore should not be re-applied to this class.


.. change::
:tags: bug, schema, mysql, mariadb, oracle, postgresql
:tickets: 6338

Ensure that the MySQL and MariaDB dialect ignore the
:class:`_sql.Identity` construct while rendering the ``AUTO_INCREMENT``
keyword in a create table.

The Oracle and PostgreSQL compiler was updated to not render
:class:`_sql.Identity` if the database version does not support it
(Oracle < 12 and PostgreSQL < 10). Previously it was rendered regardless
of the database version.

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

Fixed an issue with the (deprecated in 1.4)
:meth:`_schema.ForeignKeyConstraint.copy` method that caused an error when
invoked with the ``schema`` argument.

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

Fixed issue where usage of an explicit :class:`.Sequence` would produce
inconsistent "inline" behavior for an :class:`_sql.Insert` construct that
includes multiple values phrases; the first seq would be inline but
subsequent ones would be "pre-execute", leading to inconsistent sequence
ordering. The sequence expressions are now fully inline.

.. changelog::

:released: April 21, 2021

.. change::
:tags: bug, engine, regression
:tickets: 6337

Fixed critical regression caused by the change in :ticket:`5497` where the
connection pool "init" phase no longer occurred within mutexed isolation,
allowing other threads to proceed with the dialect uninitialized, which
could then impact the compilation of SQL statements.


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

Fixed regression where recent changes to support Python dataclasses had the
inadvertent effect that an ORM mapped class could not successfully override
the ``__new__()`` method.

.. changelog::

:released: April 20, 2021

.. change::
:tags: bug, declarative, regression
:tickets: 6291

Fixed :func:`_declarative.instrument_declarative` that called
a non existing registry method.

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

Fixed bug in new :func:`_orm.with_loader_criteria` feature where using a
mixin class with :func:`_orm.declared_attr` on an attribute that were
accessed inside the custom lambda would emit a warning regarding using an
unmapped declared attr, when the lambda callable were first initialized.
This warning is now prevented using special instrumentation for this
lambda initialization step.


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

The :paramref:`_types.DateTime.timezone` parameter when set to ``True``
will now make use of the ``DATETIMEOFFSET`` column type with SQL Server
when used to emit DDL, rather than ``DATETIME`` where the flag was silently
ignored.

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

Fixed additional regression caused by the "eagerloaders on refresh" feature
added in :ticket:`1763` where the refresh operation historically would set
``populate_existing``, which given the new feature now overwrites pending
changes on eagerly loaded objects when autoflush is false. The
populate_existing flag has been turned off for this case and a more
specific method used to ensure the correct attributes refreshed.

.. change::
:tags: bug, orm, result
:tickets: 6299

Fixed an issue when using 2.0 style execution that prevented using
:meth:`_result.Result.scalar_one` or
:meth:`_result.Result.scalar_one_or_none` after calling
:meth:`_result.Result.unique`, for the case where the ORM is returning a
single-element row in any case.

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

Fixed issue in SQL compiler where the bound parameters set up for a
:class:`.Values` construct wouldn't be positionally tracked correctly if
inside of a :class:`_sql.CTE`, affecting database drivers that support
VALUES + ctes and use positional parameters such as SQL Server in
particular as well as asyncpg. The fix also repairs support for
compiler flags such as ``literal_binds``.

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

Fixed issue where :func:`_functions.next_value` was not deriving its type
from the corresponding :class:`_schema.Sequence`, instead hardcoded to
:class:`_types.Integer`. The specific numeric type is now used.

.. change::
:tags: bug, mypy
:tickets: 6255

Fixed issue where mypy plugin would not correctly interpret an explicit
:class:`_orm.Mapped` annotation in conjunction with a
:func:`_orm.relationship` that refers to a class by string name; the
correct annotation would be downgraded to a less specific one leading to
typing errors.

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

Repaired and solidified issues regarding custom functions and other
arbitrary expression constructs which within SQLAlchemy's column labeling
mechanics would seek to use ``str(obj)`` to get a string representation to
use as an anonymous column name in the ``.c`` collection of a subquery.
This is a very legacy behavior that performs poorly and leads to lots of
issues, so has been revised to no longer perform any compilation by
establishing specific methods on :class:`.FunctionElement` to handle this
case, as SQL functions are the only use case that it came into play. An
effect of this behavior is that an unlabeled column expression with no
derivable name will be given an arbitrary label starting with the prefix
``"_no_label"`` in the ``.c`` collection of a subquery; these were
previously being represented either as the generic stringification of that
expression, or as an internal symbol.

.. change::
:tags: usecase, orm
:ticketS: 6301

Altered some of the behavior repaired in :ticket:`6232` where the
``immediateload`` loader strategy no longer goes into recursive loops; the
modification is that an eager load (joinedload, selectinload, or
subqueryload) from A->bs->B which then states ``immediateload`` for a
simple manytoone B->a->A that's in the identity map will populate the B->A,
so that this attribute is back-populated when the collection of A/A.bs are
loaded. This allows the objects to be functional when detached.


.. changelog::

:released: April 17, 2021

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

Fixed regression where an empty in statement on a tuple would result
in an error when compiled with the option ``literal_binds=True``.

.. change::
:tags: bug, regression, orm, performance, sql
:tickets: 6304

Fixed a critical performance issue where the traversal of a
:func:`_sql.select` construct would traverse a repetitive product of the
represented FROM clauses as they were each referenced by columns in
the columns clause; for a series of nested subqueries with lots of columns
this could cause a large delay and significant memory growth. This
traversal is used by a wide variety of SQL and ORM functions, including by
the ORM :class:`_orm.Session` when it's configured to have
"table-per-bind", which while this is not a common use case, it seems to be
what Flask-SQLAlchemy is hardcoded as using, so the issue impacts
Flask-SQLAlchemy users. The traversal has been repaired to uniqify on FROM
clauses which was effectively what would happen implicitly with the pre-1.4
architecture.

.. change::
:tags: bug, postgresql, sql, regression
:tickets: 6303

Fixed an argument error in the default and PostgreSQL compilers that
would interfere with an UPDATE..FROM or DELETE..FROM..USING statement
that was then SELECTed from as a CTE.

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

Fixed regression where an attribute that is mapped to a
:func:`_orm.synonym` could not be used in column loader options such as
:func:`_orm.load_only`.

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

Established support for :func:`_orm.synoynm` in conjunction with
hybrid property, assocaitionproxy is set up completely, including that
synonyms can be established linking to these constructs which work
fully. This is a behavior that was semi-explicitly disallowed previously,
however since it did not fail in every scenario, explicit support
for assoc proxy and hybrids has been added.


.. changelog::