2.0.14

2.0.14

Released: May 18, 2023

orm

• [orm] [bug] Modified the JoinedLoader implementation to use a simpler approach in
  one particular area where it previously used a cached structure that would
  be shared among threads. The rationale is to avoid a potential race
  condition which is suspected of being the cause of a particular crash
  that's been reported multiple times. The cached structure in question is
  still ultimately "cached" via the compiled SQL cache, so a performance
  degradation is not anticipated.

  References: #9777

• [orm] [bug] [regression] Fixed regression where use of _dml.update() or _dml.delete()
  within a _sql.CTE construct, then used in a _sql.select(),
  would raise a CompileError as a result of ORM related rules for
  performing ORM-level update/delete statements.

  References: #9767

• [orm] [bug] Fixed issue in new ORM Annotated Declarative where using a
  _schema.ForeignKey (or other column-level constraint) inside of
  _orm.mapped_column() which is then copied out to models via pep-593
  Annotated would apply duplicates of each constraint to the
  _schema.Column as produced in the target _schema.Table,
  leading to incorrect CREATE TABLE DDL as well as migration directives under
  Alembic.

  References: #9766

• [orm] [bug] Fixed issue where using additional relationship criteria with the
  _orm.joinedload() loader option, where the additional criteria itself
  contained correlated subqueries that referred to the joined entities and
  therefore also required "adaption" to aliased entities, would be excluded
  from this adaption, producing the wrong ON clause for the joinedload.

  References: #9779

sql

• [sql] [usecase] Generalized the MSSQL _sql.try_cast() function into the
  sqlalchemy. import namespace so that it may be implemented by third
  party dialects as well. Within SQLAlchemy, the _sql.try_cast()
  function remains a SQL Server-only construct that will raise
  CompileError if used with backends that don't support it.

  _sql.try_cast() implements a CAST where un-castable conversions are
  returned as NULL, instead of raising an error. Theoretically, the construct
  could be implemented by third party dialects for Google BigQuery, DuckDB,
  and Snowflake, and possibly others.

  Pull request courtesy Nick Crews.

  References: #9752

• [sql] [bug] Fixed issue in _sql.values() construct where an internal compilation
  error would occur if the construct were used inside of a scalar subquery.

  References: #9772

postgresql

• [postgresql] [bug] Fixed apparently very old issue where the
  _postgresql.ENUM.create_type parameter, when set to its
  non-default of False, would not be propagated when the
  _schema.Column which it's a part of were copied, as is common when
  using ORM Declarative mixins.

  References: #9773

tests

• [tests] [bug] [pypy] Fixed test that relied on the sys.getsizeof() function to not run on
  pypy, where this function appears to have different behavior than it does
  on cpython.

  References: #9789

2.0.13

2.0.13

Released: May 10, 2023

orm

• [orm] [bug] Fixed issue where ORM Annotated Declarative would not resolve forward
  references correctly in all cases; in particular, when using
  from __future__ import annotations in combination with Pydantic
  dataclasses.

  References: #9717

• [orm] [bug] Fixed issue in new orm_queryguide_upsert_returning feature where the
  populate_existing execution option was not being propagated to the
  loading option, preventing existing attributes from being refreshed
  in-place.

  References: #9746

• [orm] [bug] Fixed loader strategy pathing issues where eager loaders such as
  _orm.joinedload() / _orm.selectinload() would fail to traverse
  fully for many-levels deep following a load that had a
  _orm.with_polymorphic() or similar construct as an interim member.

  References: #9715

• [orm] [bug] Fixed issue in _orm.mapped_column() construct where the correct
  warning for "column X named directly multiple times" would not be emitted
  when ORM mapped attributes referred to the same _schema.Column, if
  the _orm.mapped_column() construct were involved, raising an internal
  assertion instead.

  References: #9630

sql

• [sql] [usecase] Implemented the "cartesian product warning" for UPDATE and DELETE
  statements, those which include multiple tables that are not correlated
  together in some way.

  References: #9721

• [sql] [bug] Fixed the base class for dialect-specific float/double types; Oracle
  _oracle.BINARY_DOUBLE now subclasses _sqltypes.Double,
  and internal types for _sqltypes.Float for asyncpg and pg8000 now
  correctly subclass _sqltypes.Float.

