Sqlalchemy

:released: July 22, 2015

.. change::
:tags: bug, sqlite
:tickets: 3495
:versions: 1.0.8

Fixed bug in SQLite dialect where reflection of UNIQUE constraints
that included non-alphabetic characters in the names, like dots or
spaces, would not be reflected with their name.

.. change::
:tags: feature, sql
:tickets: 3418
:versions: 1.0.5

Added official support for a CTE used by the SELECT present
inside of :meth:`_expression.Insert.from_select`. This behavior worked
accidentally up until 0.9.9, when it no longer worked due to
unrelated changes as part of :ticket:`3248`. Note that this
is the rendering of the WITH clause after the INSERT, before the
SELECT; the full functionality of CTEs rendered at the top
level of INSERT, UPDATE, DELETE is a new feature targeted for a
later release.

.. change::
:tags: bug, ext
:tickets: 3408
:versions: 1.0.4

Fixed bug where when using extended attribute instrumentation system,
the correct exception would not be raised when :func:`.class_mapper`
were called with an invalid input that also happened to not
be weak referencable, such as an integer.

.. change::
:tags: bug, tests, pypy
:tickets: 3406
:versions: 1.0.4

Fixed an import that prevented "pypy setup.py test" from working
correctly.

.. change::
:tags: bug, engine
:tickets: 3375
:versions: 1.0.1

Added the string value ``"none"`` to those accepted by the
:paramref:`_pool.Pool.reset_on_return` parameter as a synonym for ``None``,
so that string values can be used for all settings, allowing
utilities like :func:`.engine_from_config` to be usable without
issue.

.. change::
:tags: bug, sql
:tickets: 3362
:versions: 1.0.0

Fixed issue where a :class:`_schema.MetaData` object that used a naming
convention would not properly work with pickle. The attribute was
skipped leading to inconsistencies and failures if the unpickled
:class:`_schema.MetaData` object were used to base additional tables
from.

.. change::
:tags: bug, postgresql
:tickets: 3354
:versions: 1.0.0

Fixed a long-standing bug where the :class:`.Enum` type as used
with the psycopg2 dialect in conjunction with non-ascii values
and ``native_enum=False`` would fail to decode return results properly.
This stemmed from when the PG :class:`_postgresql.ENUM` type used
to be a standalone type without a "non native" option.

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

:class:`_query.Query` doesn't support joins, subselects, or special
FROM clauses when using the :meth:`_query.Query.update` or
:meth:`_query.Query.delete` methods; instead of silently ignoring these
fields if methods like :meth:`_query.Query.join` or
:meth:`_query.Query.select_from` has been called, a warning is emitted.
As of 1.0.0b5 this will raise an error.

.. change::
:tags: bug, orm
:tickets: 3352
:versions: 1.0.0b5

Fixed bug where the state tracking within multiple, nested
:meth:`.Session.begin_nested` operations would fail to propagate
the "dirty" flag for an object that had been updated within
the inner savepoint, such that if the enclosing savepoint were
rolled back, the object would not be part of the state that was
expired and therefore reverted to its database state.

.. change::
:tags: bug, mysql, pymysql
:tickets: 3337
:versions: 1.0.0b4

Fixed unicode support for PyMySQL when using an "executemany"
operation with unicode parameters. SQLAlchemy now passes both
the statement as well as the bound parameters as unicode
objects, as PyMySQL generally uses string interpolation
internally to produce the final statement, and in the case of
executemany does the "encode" step only on the final statement.

.. change::
:tags: bug, py3k, mysql
:tickets: 3333
:versions: 1.0.0b2

Fixed the :class:`.mysql.BIT` type on Py3K which was not using the
``ord()`` function correctly. Pull request courtesy David Marin.

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

Fixed regression from 0.9.9 where the :func:`.as_declarative`
symbol was removed from the ``sqlalchemy.ext.declarative``
namespace.

.. change::
:tags: feature, orm
:tickets: 3320
:versions: 1.0.0b1

Added a new entry ``"entity"`` to the dictionaries returned by
:attr:`_query.Query.column_descriptions`. This refers to the primary ORM
mapped class or aliased class that is referred to by the expression.
Compared to the existing entry for ``"type"``, it will always be
a mapped entity, even if extracted from a column expression, or
None if the given expression is a pure core expression.
See also :ticket:`3403` which repaired a regression in this feature
which was unreleased in 0.9.10 but was released in the 1.0 version.


.. changelog::

:released: March 10, 2015

.. change::
:tags: feature, postgresql
:versions: 1.0.0b1

Added support for the ``CONCURRENTLY`` keyword with PostgreSQL
indexes, established using ``postgresql_concurrently``. Pull
request courtesy Iuri de Silvio.

.. seealso::

:ref:`postgresql_index_concurrently`

.. change::
:tags: bug, ext, py3k
:versions: 1.0.0b1

Fixed bug where the association proxy list class would not interpret
slices correctly under Py3K. Pull request courtesy
Gilles Dartiguelongue.

.. change::
:tags: feature, sqlite
:versions: 1.0.0b1

