1.4.26

Released: October 19, 2021

orm

• [orm] [bug] Improved the exception message generated when configuring a mapping with joined table inheritance where the two tables either have no foreign key relationships set up, or where they have multiple foreign key relationships set up. The message is now ORM specific and includes context that the _orm.Mapper.inherit_condition parameter may be needed particularly for the ambiguous foreign keys case.

• [orm] [bug] Fixed issue with _orm.with_loader_criteria() feature where ON criteria would not be added to a JOIN for a query of the form select(A).join(B), stating a target while making use of an implicit ON clause.

  References: #7189

• [orm] [bug] Fixed bug where the ORM "plugin", necessary for features such as _orm.with_loader_criteria() to work correctly, would not be applied to a _sql.select() which queried from an ORM column expression if it made use of the _sql.ColumnElement.label() modifier.

  References: #7205

• [orm] [bug] Add missing methods added in #6991 to _scoping.scoped_session and _asyncio.async_scoped_session().

  References: #7103

• [orm] [bug] An extra layer of warning messages has been added to the functionality of _orm.Query.join() and the ORM version of _sql.Select.join(), where a few places where "automatic aliasing" continues to occur will now be called out as a pattern to avoid, mostly specific to the area of joined table inheritance where classes that share common base tables are being joined together without using explicit aliases. One case emits a legacy warning for a pattern that's not recommended, the other case is fully deprecated.

  The automatic aliasing within ORM join() which occurs for overlapping mapped tables does not work consistently with all APIs such as _orm.contains_eager(), and rather than continue to try to make these use cases work everywhere, replacing with a more user-explicit pattern is clearer, less prone to bugs and simplifies SQLAlchemy's internals further.

  The warnings include links to the errors.rst page where each pattern is demonstrated along with the recommended pattern to fix.

  References: #6972, #6974

• [orm] [bug] Fixed bug where iterating a Result from a _orm.Session after that _orm.Session were closed would partially attach objects to that session in an essentially invalid state. It now raises an exception with a link to new documentation if an un-buffered result is iterated from a _orm.Session that was closed or otherwise had the _orm.Session.expunge_all() method called after that Result was generated. The prebuffer_rows execution option, as is used automatically by the asyncio extension for client-side result sets, may be used to produce a Result where the ORM objects are prebuffered, and in this case iterating the result will produce a series of detached objects.

  References: #7128

• [orm] [bug] Related to #7153, fixed an issue where result column lookups would fail for "adapted" SELECT statements that selected for "constant" value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue #6259 which removed all "adaption" for constants like NULL, "true", and "false" when rewriting expressions in a SQL statement, but this broke the case where the same adaption logic were used to resolve the constant to a labeled expression for the purposes of result set targeting.

  References: #7154

• [orm] [bug] [regression] Fixed regression where ORM loaded objects could not be pickled in cases where loader options making use of "*" were used in certain combinations, such as combining the _orm.joinedload() loader strategy with raiseload('*') of sub-elements.

  References: #7134

• [orm] [bug] [regression] Fixed regression where the use of a _hybrid.hybrid_property attribute or a mapped _orm.composite() attribute as a key passed to the _dml.Update.values() method for an ORM-enabled _dml.Update statement, as well as when using it via the legacy _orm.Query.update() method, would be processed for incoming ORM/hybrid/composite values within the compilation stage of the UPDATE statement, which meant that in those cases where caching occurred, subsequent invocations of the same statement would no longer receive the correct values. This would include not only hybrids that use the _hybrid.hybrid_property.update_expression() method, but any use of a plain hybrid attribute as well. For composites, the issue instead caused a non-repeatable cache key to be generated, which would break caching and could fill up the statement cache with repeated statements.

  The _dml.Update construct now handles the processing of key/value pairs passed to _dml.Update.values() and _dml.Update.ordered_values() up front when the construct is first generated, before the cache key has been generated so that the key/value pairs are processed each time, and so that the cache key is generated against the individual column/value pairs that will ultimately be used in the statement.

  References: #7209

• [orm] Passing a Query object to _orm.Session.execute() is not the intended use of this object, and will now raise a deprecation warning.

  References: #6284

examples

• [examples] [bug] Repaired the examples in examples/versioned_rows to use SQLAlchemy 1.4 APIs correctly; these examples had been missed when API changes like removing "passive" from _orm.Session.is_modified() were made as well as the _ormevents.SessionEvents.do_orm_execute() event hook were added.

  References: #7169

engine

• [engine] [bug] Fixed issue where the deprecation warning for the URL constructor which indicates that the URL.create() method should be used would not emit if a full positional argument list of seven arguments were passed; additionally, validation of URL arguments will now occur if the constructor is called in this way, which was being skipped previously.

  References: #7130

