Sqlalchemy

:released: August 20, 2018

.. change::
:tags: bug, py3k

Started importing "collections" from "collections.abc" under Python 3.3 and
greater for Python 3.8 compatibility. Pull request courtesy Nathaniel
Knight.

.. change::
:tag: bug, sqlite

Fixed issue where the "schema" name used for a SQLite database within table
reflection would not quote the schema name correctly. Pull request
courtesy Phillip Cloud.

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

Fixed issue that is closely related to :ticket:`3639` where an expression
rendered in a boolean context on a non-native boolean backend would
be compared to 1/0 even though it is already an implicitly boolean
expression, when :meth:`_expression.ColumnElement.self_group` were used. While this
does not affect the user-friendly backends (MySQL, SQLite) it was not
handled by Oracle (and possibly SQL Server). Whether or not the
expression is implicitly boolean on any database is now determined
up front as an additional check to not generate the integer comparison
within the compilation of the statement.

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

For cx_Oracle, Integer datatypes will now be bound to "int", per advice
from the cx_Oracle developers. Previously, using cx_Oracle.NUMBER caused a
loss in precision within the cx_Oracle 6.x series.


.. change::
:tags: bug, orm, declarative
:tickets: 4321

Fixed issue in previously untested use case, allowing a declarative mapped
class to inherit from a classically-mapped class outside of the declarative
base, including that it accommodates for unmapped intermediate classes. An
unmapped intermediate class may specify ``__abstract__``, which is now
interpreted correctly, or the intermediate class can remain unmarked, and
the classically mapped base class will be detected within the hierarchy
regardless. In order to anticipate existing scenarios which may be mixing
in classical mappings into existing declarative hierarchies, an error is
now raised if multiple mapped bases are detected for a given class.

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

Added missing window function parameters
:paramref:`.WithinGroup.over.range_` and :paramref:`.WithinGroup.over.rows`
parameters to the :meth:`.WithinGroup.over` and
:meth:`.FunctionFilter.over` methods, to correspond to the range/rows
feature added to the "over" method of SQL functions as part of
:ticket:`3049` in version 1.1.

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

Fixed bug where the multi-table support for UPDATE and DELETE statements
did not consider the additional FROM elements as targets for correlation,
when a correlated SELECT were also combined with the statement. This
change now includes that a SELECT statement in the WHERE clause for such a
statement will try to auto-correlate back to these additional tables in the
parent UPDATE/DELETE or unconditionally correlate if
:meth:`_expression.Select.correlate` is used. Note that auto-correlation raises an
error if the SELECT statement would have no FROM clauses as a result, which
can now occur if the parent UPDATE/DELETE specifies the same tables in its
additional set of tables; specify :meth:`_expression.Select.correlate` explicitly to
resolve.

.. changelog::

:released: July 13, 2018

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

Fixed bug where a :class:`.Sequence` would be dropped explicitly before any
:class:`_schema.Table` that refers to it, which breaks in the case when the
sequence is also involved in a server-side default for that table, when
using :meth:`_schema.MetaData.drop_all`. The step which processes sequences
to be dropped via non server-side column default functions is now invoked
after the table itself is dropped.

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

Fixed bug in :class:`.Bundle` construct where placing two columns of the
same name would be de-duplicated, when the :class:`.Bundle` were used as
part of the rendered SQL, such as in the ORDER BY or GROUP BY of the statement.


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

Fixed regression in 1.2.9 due to :ticket:`4287` where using a
:class:`_orm.Load` option in conjunction with a string wildcard would result
in a TypeError.

.. changelog::

:released: June 29, 2018

.. change::
:tags: bug, mysql

Fixed percent-sign doubling in mysql-connector-python dialect, which does
not require de-doubling of percent signs. Additionally, the mysql-
connector-python driver is inconsistent in how it passes the column names
in cursor.description, so a workaround decoder has been added to
conditionally decode these randomly-sometimes-bytes values to unicode only
if needed. Also improved test support for mysql-connector-python, however
it should be noted that this driver still has issues with unicode that
continue to be unresolved as of yet.


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

