On SQLAlchemy 2.1.0 or 2.1.1, Fixing an N+1 Could Change Your Results

The standard fix for an N+1 query in SQLAlchemy is to eager load the relationship with selectinload(). On SQLAlchemy 2.1.0 and 2.1.1, that fix can change your results.

A regression in those two releases makes selectinload() ignore part of the join condition on some many-to-many relationships. The query gets faster, and it also returns related rows that the relationship was defined to exclude. It’s fixed in 2.1.2, released October 2.

What Broke

SQLAlchemy 2.1.0 added a performance optimization for selectinload() on many-to-many relationships (ticket #5987). Instead of joining back to the parent table in the second SELECT, it selects from the association table directly, using an option called omit_join.

That’s a good optimization when the relationship is a plain many-to-many: parent primary key to association table, association table to child. The problem was relationships with extra conditions in primaryjoin, such as a filter on a column of the association table or the parent table. In 2.1.0 and 2.1.1, selectinload() used omit_join for those too and dropped the extra conditions (ticket #13626).

What an Affected Relationship Looks Like

Here’s the kind of relationship the changelog describes, with a condition on the association table:

membership = Table(
    "membership",
    Base.metadata,
    Column("user_id", ForeignKey("user.id"), primary_key=True),
    Column("group_id", ForeignKey("group.id"), primary_key=True),
    Column("active", Boolean, nullable=False),
)

class User(Base):
    __tablename__ = "user"
    id: Mapped[int] = mapped_column(primary_key=True)

    active_groups: Mapped[list["Group"]] = relationship(
        secondary=membership,
        primaryjoin=lambda: and_(
            User.id == membership.c.user_id,
            membership.c.active.is_(True),
        ),
        secondaryjoin=lambda: Group.id == membership.c.group_id,
        viewonly=True,
    )

The relationship is meant to return only groups where the membership is active. Now load it the way you would after spotting an N+1:

users = session.scalars(
    select(User).options(selectinload(User.active_groups))
).all()

We ran this against a user with one active and one inactive membership. Same model, same data:

On 2.1.1, selectinload() drops the active condition and returns the group from the inactive membership too.

This is what makes it easy to miss. The N+1 goes away and the endpoint gets faster. Nothing errors. The only sign is that a list contains rows it shouldn’t. In this example, that’s a user showing up in groups they’ve left.

Are You Affected?

All of these have to be true:

1. You’re on SQLAlchemy 2.1.0 or 2.1.1. The 2.0 series doesn’t have the omit_join change.
2. You have a many-to-many relationship (one with secondary=).
3. Its primaryjoin includes conditions beyond matching the parent’s primary key to the association table, such as a column comparison on the association table or the parent.
4. You load it with selectinload().

To find candidates, search your models for relationships that set both secondary= and primaryjoin=, then check whether any code path loads them with selectinload().

The Fix

Upgrade to 2.1.2 or later. The latest release is 2.1.3. In 2.1.2, omit_join is only used for many-to-many relationships whose primaryjoin consists solely of comparisons between the parent’s primary key columns and the association table. Everything else goes back to the full join.

If you can’t upgrade right away, turn the optimization off for the affected relationship. omit_join has always been controllable per relationship:

active_groups: Mapped[list["Group"]] = relationship(
    secondary=membership,
    primaryjoin=...,
    secondaryjoin=...,
    viewonly=True,
    omit_join=False,
)

We confirmed this returns the correct rows on 2.1.1. Once you’ve upgraded, you can remove it, since 2.1.2 makes the right choice automatically.

Two More Reasons to Take 2.1.2

The same release fixes two other 2.1 regressions worth knowing about:

• Unbounded memory growth with Enum and DOMAIN types (#13625). Dialect-specific copies of SchemaType types registered themselves with MetaData, and a new copy is made for each new Dialect instance, for example each time str() is called on a statement that uses an Enum column. The collection grew without limit.
• TypeError: Expected tuple with the Cython extensions (#13619) when a driver returns rows as a subclass of tuple. The changelog names the Databricks SQL connector as one such driver.

Seeing This in Production

The tricky part of this bug is that the usual signal for “my query changed” is a performance change, and here performance gets better. Watch for both sides when you fix an N+1: that the query count dropped, and that the results didn’t change.

Scout Monitoring detects N+1 query patterns in SQLAlchemy automatically and shows the exact code location, so you know which relationships to eager load in the first place. After you change a loader strategy, the endpoint’s trace shows every query it now runs, which makes it easy to review what the change actually did.

Try Scout Monitoring free. No credit card needed.