Sqlalchemy

:released: October 19, 2021

.. change::
:tags: orm
:tickets: 6284

Passing a :class:`.Query` object to :meth:`_orm.Session.execute` is not
the intended use of this object, and will now raise a deprecation warning.

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

Added a "disconnect" condition for the "SSL SYSCALL error: Bad address"
error message as reported by psycopg2. Pull request courtesy Zeke Brechtel.

.. change::
:tags: bug, orm

Improved the exception message generated when configuring a mapping with
joined table inheritance where the two tables either have no foreign key
relationships set up, or where they have multiple foreign key relationships
set up. The message is now ORM specific and includes context that the
:paramref:`_orm.Mapper.inherit_condition` parameter may be needed
particularly for the ambiguous foreign keys case.


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

Fixed issue where SQL queries using the
:meth:`_functions.FunctionElement.within_group` construct could not be
pickled, typically when using the ``sqlalchemy.ext.serializer`` extension
but also for general generic pickling.

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

Fixed issue with :func:`_orm.with_loader_criteria` feature where ON
criteria would not be added to a JOIN for a query of the form
``select(A).join(B)``, stating a target while making use of an implicit
ON clause.

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

Fixed bug where the ORM "plugin", necessary for features such as
:func:`_orm.with_loader_criteria` to work correctly, would not be applied
to a :func:`_sql.select` which queried from an ORM column expression if it
made use of the :meth:`_sql.ColumnElement.label` modifier.



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

Fixed issue in mypy plugin to improve upon some issues detecting ``Enum()``
SQL types containing custom Python enumeration classes. Pull request
courtesy Hiroshi Ogawa.

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

Fixed issue in MySQL :func:`_mysql.match` construct where passing a clause
expression such as :func:`_sql.bindparam` or other SQL expression for the
"against" parameter would fail. Pull request courtesy Anton Kovalevich.


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

Fixed issue with :meth:`.Inspector.get_foreign_keys` where foreign
keys were omitted if they were established against a unique
index instead of a unique constraint.


.. change::
:tags: usecase, mssql

Added reflection support for SQL Server foreign key options, including
"ON UPDATE" and "ON DELETE" values of "CASCADE" and "SET NULL".

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

Repaired issue in new :paramref:`_sql.HasCTE.cte.nesting` parameter
introduced with :ticket:`4123` where a recursive :class:`_sql.CTE` using
:paramref:`_sql.HasCTE.cte.recursive` in typical conjunction with UNION
would not compile correctly. Additionally makes some adjustments so that
the :class:`_sql.CTE` construct creates a correct cache key.
Pull request courtesy Eric Masseran.

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

Fixed issue where the deprecation warning for the :class:`.URL` constructor
which indicates that the :meth:`.URL.create` method should be used would
not emit if a full positional argument list of seven arguments were passed;
additionally, validation of URL arguments will now occur if the constructor
is called in this way, which was being skipped previously.

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

Add missing methods added in :ticket:`6991` to
:class:`_scoping.scoped_session` and :func:`_asyncio.async_scoped_session`.

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

Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs
correctly; these examples had been missed when API changes like removing
"passive" from :meth:`_orm.Session.is_modified` were made as well as the
:meth:`_ormevents.SessionEvents.do_orm_execute()` event hook were added.

.. change::
:tags: bug, orm
:tickets: 6974, 6972

An extra layer of warning messages has been added to the functionality
of :meth:`_orm.Query.join` and the ORM version of
:meth:`_sql.Select.join`, where a few places where "automatic aliasing"
continues to occur will now be called out as a pattern to avoid, mostly
specific to the area of joined table inheritance where classes that share
common base tables are being joined together without using explicit aliases.
One case emits a legacy warning for a pattern that's not recommended,
the other case is fully deprecated.

The automatic aliasing within ORM join() which occurs for overlapping
mapped tables does not work consistently with all APIs such as
:func:`_orm.contains_eager()`, and rather than continue to try to make
these use cases work everywhere, replacing with a more user-explicit
pattern is clearer, less prone to bugs and simplifies SQLAlchemy's
internals further.

The warnings include links to the errors.rst page where each pattern is
demonstrated along with the recommended pattern to fix.

.. seealso::

:ref:`error_xaj1`

:ref:`error_xaj2`

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