Fixed bug in MSSQL reflection where when two same-named tables in different
schemas had same-named primary key constraints, foreign key constraints
referring to one of the tables would have their columns doubled, causing
errors. Pull request courtesy Sean Dunn.

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

Fixed regression in 1.2 due to :ticket:`4147` where a :class:`_schema.Table` that
has had some of its indexed columns redefined with new ones, as would occur
when overriding columns during reflection or when using
:paramref:`_schema.Table.extend_existing`, such that the :meth:`_schema.Table.tometadata`
method would fail when attempting to copy those indexes as they still
referred to the replaced column. The copy logic now accommodates for this
condition.


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

Fixed bug in index reflection where on MySQL 8.0 an index that includes
ASC or DESC in an indexed column specification would not be correctly
reflected, as MySQL 8.0 introduces support for returning this information
in a table definition string.

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

Fixed issue where chaining multiple join elements inside of
:meth:`_query.Query.join` might not correctly adapt to the previous left-hand
side, when chaining joined inheritance classes that share the same base
class.

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

Fixed bug in cache key generation for baked queries which could cause a
too-short cache key to be generated for the case of eager loads across
subclasses. This could in turn cause the eagerload query to be cached in
place of a non-eagerload query, or vice versa, for a polymorphic "selectin"
load, or possibly for lazy loads or selectin loads as well.

.. change::
:tags: bug, sqlite

Fixed issue in test suite where SQLite 3.24 added a new reserved word that
conflicted with a usage in TypeReflectionTest. Pull request courtesy Nils
Philippsen.

.. change::
:tags: feature, oracle
:tickets: 4290
:versions: 1.3.0b1

Added a new event currently used only by the cx_Oracle dialect,
:meth:`.DialectEvents.setiputsizes`. The event passes a dictionary of
:class:`.BindParameter` objects to DBAPI-specific type objects that will be
passed, after conversion to parameter names, to the cx_Oracle
``cursor.setinputsizes()`` method. This allows both visibility into the
setinputsizes process as well as the ability to alter the behavior of what
datatypes are passed to this method.

.. seealso::

:ref:`cx_oracle_setinputsizes`

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

Fixed bug in new polymorphic selectin loading where the BakedQuery used
internally would be mutated by the given loader options, which would both
inappropriately mutate the subclass query as well as carry over the effect
to subsequent queries.

.. change::
:tags: bug, py3k
:tickets: 4291

Replaced the usage of inspect.formatargspec() with a vendored version
copied from the Python standard library, as inspect.formatargspec()
is deprecated and as of Python 3.7.0 is emitting a warning.

.. change::
:tags: feature, ext
:tickets: 4243
:versions: 1.3.0b1

Added new attribute :attr:`_query.Query.lazy_loaded_from` which is populated
with an :class:`.InstanceState` that is using this :class:`_query.Query` in
order to lazy load a relationship. The rationale for this is that
it serves as a hint for the horizontal sharding feature to use, such that
the identity token of the state can be used as the default identity token
to use for the query within id_chooser().

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

Fixed bug in MySQLdb dialect and variants such as PyMySQL where an
additional "unicode returns" check upon connection makes explicit use of
the "utf8" character set, which in MySQL 8.0 emits a warning that utf8mb4
should be used. This is now replaced with a utf8mb4 equivalent.
Documentation is also updated for the MySQL dialect to specify utf8mb4 in
all examples. Additional changes have been made to the test suite to use
utf8mb3 charsets and databases (there seem to be collation issues in some
edge cases with utf8mb4), and to support configuration default changes made
in MySQL 8.0 such as explicit_defaults_for_timestamp as well as new errors
raised for invalid MyISAM indexes.



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