• [sql] [bug] Fixed issue where _dml.update() construct that included multiple
  tables and no VALUES clause would raise with an internal error. Current
  behavior for _dml.Update with no values is to generate a SQL
  UPDATE statement with an empty "set" clause, so this has been made
  consistent for this specific sub-case.

schema

• [schema] [performance] Improved how table columns are added, avoiding unnecessary allocations,
  significantly speeding up the creation of many table, like when reflecting
  entire schemas.

  References: #9597

typing

• [typing] [bug] Fixed typing for the _orm.Session.get.with_for_update parameter
  of _orm.Session.get() and _orm.Session.refresh() (as well as
  corresponding methods on _asyncio.AsyncSession) to accept boolean
  True and all other argument forms accepted by the parameter at runtime.

  References: #9762

• [typing] [sql] Added type :data:_sql.ColumnExpressionArgument as a public-facing type
  that indicates column-oriented arguments which are passed to SQLAlchemy
  constructs, such as _sql.Select.where(), _sql.and_() and
  others. This may be used to add typing to end-user functions which call
  these methods.

  Unknown interpreted text role "data".

  References: #9656

asyncio

• [asyncio] [usecase] Added a new helper mixin _asyncio.AsyncAttrs that seeks to improve
  the use of lazy-loader and other expired or deferred ORM attributes with
  asyncio, providing a simple attribute accessor that provides an await
  interface to any ORM attribute, whether or not it needs to emit SQL.

  References: #9731

• [asyncio] [bug] Fixed issue in semi-private await_only() and await_fallback()
  concurrency functions where the given awaitable would remain un-awaited if
  the function threw a GreenletError, which could cause "was not awaited"
  warnings later on if the program continued. In this case, the given
  awaitable is now cancelled before the exception is thrown.

postgresql

• [postgresql] [bug] [regression] Fixed another regression due to the "insertmanyvalues" change in 2.0.10 as
  part of #9618, in a similar way as regression #9701, where
  LargeBinary datatypes also need additional casts on when using the
  asyncpg driver specifically in order to work with the new bulk INSERT
  format.

  References: #9739

oracle

• [oracle] [reflection] Added reflection support in the Oracle dialect to expression based indexes
  and the ordering direction of index expressions.

  References: #9597

misc

• [bug] [ext] Fixed issue in _mutable.Mutable where event registration for ORM
  mapped attributes would be called repeatedly for mapped inheritance
  subclasses, leading to duplicate events being invoked in inheritance
  hierarchies.

  References: #9676

2.0.12

2.0.12

Released: April 30, 2023

orm

• [orm] [bug] Fixed critical caching issue where the combination of
  _orm.aliased() and _hybrid.hybrid_property() expression
  compositions would cause a cache key mismatch, leading to cache keys that
  held onto the actual _orm.aliased() object while also not matching
  that of equivalent constructs, filling up the cache.

  This change is also backported to: 1.4.48

  References: #9728

mysql