Account for the :paramref:`_sql.table.schema` parameter passed to
the :func:`_sql.table` construct, such that it is taken into account
when accessing the :attr:`_sql.TableClause.fullname` attribute.

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

Fixed an inconsistency in the :meth:`_sql.ColumnOperators.any_` /
:meth:`_sql.ColumnOperators.all_` functions / methods where the special
behavior these functions have of "flipping" the expression such that the
"ANY" / "ALL" expression is always on the right side would not function if
the comparison were against the None value, that is, "column.any_() ==
None" should produce the same SQL expression as "null() == column.any_()".
Added more docs to clarify this as well, plus mentions that any_() / all_()
generally supersede the ARRAY version "any()" / "all()".

.. change::
:tags: engine, bug, postgresql
:tickets: 3247

The :meth:`_reflection.Inspector.reflect_table` method now supports
reflecting tables that do not have user defined columns. This allows
:meth:`_schema.MetaData.reflect` to properly complete reflection on
databases that contain such tables. Currently, only PostgreSQL is known to
support such a construct among the common database backends.

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

Fixed issue where "expanding IN" would fail to function correctly with
datatypes that use the :meth:`_types.TypeEngine.bind_expression` method,
where the method would need to be applied to each element of the
IN expression rather than the overall IN expression itself.

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

Fixed issue where IN expressions against a series of array elements, as can
be done with PostgreSQL, would fail to function correctly due to multiple
issues within the "expanding IN" feature of SQLAlchemy Core that was
standardized in version 1.4. The psycopg2 dialect now makes use of the
:meth:`_types.TypeEngine.bind_expression` method with :class:`_types.ARRAY`
to portably apply the correct casts to elements. The asyncpg dialect was
not affected by this issue as it applies bind-level casts at the driver
level rather than at the compiler level.


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

Fixed installation issue where the ``sqlalchemy.dialects.mysql`` module
would not be importable if "greenlet" were not installed.

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

Fixed issue with :meth:`.Inspector.has_table` where it would return False
if a local temp table with the same name from a different session happened
to be returned first when querying tempdb. This is a continuation of
:ticket:`6910` which accounted for the temp table existing only in the
alternate session and not the current one.

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

Fixed bug where iterating a :class:`_result.Result` from a :class:`_orm.Session`
after that :class:`_orm.Session` were closed would partially attach objects
to that session in an essentially invalid state. It now raises an exception
with a link to new documentation if an **un-buffered** result is iterated
from a :class:`_orm.Session` that was closed or otherwise had the
:meth:`_orm.Session.expunge_all` method called after that :class:`_result.Result`
was generated. The ``prebuffer_rows`` execution option, as is used
automatically by the asyncio extension for client-side result sets, may be
used to produce a :class:`_result.Result` where the ORM objects are prebuffered,
and in this case iterating the result will produce a series of detached
objects.

.. seealso::

:ref:`error_lkrp`

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

Fixed bug in SQL Server :class:`_mssql.DATETIMEOFFSET` datatype where the
ODBC implementation would not generate the correct DDL, for cases where the
type were converted using the ``dialect.type_descriptor()`` method, the
usage of which is illustrated in some documented examples for
:class:`.TypeDecorator`, though not necessary for most datatypes.
Regression was introduced by :ticket:`6366`. As part of this change, the
full list of SQL Server date types have been amended to return a "dialect
impl" that generates the same DDL name as the supertype.

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

Adjusted the "column disambiguation" logic that's new in 1.4, where the
same expression repeated gets an "extra anonymous" label, so that the logic
more aggressively deduplicates those labels when the repeated element
is the same Python expression object each time, as occurs in cases like
when using "singleton" values like :func:`_sql.null`. This is based on
the observation that at least some databases (e.g. MySQL, but not SQLite)
will raise an error if the same label is repeated inside of a subquery.

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

Related to :ticket:`7153`, fixed an issue where result column lookups would
fail for "adapted" SELECT statements that selected for "constant" value
expressions most typically the NULL expression, as would occur in such
places as joined eager loading in conjunction with limit/offset. This was
overall a regression due to issue :ticket:`6259` which removed all
"adaption" for constants like NULL, "true", and "false" when rewriting
expressions in a SQL statement, but this broke the case where the same
adaption logic were used to resolve the constant to a labeled expression
for the purposes of result set targeting.

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

