Sqlalchemy

:released: April 30, 2023

.. change::
:tags: bug, orm
:tickets: 9728
:versions: 2.0.12

Fixed critical caching issue where the combination of
:func:`_orm.aliased()` and :func:`_hybrid.hybrid_property` expression
compositions would cause a cache key mismatch, leading to cache keys that
held onto the actual :func:`_orm.aliased` object while also not matching
that of equivalent constructs, filling up the cache.

.. change::
:tags: bug, orm
:tickets: 9634
:versions: 2.0.10

Fixed bug where various ORM-specific getters such as
:attr:`.ORMExecuteState.is_column_load`,
:attr:`.ORMExecuteState.is_relationship_load`,
:attr:`.ORMExecuteState.loader_strategy_path` etc. would throw an
``AttributeError`` if the SQL statement itself were a "compound select"
such as a UNION.

.. change::
:tags: bug, orm
:tickets: 9590
:versions: 2.0.9

Fixed endless loop which could occur when using "relationship to aliased
class" feature and also indicating a recursive eager loader such as
``lazy="selectinload"`` in the loader, in combination with another eager
loader on the opposite side. The check for cycles has been fixed to include
aliased class relationships.

.. changelog::

:released: March 18, 2023

.. change::
:tags: bug, sql
:tickets: 9075
:versions: 2.0.0rc3

Fixed bug / regression where using :func:`.bindparam()` with the same name
as a column in the :meth:`.Update.values` method of :class:`.Update`, as
well as the :meth:`_dml.Insert.values` method of :class:`_dml.Insert` in 2.0 only,
would in some cases silently fail to honor the SQL expression in which the
parameter were presented, replacing the expression with a new parameter of
the same name and discarding any other elements of the SQL expression, such
as SQL functions, etc. The specific case would be statements that were
constructed against ORM entities rather than plain :class:`.Table`
instances, but would occur if the statement were invoked with a
:class:`.Session` or a :class:`.Connection`.

:class:`.Update` part of the issue was present in both 2.0 and 1.4 and is
backported to 1.4.

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

Added :class:`_oracle.ROWID` to reflected types as this type may be used in
a "CREATE TABLE" statement.

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

Fixed stringify for a the :class:`.CreateSchema` and :class:`.DropSchema`
DDL constructs, which would fail with an ``AttributeError`` when
stringified without a dialect.


.. change::
:tags: usecase, mysql
:tickets: 9047
:versions: 2.0.0

Added support to MySQL index reflection to correctly reflect the
``mysql_length`` dictionary, which previously was being ignored.

.. change::
:tags: bug, postgresql
:tickets: 9048
:versions: 2.0.0

Added support to the asyncpg dialect to return the ``cursor.rowcount``
value for SELECT statements when available. While this is not a typical use
for ``cursor.rowcount``, the other PostgreSQL dialects generally provide
this value. Pull request courtesy Michael Gorven.

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

Fixed bug where a schema name given with brackets, but no dots inside the
name, for parameters such as :paramref:`_schema.Table.schema` would not be
interpreted within the context of the SQL Server dialect's documented
behavior of interpreting explicit brackets as token delimiters, first added
in 1.2 for 2626, when referring to the schema name in reflection
operations. The original assumption for 2626's behavior was that the
special interpretation of brackets was only significant if dots were
present, however in practice, the brackets are not included as part of the
identifier name for all SQL rendering operations since these are not valid
characters within regular or delimited identifiers. Pull request courtesy
Shan.


.. change::
:tags: bug, mypy
:versions: 2.0.0rc3

Adjustments made to the mypy plugin to accommodate for some potential
changes being made for issue 236 sqlalchemy2-stubs when using SQLAlchemy
1.4. These changes are being kept in sync within SQLAlchemy 2.0.
The changes are also backwards compatible with older versions of
sqlalchemy2-stubs.


.. change::
:tags: bug, mypy
:tickets: 9102
:versions: 2.0.0rc3

Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions
if a decorator for the :func:`_orm.registry.mapped` decorator were used
that was referenced in an expression with more than two components (e.g.
``Backend.mapper_registry.mapped``). This scenario is now ignored; when
using the plugin, the decorator expression needs to be two components (i.e.
``reg.mapped``).

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

Fixed critical SQL caching issue where use of the
:meth:`_sql.Operators.op` custom operator function would not produce an appropriate
cache key, leading to reduce the effectiveness of the SQL cache.


.. changelog::

:released: January 3, 2023

.. change::
:tags: bug, engine
:tickets: 8974
:versions: 2.0.0rc1