The :class:`_expression.Update` construct now accommodates a :class:`_expression.Join` object
as supported by MySQL for UPDATE..FROM. As the construct already
accepted an alias object for a similar purpose, the feature of UPDATE
against a non-table was already implied so this has been added.

.. change::
:tags: bug, mssql, py3k
:tickets: 4273

Fixed issue within the SQL Server dialect under Python 3 where when running
against a non-standard SQL server database that does not contain either the
"sys.dm_exec_sessions" or "sys.dm_pdw_nodes_exec_sessions" views, leading
to a failure to fetch the isolation level, the error raise would fail due
to an UnboundLocalError.



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

Fixed regression caused by :ticket:`4256` (itself a regression fix for
:ticket:`4228`) which breaks an undocumented behavior which converted for a
non-sequence of entities passed directly to the :class:`_query.Query` constructor
into a single-element sequence. While this behavior was never supported or
documented, it's already in use so has been added as a behavioral contract
to :class:`_query.Query`.

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

Fixed an issue that was both a performance regression in 1.2 as well as an
incorrect result regarding the "baked" lazy loader, involving the
generation of cache keys from the original :class:`_query.Query` object's loader
options. If the loader options were built up in a "branched" style using
common base elements for multiple options, the same options would be
rendered into the cache key repeatedly, causing both a performance issue as
well as generating the wrong cache key. This is fixed, along with a
performance improvement when such "branched" options are applied via
:meth:`_query.Query.options` to prevent the same option objects from being
applied repeatedly.

.. change::
:tags: bug, oracle, mysql
:tickets: 4275

Fixed INSERT FROM SELECT with CTEs for the Oracle and MySQL dialects, where
the CTE was being placed above the entire statement as is typical with
other databases, however Oracle and MariaDB 10.2 wants the CTE underneath
the "INSERT" segment. Note that the Oracle and MySQL dialects don't yet
work when a CTE is applied to a subquery inside of an UPDATE or DELETE
statement, as the CTE is still applied to the top rather than inside the
subquery.


.. changelog::

:released: May 28, 2018

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

Fixed regression in 1.2.7 caused by :ticket:`4228`, which itself was fixing
a 1.2-level regression, where the ``query_cls`` callable passed to a
:class:`.Session` was assumed to be a subclass of :class:`_query.Query` with
class method availability, as opposed to an arbitrary callable. In
particular, the dogpile caching example illustrates ``query_cls`` as a
function and not a :class:`_query.Query` subclass.

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

Fixed connection pool issue whereby if a disconnection error were raised
during the connection pool's "reset on return" sequence in conjunction with
an explicit transaction opened against the enclosing :class:`_engine.Connection`
object (such as from calling :meth:`.Session.close` without a rollback or
commit, or calling :meth:`_engine.Connection.close` without first closing a
transaction declared with :meth:`_engine.Connection.begin`), a double-checkin would
result, which could then lead towards concurrent checkouts of the same
connection. The double-checkin condition is now prevented overall by an
assertion, as well as the specific double-checkin scenario has been
fixed.

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

The Oracle BINARY_FLOAT and BINARY_DOUBLE datatypes now participate within
cx_Oracle.setinputsizes(), passing along NATIVE_FLOAT, so as to support the
NaN value. Additionally, :class:`_oracle.BINARY_FLOAT`,
:class:`_oracle.BINARY_DOUBLE` and :class:`_oracle.DOUBLE_PRECISION` now
subclass :class:`.Float`, since these are floating point datatypes, not
decimal. These datatypes were already defaulting the
:paramref:`.Float.asdecimal` flag to False in line with what
:class:`.Float` already does.

.. change::
:tags: bug, oracle

Added reflection capabilities for the :class:`_oracle.BINARY_FLOAT`,
:class:`_oracle.BINARY_DOUBLE` datatypes.


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