Added support for partial indexes (e.g. with a WHERE clause) on
SQLite. Pull request courtesy Kai Groner.

.. seealso::

:ref:`sqlite_partial_index`

.. change::
:tags: bug, orm
:tickets: 3310
:versions: 1.0.0b1

Fixed bugs in ORM object comparisons where comparison of
many-to-one ``!= None`` would fail if the source were an aliased
class, or if the query needed to apply special aliasing to the
expression due to aliased joins or polymorphic querying; also fixed
bug in the case where comparing a many-to-one to an object state
would fail if the query needed to apply special aliasing
due to aliased joins or polymorphic querying.

.. change::
:tags: bug, orm
:tickets: 3309
:versions: 1.0.0b1

Fixed bug where internal assertion would fail in the case where
an ``after_rollback()`` handler for a :class:`.Session` incorrectly
adds state to that :class:`.Session` within the handler, and the task
to warn and remove this state (established by :ticket:`2389`) attempts
to proceed.

.. change::
:tags: bug, orm
:versions: 1.0.0b1

Fixed bug where TypeError raised when :meth:`_query.Query.join` called
with unknown kw arguments would raise its own TypeError due
to broken formatting. Pull request courtesy Malthe Borch.

.. change::
:tags: bug, engine
:tickets: 3302
:versions: 1.0.0b1

Fixed bug in :class:`_engine.Connection` and pool where the
:meth:`_engine.Connection.invalidate` method, or an invalidation due
to a database disconnect, would fail if the
``isolation_level`` parameter had been used with
:meth:`_engine.Connection.execution_options`; the "finalizer" that resets
the isolation level would be called on the no longer opened connection.

.. change::
:tags: feature, orm
:tickets: 3296
:versions: 1.0.0b1

Added new parameter :paramref:`.Session.connection.execution_options`
which may be used to set up execution options on a :class:`_engine.Connection`
when it is first checked out, before the transaction has begun.
This is used to set up options such as isolation level on the
connection before the transaction starts.

.. seealso::

:ref:`session_transaction_isolation` - new documentation section
detailing best practices for setting transaction isolation with
sessions.

.. change::
:tags: bug, engine
:tickets: 3296
:versions: 1.0.0b1

A warning is emitted if the ``isolation_level`` parameter is used
with :meth:`_engine.Connection.execution_options` when a :class:`.Transaction`
is in play; DBAPIs and/or SQLAlchemy dialects such as psycopg2,
MySQLdb may implicitly rollback or commit the transaction, or
not change the setting til next transaction, so this is never safe.

.. change::
:tags: bug, orm
:tickets: 3300
:versions: 1.0.0b1

Fixed bug in lazy loading SQL construction whereby a complex
primaryjoin that referred to the same "local" column multiple
times in the "column that points to itself" style of self-referential
join would not be substituted in all cases. The logic to determine
substitutions here has been reworked to be more open-ended.

.. change::
:tags: bug, postgresql
:tickets: 2940
:versions: 1.0.0b1

Repaired support for PostgreSQL UUID types in conjunction with
the ARRAY type when using psycopg2. The psycopg2 dialect now
employs use of the psycopg2.extras.register_uuid() hook
so that UUID values are always passed to/from the DBAPI as
UUID() objects. The :paramref:`.UUID.as_uuid` flag is still
honored, except with psycopg2 we need to convert returned
UUID objects back into strings when this is disabled.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1

Added support for the :class:`postgresql.JSONB` datatype when
using psycopg2 2.5.4 or greater, which features native conversion
of JSONB data so that SQLAlchemy's converters must be disabled;
additionally, the newly added psycopg2 extension
``extras.register_default_jsonb`` is used to establish a JSON
deserializer passed to the dialect via the ``json_deserializer``
argument. Also repaired the PostgreSQL integration tests which
weren't actually round-tripping the JSONB type as opposed to the
JSON type. Pull request courtesy Mateusz Susik.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1

Repaired the use of the "array_oid" flag when registering the
HSTORE type with older psycopg2 versions < 2.4.3, which does not
support this flag, as well as use of the native json serializer
hook "register_default_json" with user-defined ``json_deserializer``
on psycopg2 versions < 2.5, which does not include native json.

.. change::
:tags: bug, schema
:tickets: 3298, 1765

Fixed bug in 0.9's foreign key setup system, such that
the logic used to link a :class:`_schema.ForeignKey` to its parent could fail
when the foreign key used "link_to_name=True" in conjunction with
a target :class:`_schema.Table` that would not receive its parent column until
later, such as within a reflection + "useexisting" scenario,
if the target column in fact had a key value different from its name,
as would occur in reflection if column reflect events were used to
alter the .key of reflected :class:`_schema.Column` objects so that the
link_to_name becomes significant. Also repaired support for column
type via FK transmission in a similar way when target columns had a
different key and were referenced using link_to_name.

.. change::
:tags: feature, engine
:versions: 1.0.0b1

Added new user-space accessors for viewing transaction isolation
levels; :meth:`_engine.Connection.get_isolation_level`,
:attr:`_engine.Connection.default_isolation_level`.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3174

