Sqlalchemy

:released: July 21, 2019

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

Fixed bug where using reflection function such as :meth:`_schema.MetaData.reflect`
with an :class:`_engine.Engine` object that had execution options applied to it
would fail, as the resulting :class:`.OptionEngine` proxy object failed to
include a ``.engine`` attribute used within the reflection routines.

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

Fixed bug where the special logic to render "NULL" for the
:class:`_types.TIMESTAMP` datatype when ``nullable=True`` would not work if the
column's datatype were a :class:`.TypeDecorator` or a :class:`.Variant`.
The logic now ensures that it unwraps down to the original
:class:`_types.TIMESTAMP` so that this special case NULL keyword is correctly
rendered when requested.

.. change::
:tags: performance, orm
:tickets: 4775

The optimization applied to selectin loading in :ticket:`4340` where a JOIN
is not needed to eagerly load related items is now applied to many-to-one
relationships as well, so that only the related table is queried for a
simple join condition. In this case, the related items are queried
based on the value of a foreign key column on the parent; if these columns
are deferred or otherwise not loaded on any of the parent objects in
the collection, the loader falls back to the JOIN method.


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

Fixed regression caused by :ticket:`4365` where a join from an entity to
itself without using aliases no longer raises an informative error message,
instead failing on an assertion. The informative error condition has been
restored.


.. change::
:tags: orm, feature
:tickets: 4736

Added new loader option method :meth:`_orm.Load.options` which allows loader
options to be constructed hierarchically, so that many sub-options can be
applied to a particular path without needing to call :func:`.defaultload`
many times. Thanks to Alessio Bogon for the idea.


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

Added support for reflection of indexes on PostgreSQL partitioned tables,
which was added to PostgreSQL as of version 11.

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

Enhanced MySQL/MariaDB version string parsing to accommodate for exotic
MariaDB version strings where the "MariaDB" word is embedded among other
alphanumeric characters such as "MariaDBV1". This detection is critical in
order to correctly accommodate for API features that have split between MySQL
and MariaDB such as the "transaction_isolation" system variable.


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

Ensured that the queries used to reflect indexes and view definitions will
explicitly CAST string parameters into NVARCHAR, as many SQL Server drivers
frequently treat string values, particularly those with non-ascii
characters or larger string values, as TEXT which often don't compare
correctly against VARCHAR characters in SQL Server's information schema
tables for some reason. These CAST operations already take place for
reflection queries against SQL Server ``information_schema.`` tables but
were missing from three additional queries that are against ``sys.``
tables.

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

Fixed an issue where the :meth:`.orm._ORMJoin.join` method, which is a
not-internally-used ORM-level method that exposes what is normally an
internal process of :meth:`_query.Query.join`, did not propagate the ``full`` and
``outerjoin`` keyword arguments correctly. Pull request courtesy Denis
Kataev.

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

Adjusted the initialization for :class:`.Enum` to minimize how often it
invokes the ``.__members__`` attribute of a given PEP-435 enumeration
object, to suit the case where this attribute is expensive to invoke, as is
the case for some popular third party enumeration libraries.


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

Fixed bug where a many-to-one relationship that specified ``uselist=True``
would fail to update correctly during a primary key change where a related
column needs to change.


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

Fixed bug where the detection for many-to-one or one-to-one use with a
"dynamic" relationship, which is an invalid configuration, would fail to
raise if the relationship were configured with ``uselist=True``. The
current fix is that it warns, instead of raises, as this would otherwise be
backwards incompatible, however in a future release it will be a raise.


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

Fixed bug where a synonym created against a mapped attribute that does not
exist yet, as is the case when it refers to backref before mappers are
configured, would raise recursion errors when trying to test for attributes
on it which ultimately don't exist (as occurs when the classes are run
through Sphinx autodoc), as the unconfigured state of the synonym would put
it into an attribute not found loop.


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

