Sqlalchemy

:released: December 16, 2019

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

Fixed bug where "distinct" keyword passed to :func:`_expression.select` would not
treat a string value as a "label reference" in the same way that the
:meth:`_expression.select.distinct` does; it would instead raise unconditionally. This
keyword argument and the others passed to :func:`_expression.select` will ultimately
be deprecated for SQLAlchemy 2.0.


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

Fixed issue involving ``lazy="raise"`` strategy where an ORM delete of an
object would raise for a simple "use-get" style many-to-one relationship
that had lazy="raise" configured. This is inconsistent vs. the change
introduced in 1.3 as part of :ticket:`4353`, where it was established that
a history operation that does not expect emit SQL should bypass the
``lazy="raise"`` check, and instead effectively treat it as
``lazy="raise_on_sql"`` for this case. The fix adjusts the lazy loader
strategy to not raise for the case where the lazy load was instructed that
it should not emit SQL if the object were not present.

.. change::
:tags: bug, sql

Changed the text of the exception for "Can't resolve label reference" to
include other kinds of label coercions, namely that "DISTINCT" is also in
this category under the PostgreSQL dialect.


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

Fixed regression introduced in 1.3.0 related to the association proxy
refactor in :ticket:`4351` that prevented :func:`.composite` attributes
from working in terms of an association proxy that references them.

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

Repaired support for the :class:`_mssql.DATETIMEOFFSET` datatype on PyODBC,
by adding PyODBC-level result handlers as it does not include native
support for this datatype. This includes usage of the Python 3 "timezone"
tzinfo subclass in order to set up a timezone, which on Python 2 makes
use of a minimal backport of "timezone" in sqlalchemy.util.


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

Setting persistence-related flags on :func:`_orm.relationship` while also
setting viewonly=True will now emit a regular warning, as these flags do
not make sense for a viewonly=True relationship. In particular, the
"cascade" settings have their own warning that is generated based on the
individual values, such as "delete, delete-orphan", that should not apply
to a viewonly relationship. Note however that in the case of "cascade",
these settings are still erroneously taking effect even though the
relationship is set up as "viewonly". In 1.4, all persistence-related
cascade settings will be disallowed on a viewonly=True relationship in
order to resolve this issue.

.. change::
:tags: bug, sqlite
:tickets: 5014

Fixed issue to workaround SQLite's behavior of assigning "numeric" affinity
to JSON datatypes, first described at :ref:`change_3850`, which returns
scalar numeric JSON values as a number and not as a string that can be JSON
deserialized. The SQLite-specific JSON deserializer now gracefully
degrades for this case as an exception and bypasses deserialization for
single numeric values, as from a JSON perspective they are already
deserialized.



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

Fixed issue where when assigning a collection to itself as a slice, the
mutation operation would fail as it would first erase the assigned
collection inadvertently. As an assignment that does not change the
contents should not generate events, the operation is now a no-op. Note
that the fix only applies to Python 3; in Python 2, the ``__setitem__``
hook isn't called in this case; ``__setslice__`` is used instead which
recreates the list item-by-item in all cases.

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

Fixed issue where by if the "begin" of a transaction failed at the Core
engine/connection level, such as due to network error or database is locked
for some transactional recipes, within the context of the :class:`.Session`
procuring that connection from the connection pool and then immediately
returning it, the ORM :class:`.Session` would not close the connection
despite this connection not being stored within the state of that
:class:`.Session`. This would lead to the connection being cleaned out by
the connection pool weakref handler within garbage collection which is an
unpreferred codepath that in some special configurations can emit errors in
standard error.

.. changelog::

:released: November 11, 2019

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

Fixed issue in MSSQL dialect where an expression-based OFFSET value in a
SELECT would be rejected, even though the dialect can render this
expression inside of a ROW NUMBER-oriented LIMIT/OFFSET construct.


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

Added accessor :meth:`_query.Query.is_single_entity` to :class:`_query.Query`, which
will indicate if the results returned by this :class:`_query.Query` will be a
list of ORM entities, or a tuple of entities or column expressions.
SQLAlchemy hopes to improve upon the behavior of single entity / tuples in
future releases such that the behavior would be explicit up front, however
this attribute should be helpful with the current behavior. Pull request
courtesy Patrick Hayes.

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

Added "Connection was killed" message interpreted from the base
pymysql.Error class in order to detect closed connection, based on reports
that this message is arriving via a pymysql.InternalError() object which
indicates pymysql is not handling it correctly.

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