• [mysql] [bug] [mariadb] Fixed issues regarding reflection of comments for _schema.Table
  and _schema.Column objects, where the comments contained control
  characters such as newlines. Additional testing support for these
  characters as well as extended Unicode characters in table and column
  comments (the latter of which aren't supported by MySQL/MariaDB) added to
  testing overall.

  References: #9722

1.4.48

1.4.48

Released: April 30, 2023

orm

• [orm] [bug] Fixed critical caching issue where the combination of
  _orm.aliased() and _hybrid.hybrid_property() expression
  compositions would cause a cache key mismatch, leading to cache keys that
  held onto the actual _orm.aliased() object while also not matching
  that of equivalent constructs, filling up the cache.

  References: #9728

• [orm] [bug] Fixed bug where various ORM-specific getters such as
  ORMExecuteState.is_column_load,
  ORMExecuteState.is_relationship_load,
  ORMExecuteState.loader_strategy_path etc. would throw an
  AttributeError if the SQL statement itself were a "compound select"
  such as a UNION.

  References: #9634

• [orm] [bug] Fixed endless loop which could occur when using "relationship to aliased
  class" feature and also indicating a recursive eager loader such as
  lazy="selectinload" in the loader, in combination with another eager
  loader on the opposite side. The check for cycles has been fixed to include
  aliased class relationships.

  References: #9590

2.0.11

2.0.11

Released: April 26, 2023

orm

• [orm] [usecase] The ORM bulk INSERT and UPDATE <orm_expression_update_delete>
  features now add these capabilities:

  -   The requirement that extra parameters aren't passed when using ORM
      INSERT using the "orm" dml_strategy setting is lifted.
  
  -   The requirement that additional WHERE criteria is not passed when using
      ORM UPDATE using the "bulk" dml_strategy setting is lifted.  Note that
      in this case, the check for expected row count is turned off.
  

  References: #9583, #9595

• [orm] [bug] Fixed 2.0 regression where use of _sql.bindparam() inside of
  _dml.Insert.values() would fail to be interpreted correctly when
  executing the _dml.Insert statement using the ORM
  _orm.Session, due to the new
  ORM-enabled insert feature <orm_queryguide_bulk_insert> not
  implementing this use case.

  References: #9583, #9595

engine

• [engine] [performance] A series of performance enhancements to _engine.Row:

  -   `__getattr__` performance of the row's "named tuple" interface has
      been improved; within this change, the `_engine.Row`
      implementation has been streamlined, removing constructs and logic
      that were specific to the 1.4 and prior series of SQLAlchemy.
      As part of this change, the serialization format of `_engine.Row`
      has been modified slightly, however rows which were pickled with previous
      SQLAlchemy 2.0 releases will be recognized within the new format.
      Pull request courtesy J. Nick Koston.
  
  -   Improved row processing performance for "binary" datatypes by making the
      "bytes" handler conditional on a per driver basis.  As a result, the
      "bytes" result handler has been removed for nearly all drivers other than
      psycopg2, all of which in modern forms support returning Python "bytes"
      directly.  Pull request courtesy J. Nick Koston.
  
  -   Additional refactorings inside of `_engine.Row` to improve
      performance by Federico Caselli.
  

  References: #9678, #9680

• [engine] [bug] [regression] Fixed regression which prevented the _engine.URL.normalized_query
  attribute of _engine.URL from functioning.

  References: #9682

sql

• [sql] [usecase] Added support for slice access with ColumnCollection, e.g.
  table.c[0:5], subquery.c[:-1] etc. Slice access returns a sub
  ColumnCollection in the same way as passing a tuple of keys. This
  is a natural continuation of the key-tuple access added for #8285,
  where it appears to be an oversight that the slice access use case was
  omitted.

  References: #8285

typing

• [typing] [bug] Improved typing of _engine.RowMapping to indicate that it
  support also _schema.Column as index objects, not only
  string names. Pull request courtesy Andy Freeland.

  References: #9644

postgresql

• [postgresql] [bug] [regression] Fixed critical regression caused by #9618, which modified the
  architecture of the :term:insertmanyvalues feature for 2.0.10, which
  caused floating point values to lose all decimal places when being inserted
  using the insertmanyvalues feature with either the psycopg2 or psycopg
  drivers.

  Unknown interpreted text role "term".

  References: #9701

mssql

• [mssql] [bug] Implemented the _sqltypes.Double type for SQL Server, where it
  will render DOUBLE PRECISION at DDL time. This is implemented using
  a new MSSQL datatype _mssql.DOUBLE_PRECISION which also may
  be used directly.

oracle

• [oracle] [bug] Fixed issue in Oracle dialects where Decimal returning types such as
  _sqltypes.Numeric would return floating point values, rather than
  Decimal objects, when these columns were used in the
  _dml.Insert.returning() clause to return INSERTed values.

2.0.10

2.0.10

Released: April 21, 2023

orm

• [orm] [bug] Fixed bug where various ORM-specific getters such as
  ORMExecuteState.is_column_load,
  ORMExecuteState.is_relationship_load,
  ORMExecuteState.loader_strategy_path etc. would throw an
  AttributeError if the SQL statement itself were a "compound select"
  such as a UNION.

  This change is also backported to: 1.4.48

  References: #9634

• [orm] [bug] Fixed bug where various ORM-specific getters such as
  ORMExecuteState.is_column_load,
  ORMExecuteState.is_relationship_load,
  ORMExecuteState.loader_strategy_path etc. would throw an
  AttributeError if the SQL statement itself were a "compound select"
  such as a UNION.

  This change is also backported to: 1.4.48

  References: #9634

• [orm] [bug] Fixed issue where the _orm.declared_attr.directive() modifier was not
  correctly honored for subclasses when applied to the __mapper_args__
  special method name, as opposed to direct use of
  _orm.declared_attr. The two constructs should have identical
  runtime behaviors.

  References: #9625

• [orm] [bug] Made an improvement to the _orm.with_loader_criteria() loader option
  to allow it to be indicated in the Executable.options() method of a
  top-level statement that is not itself an ORM statement. Examples include
  _sql.select() that's embedded in compound statements such as
  _sql.union(), within an _dml.Insert.from_select() construct, as
  well as within CTE expressions that are not ORM related at the top level.

  References: #9635

• [orm] [bug] Fixed bug in ORM bulk insert feature where additional unnecessary columns
  would be rendered in the INSERT statement if RETURNING of individual columns
  were requested.

  References: #9685

• [orm] [bug] Fixed bug in ORM Declarative Dataclasses where the
  _orm.query_expression() and _orm.column_property()
  constructs, which are documented as read-only constructs in the context of
  a Declarative mapping, could not be used with a
  _orm.MappedAsDataclass class without adding init=False, which
  in the case of _orm.query_expression() was not possible as no
  init parameter was included. These constructs have been modified from a
  dataclass perspective to be assumed to be "read only", setting
  init=False by default and no longer including them in the pep-681
  constructor. The dataclass parameters for _orm.column_property()
init, default, default_factory, kw_only are now deprecated;
  these fields don't apply to _orm.column_property() as used in a
  Declarative dataclasses configuration where the construct would be
  read-only. Also added read-specific parameter
  _orm.query_expression.compare to
  _orm.query_expression(); _orm.query_expression.repr
  was already present.

  References: #9628

• [orm] [bug] Added missing _orm.mapped_column.active_history parameter
  to _orm.mapped_column() construct.

engine

• [engine] [usecase] Added _sa.create_pool_from_url() and
  _asyncio.create_async_pool_from_url() to create
  a _pool.Pool instance from an input url passed as string
  or _sa.URL.

  References: #9613

• [engine] [bug] Repaired a major shortcoming which was identified in the
  engine_insertmanyvalues performance optimization feature first
  introduced in the 2.0 series. This was a continuation of the change in
  2.0.9 which disabled the SQL Server version of the feature due to a
  reliance in the ORM on apparent row ordering that is not guaranteed to take
  place. The fix applies new logic to all "insertmanyvalues" operations,
  which takes effect when a new parameter
  _dml.Insert.returning.sort_by_parameter_order on the
  _dml.Insert.returning() or _dml.UpdateBase.return_defaults()
  methods, that through a combination of alternate SQL forms, direct
  correspondence of client side parameters, and in some cases downgrading to
  running row-at-a-time, will apply sorting to each batch of returned rows
  using correspondence to primary key or other unique values in each row
  which can be correlated to the input data.

  Performance impact is expected to be minimal as nearly all common primary
  key scenarios are suitable for parameter-ordered batching to be
  achieved for all backends other than SQLite, while "row-at-a-time"
  mode operates with a bare minimum of Python overhead compared to the very
  heavyweight approaches used in the 1.x series. For SQLite, there is no
  difference in performance when "row-at-a-time" mode is used.

  It's anticipated that with an efficient "row-at-a-time" INSERT with
  RETURNING batching capability, the "insertmanyvalues" feature can be later
  be more easily generalized to third party backends that include RETURNING
  support but not necessarily easy ways to guarantee a correspondence
  with parameter order.

  References: #9603, #9618

typing

• [typing] [bug] Added typing information for recently added operators
  ColumnOperators.icontains(), ColumnOperators.istartswith(),
  ColumnOperators.iendswith(), and bitwise operators
  ColumnOperators.bitwise_and(), ColumnOperators.bitwise_or(),
  ColumnOperators.bitwise_xor(), ColumnOperators.bitwise_not(),
  ColumnOperators.bitwise_lshift()
ColumnOperators.bitwise_rshift(). Pull request courtesy Martijn
  Pieters.

  References: #9650

• [typing] [bug] Updates to the codebase to pass typing with Mypy 1.2.0.

• [typing] [bug] Fixed typing issue where _orm.PropComparator.and_() expressions would
  not be correctly typed inside of loader options such as
  _orm.selectinload().

  References: #9669

postgresql

• [postgresql] [usecase] Added prepared_statement_name_func connection argument option in the
  asyncpg dialect. This option allows passing a callable used to customize
  the name of the prepared statement that will be created by the driver
  when executing queries. Pull request courtesy Pavel Sirotkin.

  References: #9608

• [postgresql] [usecase] Add missing _postgresql.Range.intersection() method.
  Pull request courtesy Yurii Karabas.

  References: #9509

• [postgresql] [bug] Restored the _postgresql.ENUM.name parameter as optional in the
  signature for _postgresql.ENUM, as this is chosen automatically
  from a given pep-435 Enum type.

  References: #9611

• [postgresql] [bug] Fixed issue where the comparison for _postgresql.ENUM against a
  plain string would cast that right-hand side type as VARCHAR, which due to
  more explicit casting added to dialects such as asyncpg would produce a
  PostgreSQL type mismatch error.

  References: #9621

• [postgresql] [bug] Fixed issue that prevented reflection of expression based indexes
  with long expressions in PostgreSQL. The expression where erroneously
  truncated to the identifier length (that's 63 bytes by default).

  References: #9615

mssql

• [mssql] [bug] Restored the :term:insertmanyvalues feature for Microsoft SQL Server.
  This feature was disabled in version 2.0.9 due to an apparent reliance
  on the ordering of RETURNING that is not guaranteed. The architecture of
  the "insertmanyvalues" feature has been reworked to accommodate for
  specific organizations of INSERT statements and result row handling that
  can guarantee the correspondence of returned rows to input records.

  Unknown interpreted text role "term".

  References: #9603, #9618

oracle

• [oracle] [bug] Fixed issue where the _sqltypes.Uuid datatype could not be used in
  an INSERT..RETURNING clause with the Oracle dialect.

2.0.9

2.0.9

Released: April 5, 2023

orm

• [orm] [bug] Fixed endless loop which could occur when using "relationship to aliased
  class" feature and also indicating a recursive eager loader such as
  lazy="selectinload" in the loader, in combination with another eager
  loader on the opposite side. The check for cycles has been fixed to include
  aliased class relationships.

  This change is also backported to: 1.4.48

  References: #9590

mariadb

• [mariadb] [bug] Added row_number as reserved word in MariaDb.

  References: #9588

mssql

• [mssql] [bug] Due to a critical bug identified in SQL Server, the SQLAlchemy
  "insertmanyvalues" feature which allows fast INSERT of many rows while also
  supporting RETURNING unfortunately needs to be disabled for SQL Server. SQL
  Server is apparently unable to guarantee that the order of rows inserted
  matches the order in which they are sent back by OUTPUT inserted when
  table-valued rows are used with INSERT in conjunction with OUTPUT inserted.
  We are trying to see if Microsoft is able to confirm this undocumented
  behavior however there is no known workaround, other than it's not safe to
  use table-valued expressions with OUTPUT inserted for now.

  References: #9603

• [mssql] [bug] Changed the bulk INSERT strategy used for SQL Server "executemany" with
  pyodbc when fast_executemany is set to True by using
  fast_executemany / cursor.executemany() for bulk INSERT that does
  not include RETURNING, restoring the same behavior as was used in
  SQLAlchemy 1.4 when this parameter is set.

  New performance details from end users have shown that fast_executemany
  is still much faster for very large datasets as it uses ODBC commands that
  can receive all rows in a single round trip, allowing for much larger
  datasizes than the batches that can be sent by "insertmanyvalues"
  as was implemented for SQL Server.

  While this change was made such that "insertmanyvalues" continued to be
  used for INSERT that includes RETURNING, as well as if fast_executemany
  were not set, due to #9603, the "insertmanyvalues" strategy has
  been disabled for SQL Server across the board in any case.

  References: #9586

2.0.8

2.0.8

Released: March 31, 2023

orm

• [orm] [usecase] Exceptions such as TypeError and ValueError raised by Python
  dataclasses when making use of the _orm.MappedAsDataclass mixin
  class or _orm.registry.mapped_as_dataclass() decorator are now
  wrapped within an InvalidRequestError wrapper along with
  informative context about the error message, referring to the Python
  dataclasses documentation as the authoritative source of background
  information on the cause of the exception.

  References: #9563

• [orm] [bug] Fixed issue in ORM Annotated Declarative where using a recursive type (e.g.
  using a nested Dict type) would result in a recursion overflow in the ORM's
  annotation resolution logic, even if this datatype were not necessary to
  map the column.

  References: #9553

• [orm] [bug] Fixed issue where the _orm.mapped_column() construct would raise an
  internal error if used on a Declarative mixin and included the
  _orm.mapped_column.deferred parameter.

  References: #9550

• [orm] [bug] Expanded the warning emitted when a plain _sql.column() object is
  present in a Declarative mapping to include any arbitrary SQL expression
  that is not declared within an appropriate property type such as
  _orm.column_property(), _orm.deferred(), etc. These attributes
  are otherwise not mapped at all and remain unchanged within the class
  dictionary. As it seems likely that such an expression is usually not
  what's intended, this case now warns for all such otherwise ignored
  expressions, rather than just the _sql.column() case.

  References: #9537

• [orm] [bug] Fixed regression where accessing the expression value of a hybrid property
  on a class that was either unmapped or not-yet-mapped (such as calling upon
  it within a _orm.declared_attr() method) would raise an internal
  error, as an internal fetch for the parent class' mapper would fail and an
  instruction for this failure to be ignored were inadvertently removed in
  2.0.

  References: #9519

• [orm] [bug] Fields that are declared on Declarative Mixins and then combined with
  classes that make use of _orm.MappedAsDataclass, where those mixin
  fields are not themselves part of a dataclass, now emit a deprecation
  warning as these fields will be ignored in a future release, as Python
  dataclasses behavior is to ignore these fields. Type checkers will not see
  these fields under pep-681.

  References: #9350

• [orm] [bug] Fixed issue where the _sql.BindParameter.render_literal_execute()
  method would fail when called on a parameter that also had ORM annotations
  associated with it. In practice, this would be observed as a failure of SQL
  compilation when using some combinations of a dialect that uses "FETCH
  FIRST" such as Oracle along with a _sql.Select construct that uses
  _sql.Select.limit(), within some ORM contexts, including if the
  statement were embedded within a relationship primaryjoin expression.

  References: #9526

• [orm] [bug] Towards maintaining consistency with unit-of-work changes made for
  #5984 and #8862, both of which disable "lazy='raise'"
  handling within _orm.Session processes that aren't triggered by
  attribute access, the _orm.Session.delete() method will now also
  disable "lazy='raise'" handling when it traverses relationship paths in
  order to process the "delete" and "delete-orphan" cascade rules.
  Previously, there was no easy way to generically call
  _orm.Session.delete() on an object that had "lazy='raise'" set up
  such that only the necessary relationships would be loaded. As
  "lazy='raise'" is primarily intended to catch SQL loading that emits on
  attribute access, _orm.Session.delete() is now made to behave like
  other _orm.Session methods including _orm.Session.merge() as
  well as _orm.Session.flush() along with autoflush.

  References: #9549

• [orm] [bug] Fixed issue where an annotation-only _orm.Mapped directive could
  not be used in a Declarative mixin class, without that attribute attempting
  to take effect for single- or joined-inheritance subclasses of mapped
  classes that had already mapped that attribute on a superclass, producing
  conflicting column errors and/or warnings.

  References: #9564

• [orm] [bug] [typing] Properly type _dml.Insert.from_select.names to accept
  a list of string or columns or mapped attributes.

  References: #9514

examples

• [examples] [bug] Fixed issue in "versioned history" example where using a declarative base
  that is derived from _orm.DeclarativeBase would fail to be mapped.
  Additionally, repaired the given test suite so that the documented
  instructions for running the example using Python unittest now work again.

typing

• [typing] [bug] Fixed typing for _orm.deferred() and _orm.query_expression()
  to work correctly with 2.0 style mappings.

  References: #9536

postgresql

• [postgresql] [bug] Fixed critical regression in PostgreSQL dialects such as asyncpg which rely
  upon explicit casts in SQL in order for datatypes to be passed to the
  driver correctly, where a String datatype would be cast along
  with the exact column length being compared, leading to implicit truncation
  when comparing a VARCHAR of a smaller length to a string of greater
  length regardless of operator in use (e.g. LIKE, MATCH, etc.). The
  PostgreSQL dialect now omits the length from VARCHAR when rendering
  these casts.

  References: #9511

mysql

• [mysql] [bug] Fixed issue where string datatypes such as _sqltypes.CHAR,
  _sqltypes.VARCHAR, _sqltypes.TEXT, as well as binary
  _sqltypes.BLOB, could not be produced with an explicit length of
  zero, which has special meaning for MySQL. Pull request courtesy J. Nick
  Koston.

  References: #9544

misc

• [bug] [util] Implemented missing methods copy and pop in
  OrderedSet class.

  References: #9487

2.0.7

2.0.7

Released: March 18, 2023

sql

• [sql] [bug] Fixed critical SQL caching issue where use of the
  _sql.Operators.op() custom operator function would not produce an appropriate
  cache key, leading to reduce the effectiveness of the SQL cache.

  This change is also backported to: 1.4.47

  References: #9506

typing

• [typing] [bug] Fixed typing issue where _orm.composite() would not allow an
  arbitrary callable as the source of the composite class.

  References: #9502

postgresql

• [postgresql] [usecase] Added new PostgreSQL type _postgresql.CITEXT. Pull request
  courtesy Julian David Rath.

  References: #9416

• [postgresql] [usecase] Modifications to the base PostgreSQL dialect to allow for better integration with the
  sqlalchemy-redshift third party dialect for SQLAlchemy 2.0. Pull request courtesy
  matthewgdv.

  References: #9442

1.4.47

1.4.47

Released: March 18, 2023

sql

• [sql] [bug] Fixed bug / regression where using bindparam() with the same name
  as a column in the Update.values() method of Update, as
  well as the Insert.values() method of Insert in 2.0 only,
  would in some cases silently fail to honor the SQL expression in which the
  parameter were presented, replacing the expression with a new parameter of
  the same name and discarding any other elements of the SQL expression, such
  as SQL functions, etc. The specific case would be statements that were
  constructed against ORM entities rather than plain Table
  instances, but would occur if the statement were invoked with a
  Session or a Connection.

  Update part of the issue was present in both 2.0 and 1.4 and is
  backported to 1.4.

  References: #9075

• [sql] [bug] Fixed stringify for a the CreateSchema and DropSchema
  DDL constructs, which would fail with an AttributeError when
  stringified without a dialect.

  References: #7664

• [sql] [bug] Fixed critical SQL caching issue where use of the
  _sql.Operators.op() custom operator function would not produce an appropriate
  cache key, leading to reduce the effectiveness of the SQL cache.

  References: #9506

mypy

• [mypy] [bug] Adjustments made to the mypy plugin to accommodate for some potential
  changes being made for issue #236 sqlalchemy2-stubs when using SQLAlchemy
  1.4. These changes are being kept in sync within SQLAlchemy 2.0.
  The changes are also backwards compatible with older versions of
  sqlalchemy2-stubs.

• [mypy] [bug] Fixed crash in mypy plugin which could occur on both 1.4 and 2.0 versions
  if a decorator for the _orm.registry.mapped() decorator were used
  that was referenced in an expression with more than two components (e.g.
  @Backend.mapper_registry.mapped). This scenario is now ignored; when
  using the plugin, the decorator expression needs to be two components (i.e.
  @reg.mapped).

  References: #9102

postgresql

• [postgresql] [bug] Added support to the asyncpg dialect to return the cursor.rowcount
  value for SELECT statements when available. While this is not a typical use
  for cursor.rowcount, the other PostgreSQL dialects generally provide
  this value. Pull request courtesy Michael Gorven.

  References: #9048

mysql

• [mysql] [usecase] Added support to MySQL index reflection to correctly reflect the
  mysql_length dictionary, which previously was being ignored.

  References: #9047

mssql

• [mssql] [bug] Fixed bug where a schema name given with brackets, but no dots inside the
  name, for parameters such as _schema.Table.schema would not be
  interpreted within the context of the SQL Server dialect's documented
  behavior of interpreting explicit brackets as token delimiters, first added
  in 1.2 for #2626, when referring to the schema name in reflection
  operations. The original assumption for #2626's behavior was that the
  special interpretation of brackets was only significant if dots were
  present, however in practice, the brackets are not included as part of the
  identifier name for all SQL rendering operations since these are not valid
  characters within regular or delimited identifiers. Pull request courtesy
  Shan.

  References: #9133

oracle

• [oracle] [bug] Added _oracle.ROWID to reflected types as this type may be used in
  a "CREATE TABLE" statement.

  References: #5047