Added support for multidimensional Postgresql array literals via nesting
the :class:`_postgresql.array` object within another one. The
multidimensional array type is detected automatically.

.. seealso::

:class:`_postgresql.array`

.. change::
:tags: bug, sql, postgresql
:tickets: 4760

Fixed issue where the :class:`_functions.array_agg` construct in combination with
:meth:`.FunctionElement.filter` would not produce the correct operator
precedence in combination with the array index operator.


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

Fixed an unlikely issue where the "corresponding column" routine for unions
and other :class:`_selectable.CompoundSelect` objects could return the wrong column in
some overlapping column situations, thus potentially impacting some ORM
operations when set operations are in use, if the underlying
:func:`_expression.select` constructs were used previously in other similar kinds of
routines, due to a cached value not being cleared.

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

Added support for composite (tuple) IN operators with SQLite, by rendering
the VALUES keyword for this backend. As other backends such as DB2 are
known to use the same syntax, the syntax is enabled in the base compiler
using a dialect-level flag ``tuple_in_values``. The change also includes
support for "empty IN tuple" expressions for SQLite when using "in_()"
between a tuple value and an empty set.


.. changelog::

:released: June 17, 2019

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

Fixed bug where MySQL ON DUPLICATE KEY UPDATE would not accommodate setting
a column to the value NULL. Pull request courtesy Lukáš Banič.

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

Fixed a series of related bugs regarding joined table inheritance more than
two levels deep, in conjunction with modification to primary key values,
where those primary key columns are also linked together in a foreign key
relationship as is typical for joined table inheritance. The intermediary
table in a three-level inheritance hierarchy will now get its UPDATE if
only the primary key value has changed and passive_updates=False (e.g.
foreign key constraints not being enforced), whereas before it would be
skipped; similarly, with passive_updates=True (e.g. ON UPDATE CASCADE in
effect), the third-level table will not receive an UPDATE statement as was
the case earlier which would fail since CASCADE already modified it. In a
related issue, a relationship linked to a three-level inheritance hierarchy
on the primary key of an intermediary table of a joined-inheritance
hierarchy will also correctly have its foreign key column updated when the
parent object's primary key is modified, even if that parent object is a
subclass of the linked parent class, whereas before these classes would
not be counted.

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

Fixed bug where the :attr:`_orm.Mapper.all_orm_descriptors` accessor would
return an entry for the :class:`_orm.Mapper` itself under the declarative
``__mapper__`` key, when this is not a descriptor. The ``.is_attribute``
flag that's present on all :class:`.InspectionAttr` objects is now
consulted, which has also been modified to be ``True`` for an association
proxy, as it was erroneously set to False for this object.

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

Fixed regression in :meth:`_query.Query.join` where the ``aliased=True`` flag
would not properly apply clause adaptation to filter criteria, if a
previous join were made to the same entity. This is because the adapters
were placed in the wrong order. The order has been reversed so that the
adapter for the most recent ``aliased=True`` call takes precedence as was
the case in 1.2 and earlier. This broke the "elementtree" examples among
other things.

.. change::
:tags: bug, orm, py3k
:tickets: 4674

Replaced the Python compatibility routines for ``getfullargspec()`` with a
fully vendored version from Python 3.3. Originally, Python was emitting
deprecation warnings for this function in Python 3.8 alphas. While this
change was reverted, it was observed that Python 3 implementations for
``getfullargspec()`` are an order of magnitude slower as of the 3.4 series
where it was rewritten against ``Signature``. While Python plans to
improve upon this situation, SQLAlchemy projects for now are using a simple
replacement to avoid any future issues.

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

Reworked the attribute mechanics used by :class:`.AliasedClass` to no
longer rely upon calling ``__getattribute__`` on the MRO of the wrapped
class, and to instead resolve the attribute normally on the wrapped class
using getattr(), and then unwrap/adapt that. This allows a greater range
of attribute styles on the mapped class including special ``__getattr__()``
schemes; but it also makes the code simpler and more resilient in general.

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