The :paramref:`_orm.relationship.omit_join` flag was not intended to be
manually set to True, and will now emit a warning when this occurs. The
omit_join optimization is detected automatically, and the ``omit_join``
flag was only intended to disable the optimization in the hypothetical case
that the optimization may have interfered with correct results, which has
not been observed with the modern version of this feature. Setting the
flag to True when it is not automatically detected may cause the selectin
load feature to not work correctly when a non-default primary join
condition is in use.


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

A warning is emitted if a primary key value is passed to :meth:`_query.Query.get`
that consists of None for all primary key column positions. Previously,
passing a single None outside of a tuple would raise a ``TypeError`` and
passing a composite None (tuple of None values) would silently pass
through. The fix now coerces the single None into a tuple where it is
handled consistently with the other None conditions. Thanks to Lev
Izraelit for the help with this.


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

The :class:`.BakedQuery` will not cache a query that was modified by a
:meth:`.QueryEvents.before_compile` event, so that compilation hooks that
may be applying ad-hoc modifications to queries will take effect on each
run. In particular this is helpful for events that modify queries used in
lazy loading as well as eager loading such as "select in" loading. In
order to re-enable caching for a query modified by this event, a new
flag ``bake_ok`` is added; see :ref:`baked_with_before_compile` for
details.

A longer term plan to provide a new form of SQL caching should solve this
kind of issue more comprehensively.

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

Fixed test failures which would occur with newer SQLite as of version 3.30
or greater, due to their addition of nulls ordering syntax as well as new
restrictions on aggregate functions. Pull request courtesy Nils Philippsen.



.. change::
:tags: bug, installation, windows
:tickets: 4967

Added a workaround for a setuptools-related failure that has been observed
as occurring on Windows installations, where setuptools is not correctly
reporting a build error when the MSVC build dependencies are not installed
and therefore not allowing graceful degradation into non C extensions
builds.

.. change::
:tags: bug, sql, py3k
:tickets: 4931

Changed the ``repr()`` of the :class:`.quoted_name` construct to use
regular string repr() under Python 3, rather than running it through
"backslashreplace" escaping, which can be misleading.

.. change::
:tags: bug, oracle, firebird
:tickets: 4931

Modified the approach of "name normalization" for the Oracle and Firebird
dialects, which converts from the UPPERCASE-as-case-insensitive convention
of these dialects into lowercase-as-case-insensitive for SQLAlchemy, to not
automatically apply the :class:`.quoted_name` construct to a name that
matches itself under upper or lower case conversion, as is the case for
many non-european characters. All names used within metadata structures
are converted to :class:`.quoted_name` objects in any case; the change
here would only affect the output of some inspection functions.

.. change::
:tags: bug, schema
:tickets: 4911

Fixed bug where a table that would have a column label overlap with a plain
column name, such as "foo.id AS foo_id" vs. "foo.foo_id", would prematurely
generate the ``._label`` attribute for a column before this overlap could
be detected due to the use of the ``index=True`` or ``unique=True`` flag on
the column in conjunction with the default naming convention of
``"column_0_label"``. This would then lead to failures when ``._label``
were used later to generate a bound parameter name, in particular those
used by the ORM when generating the WHERE clause for an UPDATE statement.
The issue has been fixed by using an alternate ``._label`` accessor for DDL
generation that does not affect the state of the :class:`_schema.Column`. The
accessor also bypasses the key-deduplication step as it is not necessary
for DDL, the naming is now consistently ``"<tablename>_<columnname>"``
without any subsequent numeric symbols when used in DDL.



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

Fixed bug where parameter repr as used in logging and error reporting needs
additional context in order to distinguish between a list of parameters for
a single statement and a list of parameter lists, as the "list of lists"
structure could also indicate a single parameter list where the first
parameter itself is a list, such as for an array parameter. The
engine/connection now passes in an additional boolean indicating how the
parameters should be considered. The only SQLAlchemy backend that expects
arrays as parameters is that of psycopg2 which uses pyformat parameters,
so this issue has not been too apparent, however as other drivers that use
positional gain more features it is important that this be supported. It
also eliminates the need for the parameter repr function to guess based on
the parameter structure passed.

.. change::
:tags: usecase, schema
:tickets: 4894

