db: tenant filter adds the nullable side of a LEFT OUTER JOIN to WHERE when its column is only inside an aggregate (func.count(Child.id)) #417

Summary

When a tenant-scoped model is the nullable side of a LEFT OUTER JOIN and one of its columns is referenced only inside a SQL function (func.count(Child.id), func.sum(...), func.max(...), ...), the tenant filter puts child.tenant_id = :t in the statement's WHERE as well as in the ON clause. The WHERE predicate is false for every row the outer join padded with NULLs, so the outer join silently becomes an inner join. The canonical "list parents with a usage count, including unused ones" query loses exactly the rows it exists to show.

Reproduced on the 0.0.35 release and on main at a0087f4, SQLAlchemy 2.0.51, aiosqlite in-memory.

Observed

Two MultiTenantMixin models, Tag and ArticleTag (no relationship needed). Tenant t1 has tags used (one ArticleTag row) and unused (none). With tenant_context("t1"):

select(Tag, func.count(ArticleTag.article_id))
    .outerjoin(ArticleTag, ArticleTag.tag_id == Tag.id)
    .group_by(Tag.id)

emits

SELECT r_tag.tenant_id, r_tag.id, r_tag.name, count(r_article_tag.article_id) AS count_1
FROM r_tag LEFT OUTER JOIN r_article_tag
  ON r_article_tag.tag_id = r_tag.id AND r_article_tag.tenant_id = ?
WHERE r_article_tag.tenant_id = ? AND r_tag.tenant_id = ?
GROUP BY r_tag.id ORDER BY r_tag.name
-- rows: [('used', 1)]          expected: [('unused', 0), ('used', 1)]

The same happens with select(Tag.name, func.count(ArticleTag.article_id)).select_from(Tag).outerjoin(...).

Shapes that are filtered correctly (predicate only in ON, unused tag kept):

So the trigger is not the outer join itself (with_loader_criteria correctly lands in ON); it is a column of the outer-joined table appearing inside a function in the columns clause.

Cause

framework/db/simple_module_db/query_filter.py:

• _plain_tables (L170-182) collects candidates from stmt.columns_clause_froms plus stmt._from_obj, and registry.is_plain_table (model_registry.py L52-56) treats any table without a parententity annotation as a plain Core Model.__table__ reference.

• A column wrapped in func.count(...) loses its ORM annotation: columns_clause_froms yields AnnotatedTable r_tag (annotated) but plain Table r_article_tag (not annotated). Checked directly:

  select(Tag, func.count(ArticleTag.article_id)).outerjoin(ArticleTag, ...).columns_clause_froms
    AnnotatedTable r_tag          parententity=True
    Table          r_article_tag  parententity=False   <- treated as a Core table
  select(Tag, ArticleTag.article_id).outerjoin(ArticleTag, ...).columns_clause_froms
    AnnotatedTable r_tag          parententity=True
    AnnotatedTable r_article_tag  parententity=True
  

• _tenant_criteria (L221-222) then appends table.c.tenant_id == tenant_id to WHERE for it. _where_able (L185-200) is meant to keep the nullable side of an outer join out of WHERE, but it only sees Join objects in the list it is handed. A join built with .outerjoin() lives in stmt._setup_joins, not in _from_obj or columns_clause_froms, so the table arrives as a bare entry and is never recognised as the right side of an outer join.

_soft_delete_criteria (L138-144) uses the same _plain_tables path, so a SoftDeleteMixin child in the same position presumably gets is_deleted = false in WHERE too (not separately verified).

This is distinct from #332, which was about filters being skipped; this one over-filters, and comes from the _plain_tables path that #332's fix (and #370) added.

Expected

Criteria for an outer-joined entity belong only in its ON clause, which with_loader_criteria already produces. A table that is reachable through the statement's ORM entities or joins should not be re-filtered as a Core table in WHERE. Concretely, either:

1. In _plain_tables, skip a table that is the target of any outer join in stmt._setup_joins (and of any outer Join in _from_obj), or
2. Only treat a table as "plain" when no ORM entity in the statement maps it, i.e. when none of execute_state.all_mappers / the statement's entities has it as mapper.local_table. An un-annotated reference to a table whose mapper is already in the statement is covered by the loader criteria.

A regression test with the four shapes above (parent kept with count 0) would pin it.

Why a module cannot work around it

It can only avoid the shape: news rewrote list_tags as a correlated scalar subquery (select(NewsTag, select(func.count(...)).where(...).correlate(NewsTag).scalar_subquery())). Nothing fails loudly; the listing just drops rows, so the next module that writes the obvious outer-join-plus-count will hit it again, on Postgres and SQLite alike.

Context

Found while making the news module multi-tenant: antosubash/smpy_modules#57. The original query is modules/news/news/tag_service.py list_tags at antosubash/smpy_modules@c03a665; adding MultiTenantMixin to NewsArticleTag made every unused tag disappear from the tags screen. The repro script is standalone (four MultiTenantMixin models, init_db("sqlite+aiosqlite:///:memory:"), register_listeners, tenant_context).

https://claude.ai/code/session_017uTbtobjCKQYnAxF5tgK8t