Added support for column sorting flags when reflecting indexes for
PostgreSQL, including ASC, DESC, NULLSFIRST, NULLSLAST. Also adds this
facility to the reflection system in general which can be applied to other
dialects in future releases. Pull request courtesy Eli Collins.

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

Fixed bug where PostgreSQL dialect could not correctly reflect an ENUM
datatype that has no members, returning a list with ``None`` for the
``get_enums()`` call and raising a TypeError when reflecting a column which
has such a datatype. The inspection now returns an empty list.

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

Addressed a range of quoting issues originating from the use of the
:func:`_expression.literal_column`` construct. When this construct is
"proxied" through a subquery and referred to by a label matching its
text, the label does not have quoting rules applied to it, even if the
string in the :class:`.Label` was set up using a :class:`.quoted_name``
construct. Not applying quoting to the text of the :class:`.Label` is a
bug because this text is strictly a SQL identifier name and not a SQL
expression, and the string should not have quotes embedded into it
already unlike the :func:`_expression.literal_column` which it may be
applied towards. The existing behavior of a non-labeled
:func:`_expression.literal_column` being propagated as is on the
outside of a subquery is maintained in order to help with manual
quoting schemes, although it's not clear if valid SQL can be generated
for such a construct in any case.

.. changelog::

:released: May 27, 2019

.. change::
:tags: feature, mssql
:tickets: 4657

Added support for SQL Server filtered indexes, via the ``mssql_where``
parameter which works similarly to that of the ``postgresql_where`` index
function in the PostgreSQL dialect.

.. seealso::

:ref:`mssql_index_where`

.. change::
:tags: bug, misc
:tickets: 4625

Removed errant "sqla_nose.py" symbol from MANIFEST.in which created an
undesirable warning message.

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

Fixed that the :class:`.GenericFunction` class was inadvertently
registering itself as one of the named functions. Pull request courtesy
Adrien Berchet.

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

Moved the "rollback" which occurs during dialect initialization so that it
occurs after additional dialect-specific initialize steps, in particular
those of the psycopg2 dialect which would inadvertently leave transactional
state on the first new connection, which could interfere with some
psycopg2-specific APIs which require that no transaction is started. Pull
request courtesy Matthew Wilkes.


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

Fixed issue where the :paramref:`.AttributeEvents.active_history` flag
would not be set for an event listener that propagated to a subclass via the
:paramref:`.AttributeEvents.propagate` flag. This bug has been present
for the full span of the :class:`.AttributeEvents` system.


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

Fixed regression where new association proxy system was still not proxying
hybrid attributes when they made use of the ``hybrid_property.expression``
decorator to return an alternate SQL expression, or when the hybrid
returned an arbitrary :class:`.PropComparator`, at the expression level.
This involved further generalization of the heuristics used to detect the
type of object being proxied at the level of :class:`.QueryableAttribute`,
to better detect if the descriptor ultimately serves mapped classes or
column expressions.

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

Applied the mapper "configure mutex" against the declarative class mapping
process, to guard against the race which can occur if mappers are used
while dynamic module import schemes are still in the process of configuring
mappers for related classes. This does not guard against all possible race
conditions, such as if the concurrent import has not yet encountered the
dependent classes as of yet, however it guards against as much as possible
within the SQLAlchemy declarative process.

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

Added error code 20047 to "is_disconnect" for pymssql. Pull request
courtesy Jon Schuff.


.. change::
:tags: bug, postgresql, orm
:tickets: 4661

Fixed an issue where the "number of rows matched" warning would emit even if
the dialect reported "supports_sane_multi_rowcount=False", as is the case
for psycogp2 with ``use_batch_mode=True`` and others.


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

Fixed issue where double negation of a boolean column wouldn't reset
the "NOT" operator.

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