Added DDL support for "computed columns"; these are DDL column
specifications for columns that have a server-computed value, either upon
SELECT (known as "virtual") or at the point of which they are INSERTed or
UPDATEd (known as "stored"). Support is established for Postgresql, MySQL,
Oracle SQL Server and Firebird. Thanks to Federico Caselli for lots of work
on this one.

.. seealso::

:ref:`computed_ddl`


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

Fixed bug in :class:`_reflection.Inspector` where the cache key generation did not
take into account arguments passed in the form of tuples, such as the tuple
of view name styles to return for the PostgreSQL dialect. This would lead
the inspector to cache too generally for a more specific set of criteria.
The logic has been adjusted to include every keyword element in the cache,
as every argument is expected to be appropriate for a cache else the
caching decorator should be bypassed by the dialect.


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

Fixed an issue in the :meth:`_engine.Engine.table_names` method where it would
feed the dialect's default schema name back into the dialect level table
function, which in the case of SQL Server would interpret it as a
dot-tokenized schema name as viewed by the mssql dialect, which would
cause the method to fail in the case where the database username actually
had a dot inside of it. In 1.3, this method is still used by the
:meth:`_schema.MetaData.reflect` function so is a prominent codepath. In 1.4,
which is the current master development branch, this issue doesn't exist,
both because :meth:`_schema.MetaData.reflect` isn't using this method nor does the
method pass the default schema name explicitly. The fix nonetheless
guards against the default server name value returned by the dialect from
being interpreted as dot-tokenized name under any circumstances by
wrapping it in quoted_name().

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

Fixed ORM bug where a "secondary" table that referred to a selectable which
in some way would refer to the local primary table would apply aliasing to
both sides of the join condition when a relationship-related join, either
via :meth:`_query.Query.join` or by :func:`_orm.joinedload`, were generated. The
"local" side is now excluded.

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

Added new accessors to expressions of type :class:`_types.JSON` to allow for
specific datatype access and comparison, covering strings, integers,
numeric, boolean elements. This revises the documented approach of
CASTing to string when comparing values, instead adding specific
functionality into the PostgreSQL, SQlite, MySQL dialects to reliably
deliver these basic types in all cases.

.. seealso::

:class:`_types.JSON`

:meth:`_sqltypes.JSON.Comparator.as_string`

:meth:`_sqltypes.JSON.Comparator.as_boolean`

:meth:`_sqltypes.JSON.Comparator.as_float`

:meth:`_sqltypes.JSON.Comparator.as_integer`

.. change::
:tags: usecase, oracle
:tickets: 4799

Added dialect-level flag ``encoding_errors`` to the cx_Oracle dialect,
which can be specified as part of :func:`_sa.create_engine`. This is passed
to SQLAlchemy's unicode decoding converter under Python 2, and to
cx_Oracle's ``cursor.var()`` object as the ``encodingErrors`` parameter
under Python 3, for the very unusual case that broken encodings are present
in the target database which cannot be fetched unless error handling is
relaxed. The value is ultimately one of the Python "encoding errors"
parameters passed to ``decode()``.

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

The :func:`_expression.text` construct now supports "unique" bound parameters, which
will dynamically uniquify themselves on compilation thus allowing multiple
:func:`_expression.text` constructs with the same bound parameter names to be combined
together.


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

The :class:`_types.NCHAR` datatype will now bind to the
``cx_Oracle.FIXED_NCHAR`` DBAPI data bindings when used in a bound
parameter, which supplies proper comparison behavior against a
variable-length string. Previously, the :class:`_types.NCHAR` datatype
would bind to ``cx_oracle.NCHAR`` which is not fixed length; the
:class:`_types.CHAR` datatype already binds to ``cx_Oracle.FIXED_CHAR``
so it is now consistent that :class:`_types.NCHAR` binds to
``cx_Oracle.FIXED_NCHAR``.



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

Added additional "disconnect" message "Error writing data to the
connection" to Firebird disconnection detection. Pull request courtesy
lukens.

.. changelog::

:released: October 9, 2019

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

Fixed bug in SQL Server dialect with new "max_identifier_length" feature
where the mssql dialect already featured this flag, and the implementation
did not accommodate for the new initialization hook correctly.


.. change::
:tags: bug, oracle
:tickets: 4898, 4857

