skip to content

A SQLAlchemy Core report logs 'SELECT statement has a cartesian product between FROM element(s)' and its counts are inflated; what causes it and how do you fix it?

level: seniorimportance: should knowfreq 28%

answer

  1. implicit FROM list
  2. the comma in FROM
  3. a table used but never joined
  4. alias versus base table
  5. linting at compile time

basics

~20 s

A table reached the FROM clause through a column or filter but was never joined, so the database pairs every row with every row. SQLAlchemy's FROM linter flags it at compilation; join the table with an ON condition, or use the alias you meant.

solid answer

~40 s

SQLAlchemy builds the FROM list implicitly: any table whose column appears in the columns, `where()`, `order_by()` or `group_by()` is added. A table that is not part of a JOIN is rendered after a comma, as in `FROM customers, orders`, and when no WHERE condition links it either, the database returns every combination, so `count()` and `sum()` are multiplied. The FROM linter, on by default through `create_engine(enable_from_linting=True)`, finds those unlinked elements when the statement is compiled and emits an `SAWarning`. Usual causes: filtering on `orders.c.status` without joining `orders`, joining `orders.alias("o2")` but filtering on `orders`, or selecting a subquery's columns without joining it. Fix the join with `join_from(customers, orders)` or an explicit ON clause; `distinct()` and `enable_from_linting=False` only hide it. The check runs once per cached compilation, so turn `SAWarning` into an error in tests.

code

python · 42 lines
python
import warnings

from sqlalchemy import (Column, ForeignKey, Integer, MetaData, String, Table,
                        create_engine, exc, func, select)

metadata = MetaData()
customers = Table(
    "customers", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String(100), nullable=False),
)
orders = Table(
    "orders", metadata,
    Column("id", Integer, primary_key=True),
    Column("customer_id", ForeignKey("customers.id"), nullable=False),
    Column("status", String(20), nullable=False),
)

engine = create_engine("sqlite://")  # enable_from_linting defaults to True
metadata.create_all(engine)
warnings.simplefilter("error", exc.SAWarning)  # make the linter fail loudly

paid = (
    select(customers.c.name, func.count().label("paid_orders"))
    .where(orders.c.status == "PAID")          # orders is never joined
    .group_by(customers.c.id, customers.c.name)
)
print(paid)
with engine.begin() as conn:
    conn.execute(customers.insert(), [{"id": 1, "name": "Ada"}, {"id": 2, "name": "Bo"}])
    conn.execute(orders.insert(), [
        {"customer_id": 1, "status": "PAID"},
        {"customer_id": 1, "status": "PAID"},
        {"customer_id": 2, "status": "PAID"},
    ])
    try:
        conn.execute(paid)
    except exc.SAWarning as warning:
        print(warning)
    fixed = paid.join_from(customers, orders)
    print(fixed)
    print(conn.execute(fixed.order_by(customers.c.id)).all())

go deeper

for a junior

Recall that a column from a table you never joined still adds that table to FROM, and a comma in FROM is a warning sign.

for a middle

Explain the implicit FROM model, what the FROM linter checks, and why an alias plus its base table produces two FROM elements.

for a senior

Diagnose inflated report numbers from the warning or the FROM line, fix the join rather than masking it, and make SAWarning fail the tests.

for a principal

Make warnings-as-errors and executed query tests a team norm so linter findings block merges instead of surfacing once in production logs.