Added support for DROP CHECK constraint which is required by MySQL 8.0.16
to drop a CHECK constraint; MariaDB supports plain DROP CONSTRAINT. The
logic distinguishes between the two syntaxes by checking the server version
string for MariaDB presence. Alembic migrations has already worked
around this issue by implementing its own DROP for MySQL / MariaDB CHECK
constraints, however this change implements it straight in Core so that its
available for general use. Pull request courtesy Hannes Hansen.

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

A warning is now emitted for the case where a transient object is being
merged into the session with :meth:`.Session.merge` when that object is
already transient in the :class:`.Session`. This warns for the case where
the object would normally be double-inserted.


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

Fixed regression in new relationship m2o comparison logic first introduced
at :ref:`change_4359` when comparing to an attribute that is persisted as
NULL and is in an un-fetched state in the mapped instance. Since the
attribute has no explicit default, it needs to default to NULL when
accessed in a persistent setting.


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

The :class:`.GenericFunction` namespace is being migrated so that function
names are looked up in a case-insensitive manner, as SQL functions do not
collide on case sensitive differences nor is this something which would
occur with user-defined functions or stored procedures. Lookups for
functions declared with :class:`.GenericFunction` now use a case
insensitive scheme, however a deprecation case is supported which allows
two or more :class:`.GenericFunction` objects with the same name of
different cases to exist, which will cause case sensitive lookups to occur
for that particular name, while emitting a warning at function registration
time. Thanks to Adrien Berchet for a lot of work on this complicated
feature.


.. changelog::

:released: April 15, 2019

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

Fixed regression from release 1.3.2 caused by :ticket:`4562` where a URL
that contained only a query string and no hostname, such as for the
purposes of specifying a service file with connection information, would no
longer be propagated to psycopg2 properly. The change in :ticket:`4562`
has been adjusted to further suit psycopg2's exact requirements, which is
that if there are any connection parameters whatsoever, the "dsn" parameter
is no longer required, so in this case the query string parameters are
passed alone.

.. change::
:tags: bug, pool
:tickets: 4585

Fixed behavioral regression as a result of deprecating the "use_threadlocal"
flag for :class:`_pool.Pool`, where the :class:`.SingletonThreadPool` no longer
makes use of this option which causes the "rollback on return" logic to take
place when the same :class:`_engine.Engine` is used multiple times in the context
of a transaction to connect or implicitly execute, thereby cancelling the
transaction. While this is not the recommended way to work with engines
and connections, it is nonetheless a confusing behavioral change as when
using :class:`.SingletonThreadPool`, the transaction should stay open
regardless of what else is done with the same engine in the same thread.
The ``use_threadlocal`` flag remains deprecated however the
:class:`.SingletonThreadPool` now implements its own version of the same
logic.


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

Fixed 1.3 regression in new "ambiguous FROMs" query logic introduced in
:ref:`change_4365` where a :class:`_query.Query` that explicitly places an entity
in the FROM clause with :meth:`_query.Query.select_from` and also joins to it
using :meth:`_query.Query.join` would later cause an "ambiguous FROM" error if
that entity were used in additional joins, as the entity appears twice in
the "from" list of the :class:`_query.Query`. The fix resolves this ambiguity by
folding the standalone entity into the join that it's already a part of in
the same way that ultimately happens when the SELECT statement is rendered.

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

Fixed bug where using ``copy.copy()`` or ``copy.deepcopy()`` on
:class:`.MutableList` would cause the items within the list to be
duplicated, due to an inconsistency in how Python pickle and copy both make
use of ``__getstate__()`` and ``__setstate__()`` regarding lists. In order
to resolve, a ``__reduce_ex__`` method had to be added to
:class:`.MutableList`. In order to maintain backwards compatibility with
existing pickles based on ``__getstate__()``, the ``__setstate__()`` method
remains as well; the test suite asserts that pickles made against the old
version of the class can still be deserialized by the pickle module.

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