Fixed regression where ORM loaded objects could not be pickled in cases
where loader options making use of ``"*"`` were used in certain
combinations, such as combining the :func:`_orm.joinedload` loader strategy
with ``raiseload('*')`` of sub-elements.


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

Implemented proper ``__reduce__()`` methods for all SQLAlchemy exception
objects to ensure they all support clean round trips when pickling, as
exception objects are often serialized for the purposes of various
debugging tools.

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

Fixed regression where the use of a :class:`_hybrid.hybrid_property`
attribute or a mapped :func:`_orm.composite` attribute as a key passed to
the :meth:`_dml.Update.values` method for an ORM-enabled
:class:`_dml.Update` statement, as well as when using it via the legacy
:meth:`_orm.Query.update` method, would be processed for incoming
ORM/hybrid/composite values within the compilation stage of the UPDATE
statement, which meant that in those cases where caching occurred,
subsequent invocations of the same statement would no longer receive the
correct values. This would include not only hybrids that use the
:meth:`_hybrid.hybrid_property.update_expression` method, but any use of a
plain hybrid attribute as well. For composites, the issue instead caused a
non-repeatable cache key to be generated, which would break caching and
could fill up the statement cache with repeated statements.

The :class:`_dml.Update` construct now handles the processing of key/value
pairs passed to :meth:`_dml.Update.values` and
:meth:`_dml.Update.ordered_values` up front when the construct is first
generated, before the cache key has been generated so that the key/value
pairs are processed each time, and so that the cache key is generated
against the individual column/value pairs that will ultimately be
used in the statement.


.. changelog::

:released: September 22, 2021

.. change::
:tags: bug, platform, regression
:tickets: 7024

Fixed regression due to :ticket:`7024` where the reorganization of the
"platform machine" names used by the ``greenlet`` dependency mis-spelled
"aarch64" and additionally omitted uppercase "AMD64" as is needed for
Windows machines. Pull request courtesy James Dow.

.. changelog::

:released: September 22, 2021

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

Fixed a bug in :meth:`_asyncio.AsyncSession.execute` and
:meth:`_asyncio.AsyncSession.stream` that required ``execution_options``
to be an instance of ``immutabledict`` when defined. It now
correctly accepts any mapping.

.. change::
:tags: engine, asyncio, usecase
:tickets: 6832

Improve the interface used by adapted drivers, like the asyncio ones,
to access the actual connection object returned by the driver.

The :class:`._ConnectionFairy` object has two new attributes:

* :attr:`._ConnectionFairy.dbapi_connection` always represents a DBAPI
compatible object. For pep-249 drivers, this is the DBAPI connection as
it always has been, previously accessed under the ``.connection``
attribute. For asyncio drivers that SQLAlchemy adapts into a pep-249
interface, the returned object will normally be a SQLAlchemy adaption
object called :class:`_engine.AdaptedConnection`.
* :attr:`._ConnectionFairy.driver_connection` always represents the actual
connection object maintained by the third party pep-249 DBAPI or async
driver in use. For standard pep-249 DBAPIs, this will always be the same
object as that of the ``dbapi_connection``. For an asyncio driver, it
will be the underlying asyncio-only connection object.

The ``.connection`` attribute remains available and is now a legacy alias
of ``.dbapi_connection``.

.. seealso::

:ref:`faq_dbapi_connection`


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

Implemented missing methods in :class:`_functions.FunctionElement` which,
while unused, would lead pylint to report them as unimplemented abstract
methods.

.. change::
:tags: bug, mssql, reflection
:tickets: 6910

Fixed an issue where :meth:`_reflection.has_table` returned
``True`` for local temporary tables that actually belonged to a
different SQL Server session (connection). An extra check is now
performed to ensure that the temp table detected is in fact owned
by the current session.

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

Fixed issue where the ability of the
:meth:`_events.ConnectionEvents.before_execute` method to alter the SQL
statement object passed, returning the new object to be invoked, was
inadvertently removed. This behavior has been restored.


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

Ensure that ``str()`` is called on the an
:paramref:`_url.URL.create.password` argument, allowing usage of objects
that implement the ``__str__()`` method as password attributes. Also
clarified that one such object is not appropriate to dynamically change the
password for each database connection; the approaches at
:ref:`engines_dynamic_tokens` should be used instead.

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