Fixed a long-standing race condition in the connection pool which could
occur under eventlet/gevent monkeypatching schemes in conjunction with the
use of eventlet/gevent ``Timeout`` conditions, where a connection pool
checkout that's interrupted due to the timeout would fail to clean up the
failed state, causing the underlying connection record and sometimes the
database connection itself to "leak", leaving the pool in an invalid state
with unreachable entries. This issue was first identified and fixed in
SQLAlchemy 1.2 for :ticket:`4225`, however the failure modes detected in
that fix failed to accommodate for ``BaseException``, rather than
``Exception``, which prevented eventlet/gevent ``Timeout`` from being
caught. In addition, a block within initial pool connect has also been
identified and hardened with a ``BaseException`` -> "clean failed connect"
block to accommodate for the same condition in this location.
Big thanks to Github user niklaus for their tenacious efforts in
identifying and describing this intricate issue.

.. change::
:tags: bug, postgresql
:tickets: 9023
:versions: 2.0.0rc1

Fixed bug where the PostgreSQL
:paramref:`_postgresql.Insert.on_conflict_do_update.constraint` parameter
would accept an :class:`.Index` object, however would not expand this index
out into its individual index expressions, instead rendering its name in an
ON CONFLICT ON CONSTRAINT clause, which is not accepted by PostgreSQL; the
"constraint name" form only accepts unique or exclude constraint names. The
parameter continues to accept the index but now expands it out into its
component expressions for the render.

.. change::
:tags: bug, general
:tickets: 8995
:versions: 2.0.0rc1

Fixed regression where the base compat module was calling upon
``platform.architecture()`` in order to detect some system properties,
which results in an over-broad system call against the system-level
``file`` call that is unavailable under some circumstances, including
within some secure environment configurations.

.. change::
:tags: usecase, postgresql
:tickets: 8393
:versions: 2.0.0b5

Added the PostgreSQL type ``MACADDR8``.
Pull request courtesy of Asim Farooq.

.. change::
:tags: bug, sqlite
:tickets: 8969
:versions: 2.0.0b5

Fixed regression caused by new support for reflection of partial indexes on
SQLite added in 1.4.45 for :ticket:`8804`, where the ``index_list`` pragma
command in very old versions of SQLite (possibly prior to 3.8.9) does not
return the current expected number of columns, leading to exceptions raised
when reflecting tables and indexes.

.. change::
:tags: bug, tests
:versions: 2.0.0rc1

Fixed issue in tox.ini file where changes in the tox 4.0 series to the
format of "passenv" caused tox to not function correctly, in particular
raising an error as of tox 4.0.6.

.. change::
:tags: bug, tests
:tickets: 9002
:versions: 2.0.0rc1

Added new exclusion rule for third party dialects called
``unusual_column_name_characters``, which can be "closed" for third party
dialects that don't support column names with unusual characters such as
dots, slashes, or percent signs in them, even if the name is properly
quoted.


.. change::
:tags: bug, sql
:tickets: 9009
:versions: 2.0.0b5

Added parameter
:paramref:`.FunctionElement.column_valued.joins_implicitly`, which is
useful in preventing the "cartesian product" warning when making use of
table-valued or column-valued functions. This parameter was already
introduced for :meth:`.FunctionElement.table_valued` in :ticket:`7845`,
however it failed to be added for :meth:`.FunctionElement.column_valued`
as well.

.. change::
:tags: change, general
:tickets: 8983

A new deprecation "uber warning" is now emitted at runtime the
first time any SQLAlchemy 2.0 deprecation warning would normally be
emitted, but the ``SQLALCHEMY_WARN_20`` environment variable is not set.
The warning emits only once at most, before setting a boolean to prevent
it from emitting a second time.

This deprecation warning intends to notify users who may not have set an
appropriate constraint in their requirements files to block against a
surprise SQLAlchemy 2.0 upgrade and also alert that the SQLAlchemy 2.0
upgrade process is available, as the first full 2.0 release is expected
very soon. The deprecation warning can be silenced by setting the
environment variable ``SQLALCHEMY_SILENCE_UBER_WARNING`` to ``"1"``.

.. seealso::

:ref:`migration_20_toplevel`

.. change::
:tags: bug, orm
:tickets: 9033
:versions: 2.0.0rc1

Fixed issue in the internal SQL traversal for DML statements like
:class:`_dml.Update` and :class:`_dml.Delete` which would cause among other
potential issues, a specific issue using lambda statements with the ORM
update/delete feature.