The horizontal sharding extension now makes use of the identity token
added to ORM identity keys as part of :ticket:`4137`, when an object
refresh or column-based deferred load or unexpiration operation occurs.
Since we know the "shard" that the object originated from, we make
use of this value when refreshing, thereby avoiding queries against
other shards that don't match this object's identity in any case.

.. change::
:tags: bug, sql

Fixed issue where the "ambiguous literal" error message used when
interpreting literal values as SQL expression values would encounter a
tuple value, and fail to format the message properly. Pull request courtesy
Miguel Ventura.

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

Fixed a 1.2 regression caused by :ticket:`4061` where the SQL Server
"BIT" type would be considered to be "native boolean". The goal here
was to avoid creating a CHECK constraint on the column, however the bigger
issue is that the BIT value does not behave like a true/false constant
and cannot be interpreted as a standalone expression, e.g.
"WHERE <column>". The SQL Server dialect now goes back to being
non-native boolean, but with an extra flag that still avoids creating
the CHECK constraint.

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

Altered the Oracle dialect such that when an :class:`.Integer` type is in
use, the cx_Oracle.NUMERIC type is set up for setinputsizes(). In
SQLAlchemy 1.1 and earlier, cx_Oracle.NUMERIC was passed for all numeric
types unconditionally, and in 1.2 this was removed to allow for better
numeric precision. However, for integers, some database/client setups
will fail to coerce boolean values True/False into integers which introduces
regressive behavior when using SQLAlchemy 1.2. Overall, the setinputsizes
logic seems like it will need a lot more flexibility going forward so this
is a start for that.

.. change::
:tags: bug, engine

Fixed a reference leak issue where the values of the parameter dictionary
used in a statement execution would remain referenced by the "compiled
cache", as a result of storing the key view used by Python 3 dictionary
keys(). Pull request courtesy Olivier Grisel.

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

Fixed a long-standing regression that occurred in version
1.0, which prevented the use of a custom :class:`.MapperOption`
that alters the _params of a :class:`_query.Query` object for a
lazy load, since the lazy loader itself would overwrite those
parameters. This applies to the "temporal range" example
on the wiki. Note however that the
:meth:`_query.Query.populate_existing` method is now required in
order to rewrite the mapper options associated with an object
already loaded in the identity map.

As part of this change, a custom defined
:class:`.MapperOption` will now cause lazy loaders related to
the target object to use a non-baked query by default unless
the :meth:`.MapperOption._generate_cache_key` method is implemented.
In particular, this repairs one regression which occurred when
using the dogpile.cache "advanced" example, which was not
returning cached results and instead emitting SQL due to an
incompatibility with the baked query loader; with the change,
the ``RelationshipCache`` option included for many releases
in the dogpile example will disable the "baked" query altogether.
Note that the dogpile example is also modernized to avoid both
of these issues as part of issue :ticket:`4258`.

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

Fixed a race condition which could occur if automap
:meth:`.AutomapBase.prepare` were used within a multi-threaded context
against other threads which may call :func:`.configure_mappers` as a
result of use of other mappers. The unfinished mapping work of automap
is particularly sensitive to being pulled in by a
:func:`.configure_mappers` step leading to errors.

.. change::
:tags: bug, orm

Fixed bug where the new :meth:`.baked.Result.with_post_criteria`
method would not interact with a subquery-eager loader correctly,
in that the "post criteria" would not be applied to embedded
subquery eager loaders. This is related to :ticket:`4128` in that
the post criteria feature is now used by the lazy loader.

.. change::
:tags: bug, tests
:tickets: 4249

Fixed a bug in the test suite where if an external dialect returned
``None`` for ``server_version_info``, the exclusion logic would raise an
``AttributeError``.

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

Updated the dogpile.caching example to include new structures that
accommodate for the "baked" query system, which is used by default within
lazy loaders and some eager relationship loaders. The dogpile.caching
"relationship_caching" and "advanced" examples were also broken due to
:ticket:`4256`. The issue here is also worked-around by the fix in
:ticket:`4128`.