Fixed ORM issue where column expressions passed to ``query()`` or
ORM-enabled ``select()`` would be deduplicated on the identity of the
object, such as a phrase like ``select(A.id, null(), null())`` would
produce only one "NULL" expression, which previously was not the case in
1.3. However, the change also allows for ORM expressions to render as given
as well, such as ``select(A.data, A.data)`` will produce a result row with
two columns.

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

Fixed issue in :class:`_engine.URL` where validation of "drivername" would
not appropriately respond to the ``None`` value where a string were
expected.

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

Fixed issue where mypy plugin would crash when interpreting a
``query_expression()`` construct.

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

Added new parameter :paramref:`_sql.HasCTE.cte.nesting` to the
:class:`_sql.CTE` constructor and :meth:`_sql.HasCTE.cte` method, which
flags the CTE as one which should remain nested within an enclosing CTE,
rather than being moved to the top level of the outermost SELECT. While in
the vast majority of cases there is no difference in SQL functionality,
users have identified various edge-cases where true nesting of CTE
constructs is desirable. Much thanks to Eric Masseran for lots of work on
this intricate feature.

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

Added new methods :meth:`_orm.Session.scalars`,
:meth:`_engine.Connection.scalars`, :meth:`_asyncio.AsyncSession.scalars`
and :meth:`_asyncio.AsyncSession.stream_scalars`, which provide a short cut
to the use case of receiving a row-oriented :class:`_result.Result` object
and converting it to a :class:`_result.ScalarResult` object via the
:meth:`_engine.Result.scalars` method, to return a list of values rather
than a list of rows. The new methods are analogous to the long existing
:meth:`_orm.Session.scalar` and :meth:`_engine.Connection.scalar` methods
used to return a single value from the first row only. Pull request
courtesy Miguel Grinberg.

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

Added loader options to :meth:`_orm.Session.merge` and
:meth:`_asyncio.AsyncSession.merge` via a new
:paramref:`_orm.Session.merge.options` parameter, which will apply the
given loader options to the ``get()`` used internally by merge, allowing
eager loading of relationships etc. to be applied when the merge process
loads a new object. Pull request courtesy Daniel Stone.

.. change::
:tags: feature, asyncio, mysql
:tickets: 6993

Added initial support for the ``asyncmy`` asyncio database driver for MySQL
and MariaDB. This driver is very new, however appears to be the only
current alternative to the ``aiomysql`` driver which currently appears to
be unmaintained and is not working with current Python versions. Much
thanks to long2ice for the pull request for this dialect.

.. seealso::

:ref:`asyncmy`

.. change::
:tags: bug, asyncio

Added missing ``**kw`` arguments to the
:meth:`_asyncio.AsyncSession.connection` method.

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

Fixed a two issues where combinations of ``select()`` and ``join()`` when
adapted to form a copy of the element would not completely copy the state
of all column objects associated with subqueries. A key problem this caused
is that usage of the :meth:`_sql.ClauseElement.params` method (which should
probably be moved into a legacy category as it is inefficient and error
prone) would leave copies of the old :class:`_sql.BindParameter` objects
around, leading to issues in correctly setting the parameters at execution
time.



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

Fixed issue in recently repaired ``Query.with_entities()`` method where the
flag that determines automatic uniquing for legacy ORM ``Query`` objects
only would be set to ``True`` inappropriately in cases where the
``with_entities()`` call would be setting the ``Query`` to return
column-only rows, which are not uniqued.

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

Qualify ``version()`` call to avoid shadowing issues if a different
search path is configured by the user.

.. change::
:tags: bug, engine, postgresql
:tickets: 6963

Fixed issue where an engine that had
:paramref:`_sa.create_engine.implicit_returning` set to False would fail to
function when PostgreSQL's "fast insertmany" feature were used in
conjunction with a ``Sequence``, as well as if any kind of "executemany"
with "return_defaults()" were used in conjunction with a ``Sequence``. Note
that PostgreSQL "fast insertmany" uses "RETURNING" by definition, when the
SQL statement is passed to the driver; overall, the
:paramref:`_sa.create_engine.implicit_returning` flag is legacy and has no
real use in modern SQLAlchemy, and will be deprecated in a separate change.

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