.. change::
:tags: bug, sql
:tickets: 8989
:versions: 2.0.0b5

Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType
error in 1.4) when using an expression whose type included
:meth:`_types.TypeEngine.bind_expression`, in the context of an "expanding"
(i.e. "IN") parameter in conjunction with the ``literal_binds`` compiler
parameter.

.. change::
:tags: bug, sql
:tickets: 9029
:versions: 2.0.0rc1

Fixed issue in lambda SQL feature where the calculated type of a literal
value would not take into account the type coercion rules of the "compared
to type", leading to a lack of typing information for SQL expressions, such
as comparisons to :class:`_types.JSON` elements and similar.

.. changelog::

:released: December 10, 2022

.. change::
:tags: bug, orm
:tickets: 8862
:versions: 2.0.0rc1

Fixed bug where :meth:`_orm.Session.merge` would fail to preserve the
current loaded contents of relationship attributes that were indicated with
the :paramref:`_orm.relationship.viewonly` parameter, thus defeating
strategies that use :meth:`_orm.Session.merge` to pull fully loaded objects
from caches and other similar techniques. In a related change, fixed issue
where an object that contains a loaded relationship that was nonetheless
configured as ``lazy='raise'`` on the mapping would fail when passed to
:meth:`_orm.Session.merge`; checks for "raise" are now suspended within
the merge process assuming the :paramref:`_orm.Session.merge.load`
parameter remains at its default of ``True``.

Overall, this is a behavioral adjustment to a change introduced in the 1.4
series as of :ticket:`4994`, which took "merge" out of the set of cascades
applied by default to "viewonly" relationships. As "viewonly" relationships
aren't persisted under any circumstances, allowing their contents to
transfer during "merge" does not impact the persistence behavior of the
target object. This allows :meth:`_orm.Session.merge` to correctly suit one
of its use cases, that of adding objects to a :class:`.Session` that were
loaded elsewhere, often for the purposes of restoring from a cache.


.. change::
:tags: bug, orm
:tickets: 8881
:versions: 2.0.0rc1

Fixed issues in :func:`_orm.with_expression` where expressions that were
composed of columns that were referenced from the enclosing SELECT would
not render correct SQL in some contexts, in the case where the expression
had a label name that matched the attribute which used
:func:`_orm.query_expression`, even when :func:`_orm.query_expression` had
no default expression. For the moment, if the :func:`_orm.query_expression`
does have a default expression, that label name is still used for that
default, and an additional label with the same name will continue to be
ignored. Overall, this case is pretty thorny so further adjustments might
be warranted.

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

Backported a fix for SQLite reflection of unique constraints in attached
schemas, released in 2.0 as a small part of :ticket:`4379`. Previously,
unique constraints in attached schemas would be ignored by SQLite
reflection. Pull request courtesy Michael Gorven.

.. change::
:tags: bug, asyncio
:tickets: 8952
:versions: 2.0.0rc1

Removed non-functional ``merge()`` method from
:class:`_asyncio.AsyncResult`. This method has never worked and was
included with :class:`_asyncio.AsyncResult` in error.

.. change::
:tags: bug, oracle
:tickets: 8708
:versions: 2.0.0b4

Continued fixes for Oracle fix :ticket:`8708` released in 1.4.43 where
bound parameter names that start with underscores, which are disallowed by
Oracle, were still not being properly escaped in all circumstances.


.. change::
:tags: bug, postgresql
:tickets: 8748
:versions: 2.0.0rc1

Made an adjustment to how the PostgreSQL dialect considers column types
when it reflects columns from a table, to accommodate for alternative
backends which may return NULL from the PG ``format_type()`` function.

.. change::
:tags: usecase, sqlite
:tickets: 8903
:versions: 2.0.0rc1

Added support for the SQLite backend to reflect the "DEFERRABLE" and
"INITIALLY" keywords which may be present on a foreign key construct. Pull
request courtesy Michael Gorven.

.. change::
:tags: usecase, sql
:tickets: 8800
:versions: 2.0.0rc1

An informative re-raise is now thrown in the case where any "literal
bindparam" render operation fails, indicating the value itself and
the datatype in use, to assist in debugging when literal params
are being rendered in a statement.

.. change::
:tags: usecase, sqlite
:tickets: 8804
:versions: 2.0.0rc1

Added support for reflection of expression-oriented WHERE criteria included
in indexes on the SQLite dialect, in a manner similar to that of the
PostgreSQL dialect. Pull request courtesy Tobias Pfeiffer.

.. change::
:tags: bug, sql
:tickets: 8827
:versions: 2.0.0rc1