Fixed bug where PostgreSQL dialect would fail to render an
expression in an :class:`.Index` that did not correspond directly
to a table-bound column; typically when a :func:`_expression.text` construct
was one of the expressions within the index; or could misinterpret the
list of expressions if one or more of them were such an expression.

.. change::
:tags: bug, orm
:versions: 1.0.0b1
:tickets: 3287

The "wildcard" loader options, in particular the one set up by
the :func:`_orm.load_only` option to cover all attributes not
explicitly mentioned, now takes into account the superclasses
of a given entity, if that entity is mapped with inheritance mapping,
so that attribute names within the superclasses are also omitted
from the load. Additionally, the polymorphic discriminator column
is unconditionally included in the list, just in the same way that
primary key columns are, so that even with load_only() set up,
polymorphic loading of subtypes continues to function correctly.

.. change::
:tags: bug, sql
:versions: 1.0.0b1

Added the ``native_enum`` flag to the ``__repr__()`` output
of :class:`.Enum`, which is mostly important when using it with
Alembic autogenerate. Pull request courtesy Dimitris Theodorou.

.. change::
:tags: bug, orm, pypy
:versions: 1.0.0b1
:tickets: 3285

Fixed bug where if an exception were thrown at the start of a
:class:`_query.Query` before it fetched results, particularly when
row processors can't be formed, the cursor would stay open with
results pending and not actually be closed. This is typically only
an issue on an interpreter like PyPy where the cursor isn't
immediately GC'ed, and can in some circumstances lead to transactions/
locks being open longer than is desirable.

.. change::
:tags: change, mysql
:versions: 1.0.0b1
:tickets: 3275

The ``gaerdbms`` dialect is no longer necessary, and emits a
deprecation warning. Google now recommends using the MySQLdb
dialect directly.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3278

Fixed bug where using a :class:`.TypeDecorator` that implemented
a type that was also a :class:`.TypeDecorator` would fail with
Python's "Cannot create a consistent method resolution order (MRO)"
error, when any kind of SQL comparison expression were used against
an object using this type.

.. change::
:tags: bug, mysql
:versions: 1.0.0b1
:tickets: 3274

Added a version check to the MySQLdb dialect surrounding the
check for 'utf8_bin' collation, as this fails on MySQL server < 5.0.

.. change::
:tags: feature, orm
:versions: 1.0.0b1

Added new method :meth:`.Session.invalidate`, functions similarly
to :meth:`.Session.close`, except also calls
:meth:`_engine.Connection.invalidate`
on all connections, guaranteeing that they will not be returned to
the connection pool. This is useful in situations e.g. dealing
with gevent timeouts when it is not safe to use the connection further,
even for rollbacks.

.. change::
:tags: bug, examples
:versions: 1.0.0b1

Updated the :ref:`examples_versioned_history` example such that
mapped columns are re-mapped to
match column names as well as grouping of columns; in particular,
this allows columns that are explicitly grouped in a same-column-named
joined inheritance scenario to be mapped in the same way in the
history mappings, avoiding warnings added in the 0.9 series
regarding this pattern and allowing the same view of attribute
keys.

.. change::
:tags: bug, examples
:versions: 1.0.0b1

Fixed a bug in the examples/generic_associations/discriminator_on_association.py
example, where the subclasses of AddressAssociation were not being
mapped as "single table inheritance", leading to problems when trying
to use the mappings further.

.. change::
:tags: bug, orm
:versions: 1.0.0b1
:tickets: 3251

Fixed a leak which would occur in the unsupported and highly
non-recommended use case of replacing a relationship on a fixed
mapped class many times, referring to an arbitrarily growing number of
target mappers. A warning is emitted when the old relationship is
replaced, however if the mapping were already used for querying, the
old relationship would still be referenced within some registries.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3248

Fixed issue where the columns from a SELECT embedded in an
INSERT, either through the values clause or as a "from select",
would pollute the column types used in the result set produced by
the RETURNING clause when columns from both statements shared the
same name, leading to potential errors or mis-adaptation when
retrieving the returning rows.

.. change::
:tags: bug, orm, sqlite
:versions: 1.0.0b1
:tickets: 3241

Fixed bug regarding expression mutations which could express
itself as a "Could not locate column" error when using
:class:`_query.Query` to select from multiple, anonymous column
entities when querying against SQLite, as a side effect of the
"join rewriting" feature used by the SQLite dialect.

.. change::
:tags: feature, sqlite
:versions: 1.0.0b1

Added a new SQLite backend for the SQLCipher backend. This backend
provides for encrypted SQLite databases using the pysqlcipher Python
driver, which is very similar to the pysqlite driver.

.. seealso::

:mod:`~sqlalchemy.dialects.sqlite.pysqlcipher`

.. change::
:tags: bug, orm
:tickets: 3232
:versions: 1.0.0b1

Fixed bug where the ON clause for :meth:`_query.Query.join`,
and :meth:`_query.Query.outerjoin` to a single-inheritance subclass
using ``of_type()`` would not render the "single table criteria" in
the ON clause if the ``from_joinpoint=True`` flag were set.

