Sqlalchemy

:released: July 6, 2016

.. change::
:tags: bug, postgresql
:tickets: 3739
:versions: 1.1.0b3

Fixed bug whereby :class:`.TypeDecorator` and :class:`.Variant`
types were not deeply inspected enough by the PostgreSQL dialect
to determine if SMALLSERIAL or BIGSERIAL needed to be rendered
rather than SERIAL.

.. change::
:tags: bug, oracle
:tickets: 3741
:versions: 1.1.0b3

Fixed bug in :paramref:`.Select.with_for_update.of`, where the Oracle
"rownum" approach to LIMIT/OFFSET would fail to accommodate for the
expressions inside the "OF" clause, which must be stated at the topmost
level referring to expression within the subquery. The expressions are
now added to the subquery if needed.

.. change::
:tags: bug, sql
:tickets: 3735
:versions: 1.1.0b2

Fixed issue in SQL math negation operator where the type of the
expression would no longer be the numeric type of the original.
This would cause issues where the type determined result set
behaviors.

.. change::
:tags: bug, sql
:tickets: 3728
:versions: 1.1.0b2

Fixed bug whereby the ``__getstate__`` / ``__setstate__``
methods for sqlalchemy.util.Properties were
non-working due to the transition in the 1.0 series to ``__slots__``.
The issue potentially impacted some third-party applications.
Pull request courtesy Pieter Mulder.

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

:meth:`_expression.FromClause.count` is pending deprecation for 1.1. This function
makes use of an arbitrary column in the table and is not reliable;
for Core use, ``func.count()`` should be preferred.

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

Fixed bug in :class:`_expression.CTE` structure which would cause it to not
clone properly when a union was used, as is common in a recursive
CTE. The improper cloning would cause errors when the CTE is used
in various ORM contexts such as that of a :func:`.column_property`.

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

Fixed bug whereby :meth:`_schema.Table.tometadata` would make a duplicate
:class:`.UniqueConstraint` for each :class:`_schema.Column` object that
featured the ``unique=True`` parameter.

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

Fixed bug in cross-schema foreign key reflection in conjunction
with the :paramref:`_schema.MetaData.schema` argument, where a referenced
table that is present in the "default" schema would fail since there
would be no way to indicate a :class:`_schema.Table` that has "blank" for
a schema. The special symbol :attr:`_schema.BLANK_SCHEMA` has been
added as an available value for :paramref:`_schema.Table.schema` and
:paramref:`.Sequence.schema`, indicating that the schema name
should be forced to be ``None`` even if :paramref:`_schema.MetaData.schema`
is specified.

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

Fixed a regression that occurred in the
examples/vertical/dictlike-polymorphic.py example which prevented it
from running.

.. changelog::

:released: May 16, 2016

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

Fixed bug in "evaluate" strategy of :meth:`_query.Query.update` and
:meth:`_query.Query.delete` which would fail to accommodate a bound
parameter with a "callable" value, as which occurs when filtering
by a many-to-one equality expression along a relationship.

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

Added disconnect detection support for the error string
"SSL error: decryption failed or bad record mac". Pull
request courtesy Iuri de Silvio.

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

Fixed bug where by ROW_NUMBER OVER clause applied for OFFSET
selects in SQL Server would inappropriately substitute a plain column
from the local statement that overlaps with a label name used by
the ORDER BY criteria of the statement.

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

Fixed bug whereby the event listeners used for backrefs could
be inadvertently applied multiple times, when using a deep class
inheritance hierarchy in conjunction with multiple mapper configuration
steps.

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

Fixed bug whereby passing a :func:`_expression.text` construct to the
:meth:`_query.Query.group_by` method would raise an error, instead
of interpreting the object as a SQL fragment.

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

Fixed a bug in the cx_Oracle connect process that caused a TypeError
when the either the user, password or dsn was empty. This prevented
external authentication to Oracle databases, and prevented connecting
to the default dsn. The connect string oracle:// now logs into the
default dsn using the Operating System username, equivalent to
connecting using '/' with sqlplus.

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