Adjusted the :meth:`_query.Query.filter_by` method to not call :func:`.and()`
internally against multiple criteria, instead passing it off to
:meth:`_query.Query.filter` as a series of criteria, instead of a single criteria.
This allows :meth:`_query.Query.filter_by` to defer to :meth:`_query.Query.filter`'s
treatment of variable numbers of clauses, including the case where the list
is empty. In this case, the :class:`_query.Query` object will not have a
``.whereclause``, which allows subsequent "no whereclause" methods like
:meth:`_query.Query.select_from` to behave consistently.

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

Fixed issue in SQL Server dialect where if a bound parameter were present in
an ORDER BY expression that would ultimately not be rendered in the SQL
Server version of the statement, the parameters would still be part of the
execution parameters, leading to DBAPI-level errors. Pull request courtesy
Matt Lewellyn.

.. changelog::

:released: April 2, 2019

.. change::
:tags: bug, documentation, sql
:tickets: 4580

Thanks to :ref:`change_3981`, we no longer need to rely on recipes that
subclass dialect-specific types directly, :class:`.TypeDecorator` can now
handle all cases. Additionally, the above change made it slightly less
likely that a direct subclass of a base SQLAlchemy type would work as
expected, which could be misleading. Documentation has been updated to use
:class:`.TypeDecorator` for these examples including the PostgreSQL
"ArrayOfEnum" example datatype and direct support for the "subclass a type
directly" has been removed.

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

Modified the :paramref:`.Select.with_for_update.of` parameter so that if a
join or other composed selectable is passed, the individual :class:`_schema.Table`
objects will be filtered from it, allowing one to pass a join() object to
the parameter, as occurs normally when using joined table inheritance with
the ORM. Pull request courtesy Raymond Lu.


.. change::
:tags: feature, postgresql
:tickets: 4562

Added support for parameter-less connection URLs for the psycopg2 dialect,
meaning, the URL can be passed to :func:`_sa.create_engine` as
``"postgresql+psycopg2://"`` with no additional arguments to indicate an
empty DSN passed to libpq, which indicates to connect to "localhost" with
no username, password, or database given. Pull request courtesy Julian
Mehnle.

.. change::
:tags: bug, orm, ext
:tickets: 4574, 4573

Restored instance-level support for plain Python descriptors, e.g.
``property`` objects, in conjunction with association proxies, in that if
the proxied object is not within ORM scope at all, it gets classified as
"ambiguous" but is proxed directly. For class level access, a basic class
level``__get__()`` now returns the
:class:`.AmbiguousAssociationProxyInstance` directly, rather than raising
its exception, which is the closest approximation to the previous behavior
that returned the :class:`.AssociationProxy` itself that's possible. Also
improved the stringification of these objects to be more descriptive of
current state.

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

Fixed bug where use of :func:`.with_polymorphic` or other aliased construct
would not properly adapt when the aliased target were used as the
:meth:`_expression.Select.correlate_except` target of a subquery used inside of a
:func:`.column_property`. This required a fix to the clause adaption
mechanics to properly handle a selectable that shows up in the "correlate
except" list, in a similar manner as which occurs for selectables that show
up in the "correlate" list. This is ultimately a fairly fundamental bug
that has lasted for a long time but it is hard to come across it.


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

Fixed regression where a new error message that was supposed to raise when
attempting to link a relationship option to an AliasedClass without using
:meth:`.PropComparator.of_type` would instead raise an ``AttributeError``.
Note that in 1.3, it is no longer valid to create an option path from a
plain mapper relationship to an :class:`.AliasedClass` without using
:meth:`.PropComparator.of_type`.

.. changelog::

:released: March 9, 2019

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

Fixed regression in SQL Server reflection due to :ticket:`4393` where the
removal of open-ended ``**kw`` from the :class:`.Float` datatype caused
reflection of this type to fail due to a "scale" argument being passed.

.. change::
:tags: bug, orm, ext
:tickets: 4522

Fixed regression where an association proxy linked to a synonym would no
longer work, both at instance level and at class level.

.. changelog::