.. changelog::

:released: October 13, 2014

.. change::
:tags: bug, mysql, mysqlconnector
:versions: 1.0.0b1

Mysqlconnector as of version 2.0, probably as a side effect of
the python 3 merge, now does not expect percent signs (e.g.
as used as the modulus operator and others) to be doubled,
even when using the "pyformat" bound parameter format (this
change is not documented by Mysqlconnector). The dialect now
checks for py2k and for mysqlconnector less than version 2.0
when detecting if the modulus operator should be rendered as
``%%`` or ``%``.

.. change::
:tags: bug, mysql, mysqlconnector
:versions: 1.0.0b1

Unicode SQL is now passed for MySQLconnector version 2.0 and above;
for Py2k and MySQL < 2.0, strings are encoded.


.. change::
:tags: bug, oracle
:versions: 1.0.0b1
:tickets: 2138

Fixed long-standing bug in Oracle dialect where bound parameter
names that started with numbers would not be quoted, as Oracle
doesn't like numerics in bound parameter names.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3195

Fixed bug where a fair number of SQL elements within
the sql package would fail to ``__repr__()`` successfully,
due to a missing ``description`` attribute that would then invoke
a recursion overflow when an internal AttributeError would then
re-invoke ``__repr__()``.

.. change::
:tags: bug, declarative, orm
:versions: 1.0.0b1
:tickets: 3185

Fixed "'NoneType' object has no attribute 'concrete'" error
when using :class:`.AbstractConcreteBase` in conjunction with
a subclass that declares ``__abstract__``.

.. change::
:tags: bug, engine
:versions: 1.0.0b1
:tickets: 3200

The execution options passed to an :class:`_engine.Engine` either via
:paramref:`_sa.create_engine.execution_options` or
:meth:`_engine.Engine.update_execution_options` are not passed to the
special :class:`_engine.Connection` used to initialize the dialect
within the "first connect" event; dialects will usually
perform their own queries in this phase, and none of the
current available options should be applied here. In
particular, the "autocommit" option was causing an attempt to
autocommit within this initial connect which would fail with
an AttributeError due to the non-standard state of the
:class:`_engine.Connection`.

.. change::
:tags: bug, sqlite
:versions: 1.0.0b1
:tickets: 3211

When selecting from a UNION using an attached database file,
the pysqlite driver reports column names in cursor.description
as 'dbname.tablename.colname', instead of 'tablename.colname' as
it normally does for a UNION (note that it's supposed to just be
'colname' for both, but we work around it). The column translation
logic here has been adjusted to retrieve the rightmost token, rather
than the second token, so it works in both cases. Workaround
courtesy Tony Roberts.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3021

A revisit to this issue first patched in 0.9.5, apparently
psycopg2's ``.closed`` accessor is not as reliable as we assumed,
so we have added an explicit check for the exception messages
"SSL SYSCALL error: Bad file descriptor" and
"SSL SYSCALL error: EOF detected" when detecting an
is-disconnect scenario. We will continue to consult psycopg2's
connection.closed as a first check.

.. change::
:tags: bug, orm, engine
:versions: 1.0.0b1
:tickets: 3197

Fixed bug that affected generally the same classes of event
as that of :ticket:`3199`, when the ``named=True`` parameter
would be used. Some events would fail to register, and others
would not invoke the event arguments correctly, generally in the
case of when an event was "wrapped" for adaption in some other way.
The "named" mechanics have been rearranged to not interfere with
the argument signature expected by internal wrapper functions.

.. change::
:tags: bug, declarative
:versions: 1.0.0b1
:tickets: 3208

Fixed an unlikely race condition observed in some exotic end-user
setups, where the attempt to check for "duplicate class name" in
declarative would hit upon a not-totally-cleaned-up weak reference
related to some other class being removed; the check here now ensures
the weakref still references an object before calling upon it further.

.. change::
:tags: bug, orm
:versions: 1.0.0b1
:tickets: 3199

Fixed bug that affected many classes of event, particularly
ORM events but also engine events, where the usual logic of
"de duplicating" a redundant call to :func:`.event.listen`
with the same arguments would fail, for those events where the
listener function is wrapped. An assertion would be hit within
registry.py. This assertion has now been integrated into the
deduplication check, with the added bonus of a simpler means
of checking deduplication across the board.

.. change::
:tags: bug, mssql
:versions: 1.0.0b1
:tickets: 3151

Fixed the version string detection in the pymssql dialect to
work with Microsoft SQL Azure, which changes the word "SQL Server"
to "SQL Azure".

.. change::
:tags: bug, orm
:versions: 1.0.0b1
:tickets: 3194

Fixed warning that would emit when a complex self-referential
primaryjoin contained functions, while at the same time remote_side
was specified; the warning would suggest setting "remote side".
It now only emits if remote_side isn't present.

.. change::
:tags: bug, ext
:versions: 1.0.0b1
:tickets: 3191