Fixed a series of issues regarding the position and sometimes the identity
of rendered bound parameters, such as those used for SQLite, asyncpg,
MySQL, Oracle and others. Some compiled forms would not maintain the order
of parameters correctly, such as the PostgreSQL ``regexp_replace()``
function, the "nesting" feature of the :class:`.CTE` construct first
introduced in :ticket:`4123`, and selectable tables formed by using the
:meth:`.FunctionElement.column_valued` method with Oracle.


.. change::
:tags: bug, oracle
:tickets: 8945
:versions: 2.0.0rc1

Fixed issue in Oracle compiler where the syntax for
:meth:`.FunctionElement.column_valued` was incorrect, rendering the name
``COLUMN_VALUE`` without qualifying the source table correctly.

.. change::
:tags: bug, engine
:tickets: 8963
:versions: 2.0.0rc1

Fixed issue where :meth:`_engine.Result.freeze` method would not work for
textual SQL using either :func:`_sql.text` or
:meth:`_engine.Connection.exec_driver_sql`.


.. changelog::

:released: November 12, 2022

.. change::
:tags: bug, sql
:tickets: 8790
:versions: 2.0.0b4

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

.. change::
:tags: bug, postgresql, mssql
:tickets: 8770
:versions: 2.0.0b4

For the PostgreSQL and SQL Server dialects only, adjusted the compiler so
that when rendering column expressions in the RETURNING clause, the "non
anon" label that's used in SELECT statements is suggested for SQL
expression elements that generate a label; the primary example is a SQL
function that may be emitting as part of the column's type, where the label
name should match the column's name by default. This restores a not-well
defined behavior that had changed in version 1.4.21 due to :ticket:`6718`,
:ticket:`6710`. The Oracle dialect has a different RETURNING implementation
and was not affected by this issue. Version 2.0 features an across the
board change for its widely expanded support of RETURNING on other
backends.


.. change::
:tags: bug, oracle

Fixed issue in the Oracle dialect where an INSERT statement that used
``insert(some_table).values(...).returning(some_table)`` against a full
:class:`.Table` object at once would fail to execute, raising an exception.

.. change::
:tags: bug, tests
:tickets: 8793
:versions: 2.0.0b4

Fixed issue where the ``--disable-asyncio`` parameter to the test suite
would fail to not actually run greenlet tests and would also not prevent
the suite from using a "wrapping" greenlet for the whole suite. This
parameter now ensures that no greenlet or asyncio use will occur within the
entire run when set.

.. change::
:tags: bug, tests

Adjusted the test suite which tests the Mypy plugin to accommodate for
changes in Mypy 0.990 regarding how it handles message output, which affect
how sys.path is interpreted when determining if notes and errors should be
printed for particular files. The change broke the test suite as the files
within the test directory itself no longer produced messaging when run
under the mypy API.

.. changelog::

:released: November 4, 2022

.. change::
:tags: bug, orm
:tickets: 8738
:versions: 2.0.0b3

Fixed issue in joined eager loading where an assertion fail would occur
with a particular combination of outer/inner joined eager loads, when
eager loading across three mappers where the middle mapper was
an inherited subclass mapper.


.. change::
:tags: bug, oracle
:tickets: 8708
:versions: 2.0.0b3

Fixed issue where bound parameter names, including those automatically
derived from similarly-named database columns, which contained characters
that normally require quoting with Oracle would not be escaped when using
"expanding parameters" with the Oracle dialect, causing execution errors.
The usual "quoting" for bound parameters used by the Oracle dialect is not
used with the "expanding parameters" architecture, so escaping for a large
range of characters is used instead, now using a list of characters/escapes
that are specific to Oracle.



.. change::
:tags: bug, orm
:tickets: 8721
:versions: 2.0.0b3

Fixed bug involving :class:`.Select` constructs, where combinations of
:meth:`.Select.select_from` with :meth:`.Select.join`, as well as when
using :meth:`.Select.join_from`, would cause the
:func:`_orm.with_loader_criteria` feature as well as the IN criteria needed
for single-table inheritance queries to not render, in cases where the
columns clause of the query did not explicitly include the left-hand side
entity of the JOIN. The correct entity is now transferred to the
:class:`.Join` object that's generated internally, so that the criteria
against the left side entity is correctly added.


.. change::
:tags: bug, mssql
:tickets: 8714
:versions: 2.0.0b3

Fixed issue with :meth:`.Inspector.has_table`, which when used against a
temporary table with the SQL Server dialect would fail on some Azure
variants, due to an unnecessary information schema query that is not
supported on those server versions. Pull request courtesy Mike Barry.