Fixed issue in mypy plugin where columns on a mixin would not be correctly
interpreted if the mapped class relied upon a ``__tablename__`` routine
that came from a superclass.

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

The :class:`_postgresql.ENUM` datatype is PostgreSQL-native and therefore
should not be used with the ``native_enum=False`` flag. This flag is now
ignored if passed to the :class:`_postgresql.ENUM` datatype and a warning
is emitted; previously the flag would cause the type object to fail to
function correctly.


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

Fixed issue related to new :meth:`_sql.HasCTE.add_cte` feature where
pairing two "INSERT..FROM SELECT" statements simultaneously would lose
track of the two independent SELECT statements, leading to the wrong SQL.

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

Deprecate usage of :class:`_orm.scoped_session` with asyncio drivers. When
using Asyncio the :class:`_asyncio.async_scoped_session` should be used
instead.

.. change::
:tags: bug, platform
:tickets: 7024

Further adjusted the "greenlet" package specifier in setup.cfg to use a
long chain of "or" expressions, so that the comparison of
``platform_machine`` to a specific identifier matches only the complete
string.

.. change::
:tags: bug, sqlite

Fixed bug where the error message for SQLite invalid isolation level on the
pysqlite driver would fail to indicate that "AUTOCOMMIT" is one of the
valid isolation levels.

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

Fixed issue where using ORM column expressions as keys in the list of
dictionaries passed to :meth:`_sql.Insert.values` for "multi-valued insert"
would not be processed correctly into the correct column expressions.

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

The :class:`_asyncio.AsyncSession` now supports overriding which
:class:`_orm.Session` it uses as the proxied instance. A custom ``Session``
class can be passed using the :paramref:`.AsyncSession.sync_session_class`
parameter or by subclassing the ``AsyncSession`` and specifying a custom
:attr:`.AsyncSession.sync_session_class`.

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

Added a CAST(VARCHAR2(128)) to the "table name", "owner", and other
DDL-name parameters as used in reflection queries against Oracle system
views such as ALL_TABLES, ALL_TAB_CONSTRAINTS, etc to better enable
indexing to take place against these columns, as they previously would be
implicitly handled as NVARCHAR2 due to Python's use of Unicode for strings;
these columns are documented in all Oracle versions as being VARCHAR2 with
lengths varying from 30 to 128 characters depending on server version.
Additionally, test support has been enabled for Unicode-named DDL
structures against Oracle databases.

.. changelog::

:released: August 18, 2021

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

Fix issue in :class:`_sql.CTE` where new :meth:`_sql.HasCTE.add_cte` method
added in version 1.4.21 / :ticket:`6752` failed to function correctly for
"compound select" structures such as :func:`_sql.union`,
:func:`_sql.union_all`, :func:`_sql.except`, etc. Pull request courtesy
Eric Masseran.

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

Added new attribute :attr:`_sql.Select.columns_clause_froms` that will
retrieve the FROM list implied by the columns clause of the
:class:`_sql.Select` statement. This differs from the old
:attr:`_sql.Select.froms` collection in that it does not perform any ORM
compilation steps, which necessarily deannotate the FROM elements and do
things like compute joinedloads etc., which makes it not an appropriate
candidate for the :meth:`_sql.Select.select_from` method. Additionally adds
a new parameter
:paramref:`_sql.Select.with_only_columns.maintain_column_froms` that
transfers this collection to :meth:`_sql.Select.select_from` before
replacing the columns collection.

In addition, the :attr:`_sql.Select.froms` is renamed to
:meth:`_sql.Select.get_final_froms`, to stress that this collection is not
a simple accessor and is instead calculated given the full state of the
object, which can be an expensive call when used in an ORM context.

Additionally fixes a regression involving the
:func:`_orm.with_only_columns` function to support applying criteria to
column elements that were replaced with either
:meth:`_sql.Select.with_only_columns` or :meth:`_orm.Query.with_entities` ,
which had broken as part of :ticket:`6503` released in 1.4.19.

.. change::
:tags: bug, orm, sql
:tickets: 6824

Fixed issue where a bound parameter object that was "cloned" would cause a
name conflict in the compiler, if more than one clone of this parameter
were used at the same time in a single statement. This could occur in
particular with things like ORM single table inheritance queries that
indicated the same "discriminator" value multiple times in one query.