Fixed a bug in the result proxy used mainly by Oracle when binary and
other LOB types are in play, such that when query / statement caching
were used, the type-level result processors, notably that required by
the binary type itself but also any other processor, would become lost
after the first run of the statement due to it being removed from the
cached result metadata.

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

Changed the "directed graph" example to no longer consider
integer identifiers of nodes as significant; the "higher" / "lower"
references now allow mutual edges in both directions.

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

Fixed bug where when using ``case_sensitive=False`` with an
:class:`_engine.Engine`, the result set would fail to correctly accommodate
for duplicate column names in the result set, causing an error
when the statement is executed in 1.0, and preventing the
"ambiguous column" exception from functioning in 1.1.

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

Fixed bug where the negation of an EXISTS expression would not
be properly typed as boolean in the result, and also would fail to be
anonymously aliased in a SELECT list as is the case with a
non-negated EXISTS construct.

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

Fixed bug where "unconsumed column names" exception would fail to
be raised in the case where :meth:`_expression.Insert.values` were called
with a list of parameter mappings, instead of a single mapping
of parameters. Pull request courtesy Athena Yao.

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

Anonymous labeling is applied to a :attr:`.func` construct that is
passed to :func:`.column_property`, so that if the same attribute
is referred to as a column expression twice the names are de-duped,
thus avoiding "ambiguous column" errors. Previously, the
``.label(None)`` would need to be applied in order for the name
to be de-anonymized.

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

Fixed bug in "to_list" conversion where a single bytes object
would be turned into a list of individual characters. This would
impact among other things using the :meth:`_query.Query.get` method
on a primary key that's a bytes object.

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

Fixed regression appearing in the 1.0 series in ORM loading where the
exception raised for an expected column missing would incorrectly
be a ``NoneType`` error, rather than the expected
:class:`.NoSuchColumnError`.

.. change::
:tags: bug, mssql, oracle
:tickets: 3657

Fixed regression appearing in the 1.0 series which would cause the Oracle
and SQL Server dialects to incorrectly account for result set columns
when these dialects would wrap a SELECT in a subquery in order to
provide LIMIT/OFFSET behavior, and the original SELECT statement
referred to the same column multiple times, such as a column and
a label of that same column. This issue is related
to :ticket:`3658` in that when the error occurred, it would also
cause a ``NoneType`` error, rather than reporting that it couldn't
locate a column.

.. changelog::

:released: February 15, 2016

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

Fixed bug in :meth:`.Session.merge` where an object with a composite
primary key that has values for some but not all of the PK fields
would emit a SELECT statement leaking the internal NEVER_SET symbol
into the query, rather than detecting that this object does not have
a searchable primary key and no SELECT should be emitted.

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

Fixed bug in :func:`_expression.text` construct where a double-colon
expression would not escape properly, e.g. ``some\:\:expr``, as is most
commonly required when rendering PostgreSQL-style CAST expressions.

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

Fixed issue where the "literal_binds" flag was not propagated
for :func:`_expression.insert`, :func:`_expression.update` or
:func:`_expression.delete` constructs when compiled to string
SQL. Pull request courtesy Tim Tate.

.. change::
:tags: bug, oracle, jython
:tickets: 3621

Fixed a small issue in the Jython Oracle compiler involving the
rendering of "RETURNING" which allows this currently
unsupported/untested dialect to work rudimentarily with the 1.0 series.
Pull request courtesy Carlos Rivas.

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

Fixed issue where inadvertent use of the Python ``__contains__``
override with a column expression (e.g. by using ``'x' in col``)
would cause an endless loop in the case of an ARRAY type, as Python
defers this to ``__getitem__`` access which never raises for this
type. Overall, all use of ``__contains__`` now raises
NotImplementedError.

.. change::
:tags: bug, engine, mysql
:tickets: 2696

Revisiting :ticket:`2696`, first released in 1.0.10, which attempts to
work around Python 2's lack of exception context reporting by emitting
a warning for an exception that was interrupted by a second exception
when attempting to roll back the already-failed transaction; this
issue continues to occur for MySQL backends in conjunction with a
savepoint that gets unexpectedly lost, which then causes a
"no such savepoint" error when the rollback is attempted, obscuring
what the original condition was.