Fixed regression in Oracle dialect that was inadvertently using max
identifier length of 128 characters on Oracle server 12.2 and greater even
though the stated contract for the remainder of the 1.3 series is that
this value stays at 30 until version SQLAlchemy 1.4. Also repaired issues
with the retrieval of the "compatibility" version, and removed the warning
emitted when the "v$parameter" view was not accessible as this was causing
user confusion.

.. changelog::

:released: October 4, 2019

.. change::
:tags: usecase, engine
:tickets: 4857

Added new :func:`_sa.create_engine` parameter
:paramref:`_sa.create_engine.max_identifier_length`. This overrides the
dialect-coded "max identifier length" in order to accommodate for databases
that have recently changed this length and the SQLAlchemy dialect has
not yet been adjusted to detect for that version. This parameter interacts
with the existing :paramref:`_sa.create_engine.label_length` parameter in that
it establishes the maximum (and default) value for anonymously generated
labels. Additionally, post-connection detection of max identifier lengths
has been added to the dialect system. This feature is first being used
by the Oracle dialect.

.. seealso::

:ref:`oracle_max_identifier_lengths` - in the Oracle dialect documentation

.. change::
:tags: usecase, oracle
:tickets: 4857

The Oracle dialect now emits a warning if Oracle version 12.2 or greater is
used, and the :paramref:`_sa.create_engine.max_identifier_length` parameter is
not set. The version in this specific case defaults to that of the
"compatibility" version set in the Oracle server configuration, not the
actual server version. In version 1.4, the default max_identifier_length
for 12.2 or greater will move to 128 characters. In order to maintain
forwards compatibility, applications should set
:paramref:`_sa.create_engine.max_identifier_length` to 30 in order to maintain
the same length behavior, or to 128 in order to test the upcoming behavior.
This length determines among other things how generated constraint names
are truncated for statements like ``CREATE CONSTRAINT`` and ``DROP
CONSTRAINT``, which means a the new length may produce a name-mismatch
against a name that was generated with the old length, impacting database
migrations.

.. seealso::

:ref:`oracle_max_identifier_lengths` - in the Oracle dialect documentation

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

Added support for sqlite "URI" connections, which allow for sqlite-specific
flags to be passed in the query string such as "read only" for Python
sqlite3 drivers that support this.

.. seealso::

:ref:`pysqlite_uri_connections`

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

Fixed unit test regression released in 1.3.8 that would cause failure for
Oracle, SQL Server and other non-native ENUM platforms due to new
enumeration tests added as part of :ticket:`4285` enum sortability in the
unit of work; the enumerations created constraints that were duplicated on
name.

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

Restored adding cx_Oracle.DATETIME to the setinputsizes() call when a
SQLAlchemy :class:`.Date`, :class:`.DateTime` or :class:`.Time` datatype is
used, as some complex queries require this to be present. This was removed
in the 1.2 series for arbitrary reasons.

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

Added identifier quoting to the schema name applied to the "use" statement
which is invoked when a SQL Server multipart schema name is used within a
:class:`_schema.Table` that is being reflected, as well as for :class:`_reflection.Inspector`
methods such as :meth:`_reflection.Inspector.get_table_names`; this accommodates for
special characters or spaces in the database name. Additionally, the "use"
statement is not emitted if the current database matches the target owner
database name being passed.

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

Fixed regression in selectinload loader strategy caused by :ticket:`4775`
(released in version 1.3.6) where a many-to-one attribute of None would no
longer be populated by the loader. While this was usually not noticeable
due to the lazyloader populating None upon get, it would lead to a detached
instance error if the object were detached.

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

Passing a plain string expression to :meth:`.Session.query` is deprecated,
as all string coercions were removed in :ticket:`4481` and this one should
have been included. The :func:`_expression.literal_column` function may be used to
produce a textual column expression.

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

Added an explicit error message for the case when objects passed to
:class:`_schema.Table` are not :class:`.SchemaItem` objects, rather than resolving
to an attribute error.


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

A warning is emitted for a condition in which the :class:`.Session` may
implicitly swap an object out of the identity map for another one with the
same primary key, detaching the old one, which can be an observed result of
load operations which occur within the :meth:`.SessionEvents.after_flush`
hook. The warning is intended to notify the user that some special
condition has caused this to happen and that the previous object may not be
in the expected state.

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