.. change::
:tags: bug, mssql, sql
:tickets: 6863

Fixed issue where the ``literal_binds`` compiler flag, as used externally
to render bound parameters inline, would fail to work when used with a
certain class of parameters known as "literal_execute", which covers things
like LIMIT and OFFSET values for dialects where the drivers don't allow a
bound parameter, such as SQL Server's "TOP" clause. The issue locally
seemed to affect only the MSSQL dialect.

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

Fixed issue in loader strategies where the use of the
:meth:`_orm.Load.options` method, particularly when nesting multiple calls,
would generate an overly long and more importantly non-deterministic cache
key, leading to very large cache keys which were also not allowing
efficient cache usage, both in terms of total memory used as well as number
of entries used in the cache itself.

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

Fixed an issue in the ``CacheKey.to_offline_string()`` method used by the
dogpile.caching example where attempting to create a proper cache key from
the special "lambda" query generated by the lazy loader would fail to
include the parameter values, leading to an incorrect cache key.


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

Revised the means by which the
:attr:`_orm.ORMExecuteState.user_defined_options` accessor receives
:class:`_orm.UserDefinedOption` and related option objects from the
context, with particular emphasis on the "selectinload" on the loader
strategy where this previously was not working; other strategies did not
have this problem. The objects that are associated with the current query
being executed, and not that of a query being cached, are now propagated
unconditionally. This essentially separates them out from the "loader
strategy" options which are explicitly associated with the compiled state
of a query and need to be used in relation to the cached query.

The effect of this fix is that a user-defined option, such as those used
by the dogpile.caching example as well as for other recipes such as
defining a "shard id" for the horizontal sharing extension, will be
correctly propagated to eager and lazy loaders regardless of whether
a cached query was ultimately invoked.


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

Adjusted the "from linter" warning feature to accommodate for a chain of
joins more than one level deep where the ON clauses don't explicitly match
up the targets, such as an expression such as "ON TRUE". This mode of use
is intended to cancel the cartesian product warning simply by the fact that
there's a JOIN from "a to b", which was not working for the case where the
chain of joins had more than one element.

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

Added the "is_comparison" flag to the PostgreSQL "overlaps",
"contained_by", "contains" operators, so that they work in relevant ORM
contexts as well as in conjunction with the "from linter" feature.

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

Fixed issue where the unit of work would internally use a 2.0-deprecated
SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20
were enabled.


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

Fixed issue in :func:`_orm.selectinload` where use of the new
:meth:`_orm.PropComparator.and_` feature within options that were nested
more than one level deep would fail to update bound parameter values that
were in the nested criteria, as a side effect of SQL statement caching.


.. change::
:tags: bug, general
:tickets: 6136

The setup requirements have been modified such ``greenlet`` is a default
requirement only for those platforms that are well known for ``greenlet``
to be installable and for which there is already a pre-built binary on
pypi; the current list is ``x86_64 aarch64 ppc64le amd64 win32``. For other
platforms, greenlet will not install by default, which should enable
installation and test suite running of SQLAlchemy 1.4 on platforms that
don't support ``greenlet``, excluding any asyncio features. In order to
install with the ``greenlet`` dependency included on a machine architecture
outside of the above list, the ``[asyncio]`` extra may be included by
running ``pip install sqlalchemy[asyncio]`` which will then attempt to
install ``greenlet``.

Additionally, the test suite has been repaired so that tests can complete
fully when greenlet is not installed, with appropriate skips for
asyncio-related tests.

.. change::
:tags: enum, schema
:tickets: 6146

Unify behaviour :class:`_schema.Enum` in native and non-native
implementations regarding the accepted values for an enum with
aliased elements.
When :paramref:`_schema.Enum.omit_aliases` is ``False`` all values,
alias included, are accepted as valid values.
When :paramref:`_schema.Enum.omit_aliases` is ``True`` only non aliased values
are accepted as valid values.

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

Fixed issue where the horizontal sharding extension would not correctly
accommodate for a plain textual SQL statement passed to
:meth:`_orm.Session.execute`.

.. change::
:tags: bug, orm
:tickets: 6889, 6079