Fixed bug in ordering list where the order of items would be
thrown off during a collection replace event, if the
reorder_on_append flag were set to True. The fix ensures that the
ordering list only impacts the list that is explicitly associated
with the object.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3180

An adjustment to table/index reflection such that if an index
reports a column that isn't found to be present in the table,
a warning is emitted and the column is skipped. This can occur
for some special system column situations as has been observed
with Oracle.

.. change::
:tags: bug, ext
:versions: 1.0.0b1

Fixed bug where :class:`.ext.mutable.MutableDict`
failed to implement the ``update()`` dictionary method, thus
not catching changes. Pull request courtesy Matt Chisholm.

.. change::
:tags: bug, ext
:versions: 1.0.0b1

Fixed bug where a custom subclass of :class:`.ext.mutable.MutableDict`
would not show up in a "coerce" operation, and would instead
return a plain :class:`.ext.mutable.MutableDict`. Pull request
courtesy Matt Chisholm.

.. change::
:tags: bug, pool
:versions: 1.0.0b1
:tickets: 3168

Fixed bug in connection pool logging where the "connection checked out"
debug logging message would not emit if the logging were set up using
``logging.setLevel()``, rather than using the ``echo_pool`` flag.
Tests to assert this logging have been added. This is a
regression that was introduced in 0.9.0.

.. change::
:tags: feature, postgresql, pg8000
:versions: 1.0.0b1

Support is added for "sane multi row count" with the pg8000 driver,
which applies mostly to when using versioning with the ORM.
The feature is version-detected based on pg8000 1.9.14 or greater
in use. Pull request courtesy Tony Locke.

.. change::
:tags: bug, engine
:versions: 1.0.0b1
:tickets: 3165

The string keys that are used to determine the columns impacted
for an INSERT or UPDATE are now sorted when they contribute towards
the "compiled cache" cache key. These keys were previously not
deterministically ordered, meaning the same statement could be
cached multiple times on equivalent keys, costing both in terms of
memory as well as performance.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3159

Fixed bug where PostgreSQL JSON type was not able to persist or
otherwise render a SQL NULL column value, rather than a JSON-encoded
``'null'``. To support this case, changes are as follows:

* The value :func:`.null` can now be specified, which will always
result in a NULL value resulting in the statement.

* A new parameter :paramref:`_types.JSON.none_as_null` is added, which
when True indicates that the Python ``None`` value should be
persisted as SQL NULL, rather than JSON-encoded ``'null'``.

Retrieval of NULL as None is also repaired for DBAPIs other than
psycopg2, namely pg8000.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3154

Fixed bug in CTE where ``literal_binds`` compiler argument would not
be always be correctly propagated when one CTE referred to another
aliased CTE in a statement.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3075

The exception wrapping system for DBAPI errors can now accommodate
non-standard DBAPI exceptions, such as the psycopg2
TransactionRollbackError. These exceptions will now be raised
using the closest available subclass in ``sqlalchemy.exc``, in the
case of TransactionRollbackError, ``sqlalchemy.exc.OperationalError``.

.. change::
:tags: bug, sql
:versions: 1.0.0b1
:tickets: 3144, 3067

Fixed 0.9.7 regression caused by :ticket:`3067` in conjunction with
a mis-named unit test such that so-called "schema" types like
:class:`.Boolean` and :class:`.Enum` could no longer be pickled.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3141

Fixed bug in :class:`_postgresql.array` object where comparison
to a plain Python list would fail to use the correct array constructor.
Pull request courtesy Andrew.

.. change::
:tags: bug, postgresql
:versions: 1.0.0b1
:tickets: 3137

Added a supported :meth:`.FunctionElement.alias` method to functions,
e.g. the ``func`` construct. Previously, behavior for this method
was undefined. The current behavior mimics that of pre-0.9.4,
which is that the function is turned into a single-column FROM
clause with the given alias name, where the column itself is
anonymously named.

.. changelog::

:released: July 22, 2014

.. change::
:tags: bug, postgresql, pg8000
:tickets: 3134
:versions: 1.0.0b1

Fixed bug introduced in 0.9.5 by new pg8000 isolation level feature
where engine-level isolation level parameter would raise an error
on connect.

.. change::
:tags: bug, oracle, tests
:tickets: 3128
:versions: 1.0.0b1

Fixed bug in oracle dialect test suite where in one test,
'username' was assumed to be in the database URL, even though
this might not be the case.

.. change::
:tags: bug, orm, eagerloading
:tickets: 3131
:versions: 1.0.0b1

Fixed a regression caused by :ticket:`2976` released in 0.9.4 where
the "outer join" propagation along a chain of joined eager loads
would incorrectly convert an "inner join" along a sibling join path
into an outer join as well, when only descendant paths should be
receiving the "outer join" propagation; additionally, fixed related
issue where "nested" join propagation would take place inappropriately
between two sibling join paths.

.. change::
:tags: bug, sqlite
:tickets: 3130
:versions: 1.0.0b1

Fixed a SQLite join rewriting issue where a subquery that is embedded
as a scalar subquery such as within an IN would receive inappropriate
substitutions from the enclosing query, if the same table were present
inside the subquery as were in the enclosing query such as in a
joined inheritance scenario.