.. change::
:tags: bug, orm
:tickets: 8711
:versions: 2.0.0b3

An informative exception is now raised when the
:func:`_orm.with_loader_criteria` option is used as a loader option added
to a specific "loader path", such as when using it within
:meth:`.Load.options`. This use is not supported as
:func:`_orm.with_loader_criteria` is only intended to be used as a top
level loader option. Previously, an internal error would be generated.

.. change::
:tags: bug, oracle
:tickets: 8744
:versions: 2.0.0b3

Fixed issue where the ``nls_session_parameters`` view queried on first
connect in order to get the default decimal point character may not be
available depending on Oracle connection modes, and would therefore raise
an error. The approach to detecting decimal char has been simplified to
test a decimal value directly, instead of reading system views, which
works on any backend / driver.


.. change::
:tags: bug, orm
:tickets: 8753
:versions: 2.0.0b3

Improved "dictionary mode" for :meth:`_orm.Session.get` so that synonym
names which refer to primary key attribute names may be indicated in the
named dictionary.

.. change::
:tags: bug, engine, regression
:tickets: 8717
:versions: 2.0.0b3

Fixed issue where the :meth:`.PoolEvents.reset` event hook would not be be
called in all cases when a :class:`_engine.Connection` were closed and was
in the process of returning its DBAPI connection to the connection pool.

The scenario was when the :class:`_engine.Connection` had already emitted
``.rollback()`` on its DBAPI connection within the process of returning
the connection to the pool, where it would then instruct the connection
pool to forego doing its own "reset" to save on the additional method
call. However, this prevented custom pool reset schemes from being
used within this hook, as such hooks by definition are doing more than
just calling ``.rollback()``, and need to be invoked under all
circumstances. This was a regression that appeared in version 1.4.

For version 1.4, the :meth:`.PoolEvents.checkin` remains viable as an
alternate event hook to use for custom "reset" implementations. Version 2.0
will feature an improved version of :meth:`.PoolEvents.reset` which is
called for additional scenarios such as termination of asyncio connections,
and is also passed contextual information about the reset, to allow for
"custom connection reset" schemes which can respond to different reset
scenarios in different ways.

.. change::
:tags: bug, orm
:tickets: 8704
:versions: 2.0.0b3

Fixed issue where "selectin_polymorphic" loading for inheritance mappers
would not function correctly if the :paramref:`_orm.Mapper.polymorphic_on`
parameter referred to a SQL expression that was not directly mapped on the
class.

.. change::
:tags: bug, orm
:tickets: 8710
:versions: 2.0.0b3

Fixed issue where the underlying DBAPI cursor would not be closed when
using the :class:`_orm.Query` object as an iterator, if a user-defined exception
case were raised within the iteration process, thereby causing the iterator
to be closed by the Python interpreter. When using
:meth:`_orm.Query.yield_per` to create server-side cursors, this would lead
to the usual MySQL-related issues with server side cursors out of sync,
and without direct access to the :class:`.Result` object, end-user code
could not access the cursor in order to close it.

To resolve, a catch for ``GeneratorExit`` is applied within the iterator
method, which will close the result object in those cases when the
iterator were interrupted, and by definition will be closed by the
Python interpreter.

As part of this change as implemented for the 1.4 series, ensured that
``.close()`` methods are available on all :class:`.Result` implementations
including :class:`.ScalarResult`, :class:`.MappingResult`. The 2.0
version of this change also includes new context manager patterns for use
with :class:`.Result` classes.

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

Ensured all :class:`.Result` objects include a :meth:`.Result.close` method
as well as a :attr:`.Result.closed` attribute, including on
:class:`.ScalarResult` and :class:`.MappingResult`.

.. change::
:tags: bug, mssql, reflection
:tickets: 8700
:versions: 2.0.0b3

Fixed issue with :meth:`.Inspector.has_table`, which when used against a
view with the SQL Server dialect would erroneously return ``False``, due to
a regression in the 1.4 series which removed support for this on SQL
Server. The issue is not present in the 2.0 series which uses a different
reflection architecture. Test support is added to ensure ``has_table()``
remains working per spec re: views.

.. change::
:tags: bug, sql
:tickets: 8724
:versions: 2.0.0b3

Fixed issue which prevented the :func:`_sql.literal_column` construct from
working properly within the context of a :class:`.Select` construct as well
as other potential places where "anonymized labels" might be generated, if
the literal expression contained characters which could interfere with
format strings, such as open parenthesis, due to an implementation detail
of the "anonymous label" structure.


.. changelog::