The approach has been generalized to the Core "safe
reraise" function which takes place across the ORM and Core in any
place that a transaction is being rolled back in response to an error
which occurred trying to commit, including the context managers
provided by :class:`.Session` and :class:`_engine.Connection`, and taking
place for operations such as a failure on "RELEASE SAVEPOINT".
Previously, the fix was only in place for a specific path within
the ORM flush/commit process; it now takes place for all transactional
context managers as well.

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

Fixed bug in :class:`_schema.Table` metadata construct which appeared
around the 0.9 series where adding columns to a :class:`_schema.Table`
that was unpickled would fail to correctly establish the
:class:`_schema.Column` within the 'c' collection, leading to issues in
areas such as ORM configuration. This could impact use cases such
as ``extend_existing`` and others.

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

Fixed bug where some exception re-raise scenarios would attach
the exception to itself as the "cause"; while the Python 3 interpreter
is OK with this, it could cause endless loops in iPython.

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

Fixed the syntax of the :func:`.extract` function when used on
MSSQL against a datetime value; the quotes around the keyword
are removed. Pull request courtesy Guillaume Doumenc.

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

Fixed regression since 0.9 where the 0.9 style loader options
system failed to accommodate for multiple :func:`.undefer_group`
loader options in a single query. Multiple :func:`.undefer_group`
options will now be taken into account even against the same
entity.

.. change::
:tags: bug, mssql, firebird
:tickets: 3622

Fixed 1.0 regression where the eager fetch of cursor.rowcount was
no longer called for an UPDATE or DELETE statement emitted via plain
text or via the :func:`_expression.text` construct, affecting those drivers
that erase cursor.rowcount once the cursor is closed such as SQL
Server ODBC and Firebird drivers.


.. changelog::

:released: December 22, 2015

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

An adjustment to the regular expression used to parse MySQL views,
such that we no longer assume the "ALGORITHM" keyword is present in
the reflected view source, as some users have reported this not being
present in some Amazon RDS environments.

.. change::
:tags: bug, mysql

Added new reserved words for MySQL 5.7 to the MySQL dialect,
including 'generated', 'optimizer_costs', 'stored', 'virtual'.
Pull request courtesy Hanno Schlichting.

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

Further fixes to :ticket:`3605`, pop method on :class:`.MutableDict`,
where the "default" argument was not included.

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

Fixed bug in baked loader system where the systemwide monkeypatch
for setting up baked lazy loaders would interfere with other
loader strategies that rely on lazy loading as a fallback, e.g.
joined and subquery eager loaders, leading to ``IndexError``
exceptions at mapper configuration time.

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

Fixed regression caused in 1.0.10 by the fix for :ticket:`3593` where
the check added for a polymorphic joinedload from a
poly_subclass->class->poly_baseclass connection would fail for the
scenario of class->poly_subclass->class.

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

Fixed bug where :meth:`.Session.bulk_update_mappings` and related
would not bump a version id counter when in use. The experience
here is still a little rough as the original version id is required
in the given dictionaries and there's not clean error reporting
on that yet.

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

Fixed bug in :meth:`_expression.Update.return_defaults` which would cause all
insert-default holding columns not otherwise included in the SET
clause (such as primary key cols) to get rendered into the RETURNING
even though this is an UPDATE.

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

Major fixes to the :paramref:`_orm.Mapper.eager_defaults` flag, this
flag would not be honored correctly in the case that multiple
UPDATE statements were to be emitted, either as part of a flush
or a bulk update operation. Additionally, RETURNING
would be emitted unnecessarily within update statements.

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

Fixed bug where use of the :meth:`_query.Query.select_from` method would
cause a subsequent call to the :meth:`_query.Query.with_parent` method to
fail.

.. changelog::

:released: December 11, 2015

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

Added support for the ``dict.pop()`` and ``dict.popitem()`` methods
to the :class:`.mutable.MutableDict` class.

.. change::
:tags: change, tests

The ORM and Core tutorials, which have always been in doctest format,
are now exercised within the normal unit test suite in both Python
2 and Python 3.

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