.. changelog::

:released: April 20, 2018

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

Fixed regression in 1.2 within sharded query feature where the
new "identity_token" element was not being correctly considered within
the scope of a lazy load operation, when searching the identity map
for a related many-to-one element. The new behavior will allow for
making use of the "id_chooser" in order to determine the best identity
key to retrieve from the identity map. In order to achieve this, some
refactoring of 1.2's "identity_token" approach has made some slight changes
to the implementation of ``ShardedQuery`` which should be noted for other
derivations of this class.

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

Fixed bug where the special "not equals" operator for the PostgreSQL
"range" datatypes such as DATERANGE would fail to render "IS NOT NULL" when
compared to the Python ``None`` value.



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

Fixed 1.2 regression caused by :ticket:`4060` where the query used to
reflect SQL Server cross-schema foreign keys was limiting the criteria
incorrectly.



.. change::
:tags: bug, oracle

The Oracle NUMBER datatype is reflected as INTEGER if the precision is NULL
and the scale is zero, as this is how INTEGER values come back when
reflected from Oracle's tables. Pull request courtesy Kent Bower.

.. change::
:tags: feature, postgresql
:tickets: 4160
:versions: 1.3.0b1

Added new PG type :class:`_postgresql.REGCLASS` which assists in casting
table names to OID values. Pull request courtesy Sebastian Bank.

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

Fixed issue where the compilation of an INSERT statement with the
"literal_binds" option that also uses an explicit sequence and "inline"
generation, as on PostgreSQL and Oracle, would fail to accommodate the
extra keyword argument within the sequence processing routine.

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

Fixed issue in single-inheritance loading where the use of an aliased
entity against a single-inheritance subclass in conjunction with the
:meth:`_query.Query.select_from` method would cause the SQL to be rendered with
the unaliased table mixed in to the query, causing a cartesian product. In
particular this was affecting the new "selectin" loader when used against a
single-inheritance subclass.

.. changelog::

:released: March 30, 2018

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

Adjusted the SQL Server version detection for pyodbc to only allow for
numeric tokens, filtering out non-integers, since the dialect does tuple-
numeric comparisons with this value. This is normally true for all known
SQL Server / pyodbc drivers in any case.

.. change::
:tags: feature, postgresql

Added support for "PARTITION BY" in PostgreSQL table definitions,
using "postgresql_partition_by". Pull request courtesy
Vsevolod Solovyov.

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

Fixed a regression that occurred from the previous fix to :ticket:`4204` in
version 1.2.5, where a CTE that refers to itself after the
:meth:`_expression.CTE.alias` method has been called would not refer to itself
correctly.

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

Fixed bug in connection pool where a connection could be present in the
pool without all of its "connect" event handlers called, if a previous
"connect" handler threw an exception; note that the dialects themselves
have connect handlers that emit SQL, such as those which set transaction
isolation, which can fail if the database is in a non-available state, but
still allows a connection. The connection is now invalidated first if any
of the connect handlers fail.

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

The minimum cx_Oracle version supported is 5.2 (June 2015). Previously,
the dialect asserted against version 5.0 but as of 1.2.2 we are using some
symbols that did not appear until 5.2.

.. change::
:tags: bug, declarative
:tickets: 4221

Removed a warning that would be emitted when calling upon
``__table_args__``, ``__mapper_args__`` as named with a ``declared_attr``
method, when called from a non-mapped declarative mixin. Calling these
directly is documented as the approach to use when one is overriding one
of these methods on a mapped class. The warning still emits for regular
attribute names.

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

Fixed bug where using :meth:`.Mutable.associate_with` or
:meth:`.Mutable.as_mutable` in conjunction with a class that has non-
primary mappers set up with alternatively-named attributes would produce an
attribute error. Since non-primary mappers are not used for persistence,
the mutable extension now excludes non-primary mappers from its
instrumentation steps.


.. changelog::