Characters that interfere with "pyformat" or "named" formats in bound
parameters, namely ``%, (, )`` and the space character, as well as a few
other typically undesirable characters, are stripped early for a
:func:`.bindparam` that is using an anonymized name, which is typically
generated automatically from a named column which itself includes these
characters in its name and does not use a ``.key``, so that they do not
interfere either with the SQLAlchemy compiler's use of string formatting or
with the driver-level parsing of the parameter, both of which could be
demonstrated before the fix. The change only applies to anonymized
parameter names that are generated and consumed internally, not end-user
defined names, so the change should have no impact on any existing code.
Applies in particular to the psycopg2 driver which does not otherwise quote
special parameter names, but also strips leading underscores to suit Oracle
(but not yet leading numbers, as some anon parameters are currently
entirely numeric/underscore based); Oracle in any case continues to quote
parameter names that include special characters.

.. changelog::

:released: August 27, 2019

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

Fixed bug where :class:`_orm.Load` objects were not pickleable due to
mapper/relationship state in the internal context dictionary. These
objects are now converted to picklable using similar techniques as that of
other elements within the loader option system that have long been
serializable.

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

Revised the approach for the just added support for the psycopg2
"execute_values()" feature added in 1.3.7 for :ticket:`4623`. The approach
relied upon a regular expression that would fail to match for a more
complex INSERT statement such as one which had subqueries involved. The
new approach matches exactly the string that was rendered as the VALUES
clause.

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

Added support for the use of an :class:`.Enum` datatype using Python
pep-435 enumeration objects as values for use as a primary key column
mapped by the ORM. As these values are not inherently sortable, as
required by the ORM for primary keys, a new
:attr:`.TypeEngine.sort_key_function` attribute is added to the typing
system which allows any SQL type to implement a sorting for Python objects
of its type which is consulted by the unit of work. The :class:`.Enum`
type then defines this using the database value of a given enumeration.
The sorting scheme can be also be redefined by passing a callable to the
:paramref:`.Enum.sort_key_function` parameter. Pull request courtesy
Nicolas Caniart.

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

Fixed an issue whereby if the dialect "initialize" process which occurs on
first connect would encounter an unexpected exception, the initialize
process would fail to complete and then no longer attempt on subsequent
connection attempts, leaving the dialect in an un-initialized, or partially
initialized state, within the scope of parameters that need to be
established based on inspection of a live connection. The "invoke once"
logic in the event system has been reworked to accommodate for this
occurrence using new, private API features that establish an "exec once"
hook that will continue to allow the initializer to fire off on subsequent
connections, until it completes without raising an exception. This does not
impact the behavior of the existing ``once=True`` flag within the event
system.

.. change::
:tags: bug, sqlite, reflection
:tickets: 4810

Fixed bug where a FOREIGN KEY that was set up to refer to the parent table
by table name only without the column names would not correctly be
reflected as far as setting up the "referred columns", since SQLite's
PRAGMA does not report on these columns if they weren't given explicitly.
For some reason this was hardcoded to assume the name of the local column,
which might work for some cases but is not correct. The new approach
reflects the primary key of the referred table and uses the constraint
columns list as the referred columns list, if the remote column(s) aren't
present in the reflected pragma directly.


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

Fixed bug where Postgresql operators such as
:meth:`.postgresql.ARRAY.Comparator.contains` and
:meth:`.postgresql.ARRAY.Comparator.contained_by` would fail to function
correctly for non-integer values when used against a
:class:`_postgresql.array` object, due to an erroneous assert statement.

.. change::
:tags: feature, engine
:tickets: 4815

Added new parameter :paramref:`_sa.create_engine.hide_parameters` which when
set to True will cause SQL parameters to no longer be logged, nor rendered
in the string representation of a :class:`.StatementError` object.


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

Added support for reflection of CHECK constraints that include the special
PostgreSQL qualifier "NOT VALID", which can be present for CHECK
constraints that were added to an existing table with the directive that
they not be applied to existing data in the table. The PostgreSQL
dictionary for CHECK constraints as returned by
:meth:`_reflection.Inspector.get_check_constraints` may include an additional entry
``dialect_options`` which within will contain an entry ``"not_valid":
True`` if this symbol is detected. Pull request courtesy Bill Finn.

.. changelog::

:released: August 14, 2019

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

Fixed issue where :class:`.Index` object which contained a mixture of
functional expressions which were not resolvable to a particular column,
in combination with string-based column names, would fail to initialize
its internal state correctly leading to failures during DDL compilation.

.. change::
:tags: bug, sqlite
:tickets: 4798