.. change::
:tags: bug, sql
:tickets: 3067
:versions: 1.0.0b1

Fix bug in naming convention feature where using a check
constraint convention that includes ``constraint_name`` would
then force all :class:`.Boolean` and :class:`.Enum` types to
require names as well, as these implicitly create a
constraint, even if the ultimate target backend were one that does
not require generation of the constraint such as PostgreSQL.
The mechanics of naming conventions for these particular
constraints has been reorganized such that the naming
determination is done at DDL compile time, rather than at
constraint/table construction time.

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

Fixed a regression from 0.9.5 caused by :ticket:`3025` where the
query used to determine "default schema" is invalid in SQL Server 2000.
For SQL Server 2000 we go back to defaulting to the "schema name"
parameter of the dialect, which is configurable but defaults
to 'dbo'.

.. change::
:tags: bug, orm
:tickets: 3083, 2736
:versions: 1.0.0b1

Fixed a regression from 0.9.0 due to :ticket:`2736` where the
:meth:`_query.Query.select_from` method no longer set up the "from
entity" of the :class:`_query.Query` object correctly, so that
subsequent :meth:`_query.Query.filter_by` or :meth:`_query.Query.join`
calls would fail to check the appropriate "from" entity when
searching for attributes by string name.

.. change::
:tags: bug, sql
:tickets: 3090
:versions: 1.0.0b1

Fixed bug in common table expressions whereby positional bound
parameters could be expressed in the wrong final order
when CTEs were nested in certain ways.

.. change::
:tags: bug, sql
:tickets: 3069
:versions: 1.0.0b1

Fixed bug where multi-valued :class:`_expression.Insert` construct would fail
to check subsequent values entries beyond the first one given
for literal SQL expressions.

.. change::
:tags: bug, sql
:tickets: 3123
:versions: 1.0.0b1

Added a "str()" step to the dialect_kwargs iteration for
Python version < 2.6.5, working around the
"no unicode keyword arg" bug as these args are passed along as
keyword args within some reflection processes.

.. change::
:tags: bug, sql
:tickets: 3122
:versions: 1.0.0b1

The :meth:`.TypeEngine.with_variant` method will now accept a
type class as an argument which is internally converted to an
instance, using the same convention long established by other
constructs such as :class:`_schema.Column`.

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

The "evaluator" for query.update()/delete() won't work with multi-table
updates, and needs to be set to `synchronize_session=False` or
`synchronize_session='fetch'`; a warning is now emitted. In
1.0 this will be promoted to a full exception.

.. change::
:tags: bug, tests
:versions: 1.0.0b1

Fixed bug where "python setup.py test" wasn't calling into
distutils appropriately, and errors would be emitted at the end
of the test suite.

.. change::
:tags: feature, postgresql
:versions: 1.0.0b1
:tickets: 3078

Added kw argument ``postgresql_regconfig`` to the
:meth:`.ColumnOperators.match` operator, allows the "reg config" argument
to be specified to the ``to_tsquery()`` function emitted.
Pull request courtesy Jonathan Vanasco.

.. change::
:tags: feature, postgresql
:versions: 1.0.0b1

Added support for PostgreSQL JSONB via :class:`_postgresql.JSONB`. Pull request
courtesy Damian Dimmich.

.. change::
:tags: feature, mssql
:versions: 1.0.0b1

Enabled "multivalues insert" for SQL Server 2008. Pull request
courtesy Albert Cervin. Also expanded the checks for "IDENTITY INSERT"
mode to include when the identity key is present in the
VALUEs clause of the statement.

.. change::
:tags: feature, engine
:tickets: 3076
:versions: 1.0.0b1

Added new event :meth:`_events.ConnectionEvents.handle_error`, a more
fully featured and comprehensive replacement for
:meth:`_events.ConnectionEvents.dbapi_error`.

.. change::
:tags: bug, orm
:tickets: 3108
:versions: 1.0.0b1

Fixed bug where items that were persisted, deleted, or had a
primary key change within a savepoint block would not
participate in being restored to their former state (not in
session, in session, previous PK) after the outer transaction
were rolled back.

.. change::
:tags: bug, orm
:tickets: 3106
:versions: 1.0.0b1

Fixed bug in subquery eager loading in conjunction with
:func:`.with_polymorphic`, the targeting of entities and columns
in the subquery load has been made more accurate with respect
to this type of entity and others.

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

Fixed bug involving dynamic attributes, that was again a regression
of :ticket:`3060` from version 0.9.5. A self-referential relationship
with lazy='dynamic' would raise a TypeError within a flush operation.

.. change::
:tags: bug, declarative
:tickets: 3097
:versions: 1.0.0b1

Fixed bug when the declarative ``__abstract__`` flag was not being
distinguished for when it was actually the value ``False``.
The ``__abstract__`` flag needs to actually evaluate to a True
value at the level being tested.

.. changelog::

:released: June 23, 2014

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