Adjusted ORM loader internals to no longer use the "lambda caching" system
that was added in 1.4, as well as repaired one location that was still
using the previous "baked query" system for a query. The lambda caching
system remains an effective way to reduce the overhead of building up
queries that have relatively fixed usage patterns. In the case of loader
strategies, the queries used are responsible for moving through lots of
arbitrary options and criteria, which is both generated and sometimes
consumed by end-user code, that make the lambda cache concept not any more
efficient than not using it, at the cost of more complexity. In particular
the problems noted by :ticket:`6881` and :ticket:`6887` are made are made
considerably less complicated by removing this feature internally.



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

Fixed an issue where the :class:`_orm.Bundle` construct would not create
proper cache keys, leading to inefficient use of the query cache. This
had some impact on the "selectinload" strategy and was identified as
part of :ticket:`6889`.

.. change::
:tags: usecase, mypy
:tickets: 6804, 6759

Added support for SQLAlchemy classes to be defined in user code using
"generic class" syntax as defined by ``sqlalchemy2-stubs``, e.g.
``Column[String]``, without the need for qualifying these constructs within
a ``TYPE_CHECKING`` block by implementing the Python special method
``__class_getitem__()``, which allows this syntax to pass without error at
runtime.

.. change::
:tags: bug, sql

Fixed issue in lambda caching system where an element of a query that
produces no cache key, like a custom option or clause element, would still
populate the expression in the "lambda cache" inappropriately.

.. changelog::

:released: July 21, 2021

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

Fixed issue where use of the :paramref:`_sql.case.whens` parameter passing
a dictionary positionally and not as a keyword argument would emit a 2.0
deprecation warning, referring to the deprecation of passing a list
positionally. The dictionary format of "whens", passed positionally, is
still supported and was accidentally marked as deprecated.


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

Fixed issue in new :meth:`_schema.Table.table_valued` method where the
resulting :class:`_sql.TableValuedColumn` construct would not respond
correctly to alias adaptation as is used throughout the ORM, such as for
eager loading, polymorphic loading, etc.


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

Fixed issue where usage of the :meth:`_result.Result.unique` method with an
ORM result that included column expressions with unhashable types, such as
``JSON`` or ``ARRAY`` using non-tuples would silently fall back to using
the ``id()`` function, rather than raising an error. This now raises an
error when the :meth:`_result.Result.unique` method is used in a 2.0 style
ORM query. Additionally, hashability is assumed to be True for result
values of unknown type, such as often happens when using SQL functions of
unknown return type; if values are truly not hashable then the ``hash()``
itself will raise.

For legacy ORM queries, since the legacy :class:`_orm.Query` object
uniquifies in all cases, the old rules remain in place, which is to use
``id()`` for result values of unknown type as this legacy uniquing is
mostly for the purpose of uniquing ORM entities and not column values.

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

Fixed an issue where clearing of mappers during things like test suite
teardowns could cause a "dictionary changed size" warning during garbage
collection, due to iteration of a weak-referencing dictionary. A ``list()``
has been applied to prevent concurrent GC from affecting this operation.

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

Fixed issue where type-specific bound parameter handlers would not be
called upon in the case of using the :meth:`_sql.Insert.values` method with
the Python ``None`` value; in particular, this would be noticed when using
the :class:`_types.JSON` datatype as well as related PostgreSQL specific
types such as :class:`_postgresql.JSONB` which would fail to encode the
Python ``None`` value into JSON null, however the issue was generalized to
any bound parameter handler in conjunction with this specific method of
:class:`_sql.Insert`.


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

Added some guards against ``KeyError`` in the event system to accommodate
the case that the interpreter is shutting down at the same time
:meth:`_engine.Engine.dispose` is being called, which would cause stack
trace warnings.


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

Fixed critical caching issue where the ORM's persistence feature using
INSERT..RETURNING would cache an incorrect query when mixing the "bulk
save" and standard "flush" forms of INSERT.

.. changelog::

:released: July 14, 2021

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

Modified the approach used for history tracking of scalar object
relationships that are not many-to-one, i.e. one-to-one relationships that
would otherwise be one-to-many. When replacing a one-to-one value, the
"old" value that would be replaced is no longer loaded immediately, and is
instead handled during the flush process. This eliminates an historically
troublesome lazy load that otherwise often occurs when assigning to a
one-to-one attribute, and is particularly troublesome when using
"lazy='raise'" as well as asyncio use cases.