The dialects that support json are supposed to take arguments
``json_serializer`` and ``json_deserializer`` at the create_engine() level,
however the SQLite dialect calls them ``_json_serializer`` and
``_json_deserilalizer``. The names have been corrected, the old names are
accepted with a change warning, and these parameters are now documented as
:paramref:`_sa.create_engine.json_serializer` and
:paramref:`_sa.create_engine.json_deserializer`.


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

The MySQL dialects will emit "SET NAMES" at the start of a connection when
charset is given to the MySQL driver, to appease an apparent behavior
observed in MySQL 8.0 that raises a collation error when a UNION includes
string columns unioned against columns of the form CAST(NULL AS CHAR(..)),
which is what SQLAlchemy's polymorphic_union function does. The issue
seems to have affected PyMySQL for at least a year, however has recently
appeared as of mysqlclient 1.4.4 based on changes in how this DBAPI creates
a connection. As the presence of this directive impacts three separate
MySQL charset settings which each have intricate effects based on their
presence, SQLAlchemy will now emit the directive on new connections to
ensure correct behavior.

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

Added new dialect flag for the psycopg2 dialect, ``executemany_mode`` which
supersedes the previous experimental ``use_batch_mode`` flag.
``executemany_mode`` supports both the "execute batch" and "execute values"
functions provided by psycopg2, the latter which is used for compiled
:func:`_expression.insert` constructs. Pull request courtesy Yuval Dinari.

.. seealso::

:ref:`psycopg2_executemany_mode`




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

Fixed bug where :meth:`.TypeEngine.column_expression` method would not be
applied to subsequent SELECT statements inside of a UNION or other
:class:`_selectable.CompoundSelect`, even though the SELECT statements are rendered at
the topmost level of the statement. New logic now differentiates between
rendering the column expression, which is needed for all SELECTs in the
list, vs. gathering the returned data type for the result row, which is
needed only for the first SELECT.

.. change::
:tags: bug, sqlite
:tickets: 4793

Fixed bug where usage of "PRAGMA table_info" in SQLite dialect meant that
reflection features to detect for table existence, list of table columns,
and list of foreign keys, would default to any table in any attached
database, when no schema name was given and the table did not exist in the
base schema. The fix explicitly runs PRAGMA for the 'main' schema and then
the 'temp' schema if the 'main' returned no rows, to maintain the behavior
of tables + temp tables in the "no schema" namespace, attached tables only
in the "schema" namespace.


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

Fixed issue where internal cloning of SELECT constructs could lead to a key
error if the copy of the SELECT changed its state such that its list of
columns changed. This was observed to be occurring in some ORM scenarios
which may be unique to 1.3 and above, so is partially a regression fix.



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

Fixed regression caused by new selectinload for many-to-one logic where
a primaryjoin condition not based on real foreign keys would cause
KeyError if a related object did not exist for a given key value on the
parent object.

.. change::
:tags: usecase, mysql
:tickets: 4783

Added reserved words ARRAY and MEMBER to the MySQL reserved words list, as
MySQL 8.0 has now made these reserved.


.. change::
:tags: bug, events
:tickets: 4794

Fixed issue in event system where using the ``once=True`` flag with
dynamically generated listener functions would cause event registration of
future events to fail if those listener functions were garbage collected
after they were used, due to an assumption that a listened function is
strongly referenced. The "once" wrapped is now modified to strongly
reference the inner function persistently, and documentation is updated
that using "once" does not imply automatic de-registration of listener
functions.

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

Added another fix for an upstream MySQL 8 issue where a case sensitive
table name is reported incorrectly in foreign key constraint reflection,
this is an extension of the fix first added for :ticket:`4344` which
affects a case sensitive column name. The new issue occurs through MySQL
8.0.17, so the general logic of the 88718 fix remains in place.

.. seealso::

https://bugs.mysql.com/bug.php?id=96365 - upstream bug


.. change::
:tags: usecase, mssql
:tickets: 4782

Added new :func:`_mssql.try_cast` construct for SQL Server which emits
"TRY_CAST" syntax. Pull request courtesy Leonel Atencio.

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

Fixed bug where using :meth:`_query.Query.first` or a slice expression in
conjunction with a query that has an expression based "offset" applied
would raise TypeError, due to an "or" conditional against "offset" that did
not expect it to be a SQL expression as opposed to an integer or None.


.. changelog::