• [engine] [bug] [postgresql] The _reflection.Inspector.reflect_table() method now supports reflecting tables that do not have user defined columns. This allows _schema.MetaData.reflect() to properly complete reflection on databases that contain such tables. Currently, only PostgreSQL is known to support such a construct among the common database backends.

  References: #3247

• [engine] [bug] Implemented proper __reduce__() methods for all SQLAlchemy exception objects to ensure they all support clean round trips when pickling, as exception objects are often serialized for the purposes of various debugging tools.

  References: #7077

sql

• [sql] [bug] Fixed issue where SQL queries using the _functions.FunctionElement.within_group() construct could not be pickled, typically when using the sqlalchemy.ext.serializer extension but also for general generic pickling.

  References: #6520

• [sql] [bug] Repaired issue in new _sql.HasCTE.cte.nesting parameter introduced with #4123 where a recursive _sql.CTE using _sql.HasCTE.cte.recursive in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that the _sql.CTE construct creates a correct cache key. Pull request courtesy Eric Masseran.

  References: #4123

• [sql] [bug] Account for the _sql.table.schema parameter passed to the _sql.table() construct, such that it is taken into account when accessing the _sql.TableClause.fullname attribute.

  References: #7061

• [sql] [bug] Fixed an inconsistency in the _sql.ColumnOperators.any_() / _sql.ColumnOperators.all_() functions / methods where the special behavior these functions have of "flipping" the expression such that the "ANY" / "ALL" expression is always on the right side would not function if the comparison were against the None value, that is, "column.any_() == None" should produce the same SQL expression as "null() == column.any_()". Added more docs to clarify this as well, plus mentions that any_() / all_() generally supersede the ARRAY version "any()" / "all()".

  References: #7140

• [sql] [bug] [regression] Fixed issue where "expanding IN" would fail to function correctly with datatypes that use the _types.TypeEngine.bind_expression() method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself.

  References: #7177

• [sql] [bug] Adjusted the "column disambiguation" logic that's new in 1.4, where the same expression repeated gets an "extra anonymous" label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using "singleton" values like _sql.null(). This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery.

  References: #7153

mypy

• [mypy] [bug] Fixed issue in mypy plugin to improve upon some issues detecting Enum() SQL types containing custom Python enumeration classes. Pull request courtesy Hiroshi Ogawa.

  References: #6435

postgresql

• [postgresql] [bug] Added a "disconnect" condition for the "SSL SYSCALL error: Bad address" error message as reported by psycopg2. Pull request courtesy Zeke Brechtel.

  References: #5387

• [postgresql] [bug] [regression] Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the "expanding IN" feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the _types.TypeEngine.bind_expression() method with _types.ARRAY to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level.

  References: #7177

mysql

• [mysql] [bug] [mariadb] Fixes to accommodate for the MariaDB 10.6 series, including backwards incompatible changes in both the mariadb-connector Python driver (supported on SQLAlchemy 1.4 only) as well as the native 10.6 client libraries that are used automatically by the mysqlclient DBAPI (applies to both 1.3 and 1.4). The "utf8mb3" encoding symbol is now reported by these client libraries when the encoding is stated as "utf8", leading to lookup and encoding errors within the MySQL dialect that does not expect this symbol. Updates to both the MySQL base library to accommodate for this utf8mb3 symbol being reported as well as to the test suite. Thanks to Georg Richter for support.

  This change is also backported to: 1.3.25

  References: #7115, #7136

• [mysql] [bug] Fixed issue in MySQL _mysql.match() construct where passing a clause expression such as _sql.bindparam() or other SQL expression for the "against" parameter would fail. Pull request courtesy Anton Kovalevich.

  References: #7144

• [mysql] [bug] Fixed installation issue where the sqlalchemy.dialects.mysql module would not be importable if "greenlet" were not installed.

  References: #7204

mssql

• [mssql] [usecase] Added reflection support for SQL Server foreign key options, including "ON UPDATE" and "ON DELETE" values of "CASCADE" and "SET NULL".

• [mssql] [bug] Fixed issue with Inspector.get_foreign_keys() where foreign keys were omitted if they were established against a unique index instead of a unique constraint.

  References: #7160

• [mssql] [bug] Fixed issue with Inspector.has_table() where it would return False if a local temp table with the same name from a different session happened to be returned first when querying tempdb. This is a continuation of #6910 which accounted for the temp table existing only in the alternate session and not the current one.

  References: #7168

• [mssql] [bug] [regression] Fixed bug in SQL Server _mssql.DATETIMEOFFSET datatype where the ODBC implementation would not generate the correct DDL, for cases where the type were converted using the dialect.type_descriptor() method, the usage of which is illustrated in some documented examples for TypeDecorator, though not necessary for most datatypes. Regression was introduced by #6366. As part of this change, the full list of SQL Server date types have been amended to return a "dialect impl" that generates the same DDL name as the supertype.

  References: #7129