Reverted the change for :ticket:`3060` - this is a unit of work
fix that is updated more comprehensively in 1.0 via :ticket:`3061`.
The fix in :ticket:`3060` unfortunately produces a new issue whereby
an eager load of a many-to-one attribute can produce an event
that is interpreted into an attribute change.

.. changelog::

:released: June 23, 2014

.. change::
:tags: bug, orm
:tickets: 3042
:versions: 1.0.0b1

Additional checks have been added for the case where an inheriting
mapper is implicitly combining one of its column-based attributes
with that of the parent, where those columns normally don't necessarily
share the same value. This is an extension of an existing check that
was added via :ticket:`1892`; however this new check emits only a
warning, instead of an exception, to allow for applications that may
be relying upon the existing behavior.

.. seealso::

:ref:`faq_combining_columns`

.. change::
:tags: bug, sql
:tickets: 3023
:versions: 1.0.0b1

The :paramref:`_schema.Column.nullable` flag is implicitly set to ``False``
when that :class:`_schema.Column` is referred to in an explicit
:class:`.PrimaryKeyConstraint` for that table. This behavior now
matches that of when the :class:`_schema.Column` itself has the
:paramref:`_schema.Column.primary_key` flag set to ``True``, which is
intended to be an exactly equivalent case.

.. change::
:tags: enhancement, postgresql
:tickets: 3002
:versions: 1.0.0b1

Added a new type :class:`_postgresql.OID` to the PostgreSQL dialect.
While "oid" is generally a private type within PG that is not exposed
in modern versions, there are some PG use cases such as large object
support where these types might be exposed, as well as within some
user-reported schema reflection use cases.

.. change::
:tags: bug, orm
:tickets: 3080
:versions: 1.0.0b1

Modified the behavior of :func:`_orm.load_only` such that primary key
columns are always added to the list of columns to be "undeferred";
otherwise, the ORM can't load the row's identity. Apparently,
one can defer the mapped primary keys and the ORM will fail, that
hasn't been changed. But as load_only is essentially saying
"defer all but X", it's more critical that PK cols not be part of this
deferral.

.. change::
:tags: feature, examples
:versions: 1.0.0b1

Added a new example illustrating materialized paths, using the
latest relationship features. Example courtesy Jack Zhou.

.. change::
:tags: bug, testsuite
:versions: 1.0.0b1

In public test suite, changed to use of ``String(40)`` from
less-supported ``Text`` in ``StringTest.test_literal_backslashes``.
Pullreq courtesy Jan.

.. change::
:tags: bug, engine
:versions: 1.0.0b1
:tickets: 3063

Fixed bug which would occur if a DBAPI exception
occurs when the engine first connects and does its initial checks,
and the exception is not a disconnect exception, yet the cursor
raises an error when we try to close it. In this case the real
exception would be quashed as we tried to log the cursor close
exception via the connection pool and failed, as we were trying
to access the pool's logger in a way that is inappropriate
in this very specific scenario.

.. change::
:tags: feature, postgresql
:versions: 1.0.0b1

Added support for AUTOCOMMIT isolation level when using the pg8000
DBAPI. Pull request courtesy Tony Locke.

.. change::
:tags: bug, postgresql
:tickets: 3021
:versions: 1.0.0b1

The psycopg2 ``.closed`` accessor is now consulted when determining
if an exception is a "disconnect" error; ideally, this should remove
the need for any other inspection of the exception message to detect
disconnect, however we will leave those existing messages in place
as a fallback. This should be able to handle newer cases like
"SSL EOF" conditions. Pull request courtesy Dirk Mueller.

.. change::
:tags: bug, orm
:tickets: 3060
:versions: 1.0.0b1

Fixed a few edge cases which arise in the so-called "row switch"
scenario, where an INSERT/DELETE can be turned into an UPDATE.
In this situation, a many-to-one relationship set to None, or
in some cases a scalar attribute set to None, may not be detected
as a net change in value, and therefore the UPDATE would not reset
what was on the previous row. This is due to some as-yet
unresolved side effects of the way attribute history works in terms
of implicitly assuming None isn't really a "change" for a previously
un-set attribute. See also :ticket:`3061`.

.. note::

This change has been **REVERTED** in 0.9.6. The full fix
will be in version 1.0 of SQLAlchemy.


.. change::
:tags: bug, orm
:versions: 1.0.0b1

Related to :ticket:`3060`, an adjustment has been made to the unit
of work such that loading for related many-to-one objects is slightly
more aggressive, in the case of a graph of self-referential objects
that are to be deleted; the load of related objects is to help
determine the correct order for deletion if passive_deletes is
not set.

.. change::
:tags: bug, orm
:tickets: 3057
:versions: 1.0.0b1

Fixed bug in SQLite join rewriting where anonymized column names
due to repeats would not correctly be rewritten in subqueries.
This would affect SELECT queries with any kind of subquery + join.

.. change::
:tags: bug, sql
:tickets: 3012
:versions: 1.0.0b1

Fixed bug where the :meth:`.Operators.__and__`,
:meth:`.Operators.__or__` and :meth:`.Operators.__invert__`
operator overload methods could not be overridden within a custom
:class:`.TypeEngine.Comparator` implementation.