Fixed issue within the :meth:`_expression.Insert.from_select` construct whereby
the :class:`_expression.Select` construct would have its ``._raw_columns``
collection mutated in-place when compiling the :class:`_expression.Insert`
construct, when the target :class:`_schema.Table` has Python-side defaults.
The :class:`_expression.Select` construct would compile standalone with the
erroneous column present subsequent to compilation of the
:class:`_expression.Insert`, and the :class:`_expression.Insert` statement itself would
fail on a second compile attempt due to duplicate bound parameters.

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

Fixed bug in MySQL reflection where the "fractional sections portion"
of the :class:`.mysql.DATETIME`, :class:`.mysql.TIMESTAMP` and
:class:`.mysql.TIME` types would be incorrectly placed into the
``timezone`` attribute, which is unused by MySQL, instead of the
``fsp`` attribute.

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

Fixed issue where post_update on a many-to-one relationship would
fail to emit an UPDATE in the case where the attribute were set to
None and not previously loaded.

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

Fixed bug where CREATE TABLE with a no-column table, but a constraint
such as a CHECK constraint would render an erroneous comma in the
definition; this scenario can occur such as with a PostgreSQL
INHERITS table that has no columns of its own.

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


Added the error "20006: Write to the server failed" to the list
of disconnect errors for the pymssql driver, as this has been observed
to render a connection unusable.

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


Fixed issue where the "FOR UPDATE OF" PostgreSQL-specific SELECT
modifier would fail if the referred table had a schema qualifier;
PG needs the schema name to be omitted. Pull request courtesy
Diana Clarke.

.. change::
:tags: bug, postgresql


Fixed bug where some varieties of SQL expression passed to the
"where" clause of :class:`_postgresql.ExcludeConstraint` would fail
to be accepted correctly. Pull request courtesy aisch.

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


Fixed bug where in Py2K a unicode literal would not be accepted as the
string name of a class or other argument within declarative using
:func:`.backref` on :func:`_orm.relationship`. Pull request courtesy
Nils Philippsen.

.. change::
:tags: bug, mssql

A descriptive ValueError is now raised in the event that SQL server
returns an invalid date or time format from a DATE or TIME
column, rather than failing with a NoneType error. Pull request
courtesy Ed Avis.

.. change::
:tags: bug, py3k

Updates to internal getargspec() calls, some py36-related
fixture updates, and alterations to two iterators to "return" instead
of raising StopIteration, to allow tests to pass without
errors or warnings on Py3.5, Py3.6, pull requests courtesy
Jacob MacDonald, Luri de Silvio, and Phil Jones.

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

Fixed an issue in baked queries where the .get() method, used either
directly or within lazy loads, didn't consider the mapper's "get clause"
as part of the cache key, causing bound parameter mismatches if the
clause got re-generated. This clause is cached by mappers
on the fly but in highly concurrent scenarios may be generated more
than once when first accessed.

.. change::
:tags: feature, sql

Added support for parameter-ordered SET clauses in an UPDATE
statement. This feature is available by passing the
:paramref:`~.sqlalchemy.sql.expression.update.preserve_parameter_order`
flag either to the core :class:`_expression.Update` construct or alternatively
adding it to the :paramref:`.Query.update.update_args` dictionary at
the ORM-level, also passing the parameters themselves as a list of 2-tuples.
Thanks to Gorka Eguileor for implementation and tests.

.. seealso::

:ref:`tutorial_parameter_ordered_updates`

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

Fixed bug which is actually a regression that occurred between
versions 0.8.0 and 0.8.1, due :ticket:`2714`. The case where
joined eager loading needs to join out over a subclass-bound
relationship when "with_polymorphic" were also used would fail
to join from the correct entity.

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

Fixed joinedload bug which would occur when a. the query includes
limit/offset criteria that forces a subquery b. the relationship
uses "secondary" c. the primaryjoin of the relationship refers to
a column that is either not part of the primary key, or is a PK
col in a joined-inheritance subclass table that is under a different
attribute name than the parent table's primary key column d. the
query defers the columns that are present in the primaryjoin, typically
via not being included in load_only(); the necessary column(s) would
not be present in the subquery and produce invalid SQL.

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

