Sqlalchemy

:released: July 5, 2023

.. change::
:tags: bug, sql
:tickets: 10042
:versions: 2.0.18

Fixed issue where the :meth:`_sql.ColumnOperators.regexp_match`
when using "flags" would not produce a "stable" cache key, that
is, the cache key would keep changing each time causing cache pollution.
The same issue existed for :meth:`_sql.ColumnOperators.regexp_replace`
with both the flags and the actual replacement expression.
The flags are now represented as fixed modifier strings rendered as
safestrings rather than bound parameters, and the replacement
expression is established within the primary portion of the "binary"
element so that it generates an appropriate cache key.

Note that as part of this change, the
:paramref:`_sql.ColumnOperators.regexp_match.flags` and
:paramref:`_sql.ColumnOperators.regexp_replace.flags` have been modified to
render as literal strings only, whereas previously they were rendered as
full SQL expressions, typically bound parameters. These parameters should
always be passed as plain Python strings and not as SQL expression
constructs; it's not expected that SQL expression constructs were used in
practice for this parameter, so this is a backwards-incompatible change.

The change also modifies the internal structure of the expression
generated, for :meth:`_sql.ColumnOperators.regexp_replace` with or without
flags, and for :meth:`_sql.ColumnOperators.regexp_match` with flags. Third
party dialects which may have implemented regexp implementations of their
own (no such dialects could be located in a search, so impact is expected
to be low) would need to adjust the traversal of the structure to
accommodate.


.. change::
:tags: bug, sql
:versions: 2.0.18

Fixed issue in mostly-internal :class:`.CacheKey` construct where the
``__ne__()`` operator were not properly implemented, leading to nonsensical
results when comparing :class:`.CacheKey` instances to each other.




.. change::
:tags: bug, extensions
:versions: 2.0.17

Fixed issue in mypy plugin for use with mypy 1.4.

.. change::
:tags: platform, usecase

Compatibility improvements to work fully with Python 3.12

.. changelog::

: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::