This change does cause a behavioral change within the
:meth:`_orm.AttributeEvents.set` event, which is nonetheless currently
documented, which is that the event applied to such a one-to-one attribute
will no longer receive the "old" parameter if it is unloaded and the
:paramref:`_orm.relationship.active_history` flag is not set. As is
documented in :meth:`_orm.AttributeEvents.set`, if the event handler needs
to receive the "old" value when the event fires off, the active_history
flag must be established either with the event listener or with the
relationship. This is already the behavior with other kinds of attributes
such as many-to-one and column value references.

The change additionally will defer updating a backref on the "old" value
in the less common case that the "old" value is locally present in the
session, but isn't loaded on the relationship in question, until the
next flush occurs. If this causes an issue, again the normal
:paramref:`_orm.relationship.active_history` flag can be set to ``True``
on the relationship.

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

Added new method :meth:`_sql.HasCTE.add_cte` to each of the
:func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and
:func:`_sql.delete` constructs. This method will add the given
:class:`_sql.CTE` as an "independent" CTE of the statement, meaning it
renders in the WITH clause above the statement unconditionally even if it
is not otherwise referenced in the primary statement. This is a popular use
case on the PostgreSQL database where a CTE is used for a DML statement
that runs against database rows independently of the primary statement.

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

Fixed issue in :meth:`_postgresql.Insert.on_conflict_do_nothing` and
:meth:`_postgresql.Insert.on_conflict_do_update` where the name of a unique
constraint passed as the ``constraint`` parameter would not be properly
truncated for length if it were based on a naming convention that generated
a too-long name for the PostgreSQL max identifier length of 63 characters,
in the same way which occurs within a CREATE TABLE statement.

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

Fixed issue in CTE constructs where a recursive CTE that referred to a
SELECT that has duplicate column names, which are typically deduplicated
using labeling logic in 1.4, would fail to refer to the deduplicated label
name correctly within the WITH clause.

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

Fixed regression where the special dotted-schema name handling for the SQL
Server dialect would not function correctly if the dotted schema name were
used within the ``schema_translate_map`` feature.

.. change::
:tags: orm, regression
:tickets: 6718

Fixed ORM regression where ad-hoc label names generated for hybrid
properties and potentially other similar types of ORM-enabled expressions
would usually be propagated outwards through subqueries, allowing the name
to be retained in the final keys of the result set even when selecting from
subqueries. Additional state is now tracked in this case that isn't lost
when a hybrid is selected out of a Core select / subquery.


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

Fixed issue where the PostgreSQL ``ENUM`` datatype as embedded in the
``ARRAY`` datatype would fail to emit correctly in create/drop when the
``schema_translate_map`` feature were also in use. Additionally repairs a
related issue where the same ``schema_translate_map`` feature would not
work for the ``ENUM`` datatype in combination with a ``CAST``, that's also
intrinsic to how the ``ARRAY(ENUM)`` combination works on the PostgreSQL
dialect.


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

Fixed regression where the :func:`_sql.tablesample` construct would fail to
be executable when constructed given a floating-point sampling value not
embedded within a SQL function.

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

Fixed issue in :meth:`_postgresql.Insert.on_conflict_do_nothing` and
:meth:`_postgresql.Insert.on_conflict_do_update` where the name of a unique
constraint passed as the ``constraint`` parameter would not be properly
quoted if it contained characters which required quoting.


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

Fixed regression caused in 1.4.19 due to :ticket:`6503` and related
involving :meth:`_orm.Query.with_entities` where the new structure used
would be inappropriately transferred to an enclosing :class:`_orm.Query`
when making use of set operations such as :meth:`_orm.Query.union`, causing
the JOIN instructions within to be applied to the outside query as well.

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

Fixed regression which appeared in version 1.4.3 due to :ticket:`6060`
where rules that limit ORM adaptation of derived selectables interfered
with other ORM-adaptation based cases, in this case when applying
adaptations for a :func:`_orm.with_polymorphic` against a mapping which
uses a :func:`_orm.column_property` which in turn makes use of a scalar
select that includes a :func:`_orm.aliased` object of the mapped table.

.. changelog::