.. change::
:tags: feature, postgresql
:tickets: 2785
:versions: 1.0.0b1

Added a new flag :paramref:`_types.ARRAY.zero_indexes` to the PostgreSQL
:class:`_types.ARRAY` type. When set to ``True``, a value of one will be
added to all array index values before passing to the database, allowing
better interoperability between Python style zero-based indexes and
PostgreSQL one-based indexes. Pull request courtesy Alexey Terentev.

.. change::
:tags: bug, engine
:tickets: 3043
:versions: 1.0.0b1

Fixed some "double invalidate" situations were detected where
a connection invalidation could occur within an already critical section
like a connection.close(); ultimately, these conditions are caused
by the change in :ticket:`2907`, in that the "reset on return" feature
calls out to the Connection/Transaction in order to handle it, where
"disconnect detection" might be caught. However, it's possible that
the more recent change in :ticket:`2985` made it more likely for this
to be seen as the "connection invalidate" operation is much quicker,
as the issue is more reproducible on 0.9.4 than 0.9.3.

Checks are now added within any section that
an invalidate might occur to halt further disallowed operations
on the invalidated connection. This includes two fixes both at the
engine level and at the pool level. While the issue was observed
with highly concurrent gevent cases, it could in theory occur in
any kind of scenario where a disconnect occurs within the connection
close operation.

.. change::
:tags: feature, orm
:tickets: 3029
:versions: 1.0.0b1

The "primaryjoin" model has been stretched a bit further to allow
a join condition that is strictly from a single column to itself,
translated through some kind of SQL function or expression. This
is kind of experimental, but the first proof of concept is a
"materialized path" join condition where a path string is compared
to itself using "like". The :meth:`.ColumnOperators.like` operator has
also been added to the list of valid operators to use in a primaryjoin
condition.

.. change::
:tags: feature, sql
:tickets: 3028
:versions: 1.0.0b1

Liberalized the contract for :class:`.Index` a bit in that you can
specify a :func:`_expression.text` expression as the target; the index no longer
needs to have a table-bound column present if the index is to be
manually added to the table, either via inline declaration or via
:meth:`_schema.Table.append_constraint`.

.. change::
:tags: bug, firebird
:tickets: 3038

Fixed bug where the combination of "limit" rendering as
"SELECT FIRST n ROWS" using a bound parameter (only firebird has both),
combined with column-level subqueries
which also feature "limit" as well as "positional" bound parameters
(e.g. qmark style) would erroneously assign the subquery-level positions
before that of the enclosing SELECT, thus returning parameters which
are out of order.

.. change::
:tags: bug, mssql
:tickets: 3025
:versions: 1.0.0b1

Revised the query used to determine the current default schema name
to use the ``database_principal_id()`` function in conjunction with
the ``sys.database_principals`` view so that we can determine
the default schema independently of the type of login in progress
(e.g., SQL Server, Windows, etc).

.. change::
:tags: bug, sql
:tickets: 3024
:versions: 1.0.0b1

Fixed bug in new :meth:`.DialectKWArgs.argument_for` method where
adding an argument for a construct not previously included for any
special arguments would fail.

.. change::
:tags: bug, py3k, tests
:tickets: 2830
:versions: 1.0.0b1

Corrected for some deprecation warnings involving the ``imp``
module and Python 3.3 or greater, when running tests. Pull
request courtesy Matt Chisholm.

.. change::
:tags: bug, sql
:tickets: 3020, 1068
:versions: 1.0.0b1

Fixed regression introduced in 0.9 where new "ORDER BY <labelname>"
feature from :ticket:`1068` would not apply quoting rules to the
label name as rendered in the ORDER BY.

.. change::
:tags: feature, orm
:tickets: 3017
:versions: 1.0.0b1

Added new utility function :func:`.make_transient_to_detached` which can
be used to manufacture objects that behave as though they were loaded
from a session, then detached. Attributes that aren't present
are marked as expired, and the object can be added to a Session
where it will act like a persistent one.

.. change::
:tags: bug, sql
:versions: 1.0.0b1

Restored the import for :class:`.Function` to the ``sqlalchemy.sql.expression``
import namespace, which was removed at the beginning of 0.9.

.. change::
:tags: bug, orm, sql
:tickets: 3013
:versions: 1.0.0b1

Fixes to the newly enhanced boolean coercion in :ticket:`2804` where
the new rules for "where" and "having" wouldn't take effect for the
"whereclause" and "having" kw arguments of the :func:`_expression.select` construct,
which is also what :class:`_query.Query` uses so wasn't working in the
ORM either.

.. change::
:tags: feature, sql
:tickets: 2990
:versions: 1.0.0b1

Added new flag :paramref:`.expression.between.symmetric`, when set to True
renders "BETWEEN SYMMETRIC". Also added a new negation operator
"notbetween_op", which now allows an expression like ``~col.between(x, y)``
to render as "col NOT BETWEEN x AND y", rather than a parenthesized NOT
string.

.. changelog::