A rare case which occurs when a :meth:`.Session.rollback` fails in the
scope of a :meth:`.Session.flush` operation that's raising an
exception, as has been observed in some MySQL SAVEPOINT cases, prevents
the original database exception from being observed when it was
emitted during flush, but only on Py2K because Py2K does not support
exception chaining; on Py3K the originating exception is chained. As
a workaround, a warning is emitted in this specific case showing at
least the string message of the original database error before we
proceed to raise the rollback-originating exception.

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

Fixed the ``.python_type`` attribute of :class:`_postgresql.INTERVAL`
to return ``datetime.timedelta`` in the same way as that of
:obj:`.types.Interval.python_type`, rather than raising
``NotImplementedError``.

.. change::
:tags: bug, mssql


Fixed issue where DDL generated for the MSSQL types DATETIME2,
TIME and DATETIMEOFFSET with a precision of "zero" would not generate
the precision field. Pull request courtesy Jacobo de Vera.


.. changelog::

:released: October 20, 2015

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

Fixed regression in 1.0 where new feature of using "executemany"
for UPDATE statements in the ORM (e.g. :ref:`feature_updatemany`)
would break on PostgreSQL and other RETURNING backends
when using server-side version generation
schemes, as the server side value is retrieved via RETURNING which
is not supported with executemany.

.. change::
:tags: feature, ext
:tickets: 3551

Added the :paramref:`.AssociationProxy.info` parameter to the
:class:`.AssociationProxy` constructor, to suit the
:attr:`.AssociationProxy.info` accessor that was added in
:ticket:`2971`. This is possible because :class:`.AssociationProxy`
is constructed explicitly, unlike a hybrid which is constructed
implicitly via the decorator syntax.

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

Fixed bug in Oracle dialect where reflection of tables and other
symbols with names quoted to force all-lower-case would not be
identified properly in reflection queries. The :class:`.quoted_name`
construct is now applied to incoming symbol names that detect as
forced into all-lower-case within the "name normalize" process.

.. change::
:tags: feature, orm

Added new method :meth:`_query.Query.one_or_none`; same as
:meth:`_query.Query.one` but returns None if no row found. Pull request
courtesy esiegerman.

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

Fixed rare TypeError which could occur when stringifying certain
kinds of internal column loader options within internal logging.

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

Fixed bug in :meth:`.Session.bulk_save_objects` where a mapped
column that had some kind of "fetch on update" value and was not
locally present in the given object would cause an AttributeError
within the operation.

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

Fixed regression in 1.0-released default-processor for multi-VALUES
insert statement, :ticket:`3288`, where the column type for the
default-holding column would not be propagated to the compiled
statement in the case where the default was being used,
leading to bind-level type handlers not being invoked.

.. change::
:tags: bug, examples


Fixed two issues in the "history_meta" example where history tracking
could encounter empty history, and where a column keyed to an alternate
attribute name would fail to track properly. Fixes courtesy
Alex Fraser.

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


Fixed 1.0 regression where the "noload" loader strategy would fail
to function for a many-to-one relationship. The loader used an
API to place "None" into the dictionary which no longer actually
writes a value; this is a side effect of :ticket:`3061`.

.. change::
:tags: bug, sybase
:tickets: 3508, 3509


Fixed two issues regarding Sybase reflection, allowing tables
without primary keys to be reflected as well as ensured that
a SQL statement involved in foreign key detection is pre-fetched up
front to avoid driver issues upon nested queries. Fixes here
courtesy Eugene Zapolsky; note that we cannot currently test
Sybase to locally verify these changes.

.. change::
:tags: bug, postgresql


An adjustment to the new PostgreSQL feature of reflecting storage
options and USING of :ticket:`3455` released in 1.0.6,
to disable the feature for PostgreSQL versions < 8.2 where the
``reloptions`` column is not provided; this allows Amazon Redshift
to again work as it is based on an 8.0.x version of PostgreSQL.
Fix courtesy Pete Hollobon.


.. changelog::