## How the FROM list is built SQLAlchemy's Core expression language uses an **implicit FROM** model. You rarely list tables yourself: every table, alias or subquery whose columns appear anywhere in the statement, in the columns clause, `where()`, `order_by()`, `group_by()` or `having()`, is added to the FROM clause automatically. Tables connected by `join()` are rendered as one JOIN expression. Anything else is rendered as a separate FROM element after a comma. A comma between FROM elements with no condition linking them is a **cartesian product**: the database pairs every row of one with every row of the other. In a report, aggregates are multiplied. With two customers and three paid orders, a per-customer count of paid orders that never joins `orders` returns 3 for each customer instead of 2 and 1. ## The FROM linter SQLAlchemy ships a **FROM linter** inside its SQL compiler. `create_engine()` enables it by default through `enable_from_linting=True`. When a SELECT is compiled for execution, the linter walks the JOIN conditions and the WHERE clause, builds a graph of which FROM elements are linked, and warns if the graph is not connected: `SAWarning: SELECT statement has a cartesian product between FROM element(s) "customers" and FROM element "orders". Apply join condition(s) between each element to resolve.` Three properties make this warning easy to miss: 1. **It fires at compilation, not when the statement is built.** `print(stmt)` uses a generic dialect without linting, so the comma appears with no warning. 2. **It fires once per compiled form.** A statement served from the compiled cache is not linted again, so a log shows the warning once after start-up and never again. 3. **It is a warning.** Execution continues and the inflated rows are returned. The message is built from a graph walk: SQLAlchemy starts at one FROM element, follows every JOIN and WHERE link it can, and lists the elements it could not reach. The names in the first quoted group are the unreachable elements, and the last quoted name is where the walk started. Fixing the join for each unreachable element clears the warning. An implicit join written as a WHERE condition, such as `where(customers.c.id == orders.c.customer_id)`, links the two elements, so the linter stays quiet even though the SQL still contains a comma. ## Common causes - **A filter on a table that was never joined.** `select(customers.c.name, func.count()).where(orders.c.status == "PAID")` adds `orders` to FROM through the filter alone. - **Joining an alias but referring to the base table.** After `join(o2, o2.c.customer_id == customers.c.id)` with `o2 = orders.alias("o2")`, a `where(orders.c.status == "PAID")` adds the base `orders` as a separate element: `FROM customers JOIN orders AS o2 ON ..., orders`. - **Selecting a subquery's or CTE's columns without joining it.** `select(customers.c.name, rev.c.total)` with no `join(rev, ...)` renders `FROM customers, (...) AS rev`. - **The same mistake one level down.** A subquery whose own FROM list is unlinked is checked too, and the warning names the tables inside it. ## Fixing it | Response | Effect | |---|---| | `join()` or `join_from()` with the right ON clause | removes the product; counts become correct | | use the alias's columns (`o2.c.status`) consistently | removes the stray base table | | `distinct()` | hides duplicate rows; aggregates stay inflated | | `enable_from_linting=False` | silences the warning; the SQL is unchanged | For the paid-orders count, `paid.join_from(customers, orders)` renders `FROM customers JOIN orders ON customers.id = orders.customer_id` and the counts become 2 and 1. ## Catching it before production Because the warning appears once and does not stop execution, rely on tests rather than logs: - Configure Python's warnings filter so `sqlalchemy.exc.SAWarning` is raised as an error in the test suite, for example `warnings.simplefilter("error", exc.SAWarning)`. Every report query that compiles with a cartesian product then fails its test. - Execute each report statement in a test at least once, so it is actually compiled with the linter on. A statement that is only printed or built is never linted. - When reviewing SQL from `print(stmt)`, read the FROM line for a comma; with explicit joins there should be none.

  • Why did the cartesian-product warning appear once in the log and never again, although the report runs every minute?
    The linter runs during compilation, and SQLAlchemy caches the compiled form of each statement shape. After the first execution the statement is served from the cache and is not linted again, so one warning is all you get. The rows stay inflated on every run; only the warning stops.
  • Why does `where(customers.c.id == orders.c.customer_id)` not trigger the linter, even though FROM has a comma?
    The linter checks whether FROM elements are connected by any condition, from JOIN ON clauses or the WHERE clause. That equality links `customers` and `orders`, so the graph is connected and no warning fires. It is an implicit join, legal SQL, though `join()` makes the relationship clearer in review.

  • The warning is cosmetic; the database ignores the extra table
  • Adding distinct() fixes the inflated counts
  • print(stmt) would have shown the warning during development
  • Setting enable_from_linting=False resolves the cartesian product
  • SQLAlchemy raises an error for any FROM list containing a comma