A filter that reaches through a one-to-many link adds a join - how can that change the rows the query returns?

## A filter that only constrains can still change the row count The intuition that trips people is that a filter *removes* rows. A predicate in the where clause does. But a filter written against a **related collection** is not only a predicate - it usually brings a join with it, and a join to the many side of a relationship changes the **grain** of the result before any predicate runs. ```sql SELECT o.id, o.placed_at FROM orders o JOIN order_lines l ON l.order_id = o.id WHERE l.amount > 100 ``` An order with three lines above 100 appears three times. Nothing from `order_lines` is selected; the duplication is a property of the join, not of the select list. A hundred orders can return a hundred and thirty rows, and only the orders with exactly one match are unaffected. This is why a *filters-only* change to a composed query can alter a result set in a way that looks impossible: the caller added a constraint and got **more** rows back. ## Two filters, two possible meanings When two optional filters both reach through the same collection, the builder faces a genuine ambiguity that only the product requirement can settle: - **One child satisfies both.** *Orders with a line that is both over 100 and marked as a gift.* One join, two predicates on it. - **Two children, one each.** *Orders that have some line over 100 and some line marked as a gift.* Two independent joins, or two independent subqueries. A builder that blindly reuses the first join it created silently picks the first reading. A builder that blindly adds a join per fragment silently picks the second, and multiplies rows twice over. Neither default is wrong in general; defaulting without recording the decision is. ## Ways to keep one row per entity | Technique | What it does | What it costs | |---|---|---| | `DISTINCT` over the select list | Collapses the duplicate pairs after the join | Adds a sort or hash step; breaks down if you also order by a child column | | `EXISTS` subquery per filter | Tests for a matching child without changing the grain | One correlated test per filter; the planner may still rewrite it | | Join, then group by the parent key | Lets the query express *how many* children matched | Forces a grouping step and reshapes the select list | For a filter whose only job is *constrain the parent*, the exists form is usually the honest expression: each optional filter contributes one self-contained fragment, fragments never interact, the grain stays one row per parent, and the two-filters ambiguity disappears because each subquery quantifies over the collection independently. The join form earns its place when the child column is also needed - to order by it, or to return it - and then the duplication has to be handled explicitly rather than discovered. ## What breaks around it 1. **Paging.** A row limit applies to output rows. After a multiplying join, a page of twenty holds fewer than twenty distinct parents, and the same parent can straddle a page boundary. Applying `DISTINCT` and a limit together does not reliably fix this either, because the limit is still applied to whatever grain survives. 2. **Counts.** A total for the pager built as a plain row count reports pairs. A distinct count of the parent key, or the same filter expressed with exists, reports entities. 3. **Aggregates.** Any sum over a parent column after a multiplying join is inflated by the match count, and it is inflated silently. 4. **Ordering.** Ordering by a child column while returning one row per parent is not well defined until you say *which* child - typically a minimum or maximum computed per parent. ## What the builder must own A composed builder that can reach through relationships needs a small amount of bookkeeping that a flat one does not: - A **registry of joins already added**, keyed by the relationship path *and* by whether the fragment wants to share a child with another fragment. Fragments ask the registry for a join rather than appending one. - A per-filter declaration of whether it is *constraining only* (exists form) or *projecting* (join form), so the choice is data rather than an accident of ordering. - A rule that the pager's count is derived from the same fragment set as the page query, by the same mechanism, so the two cannot disagree. - Tests that assert **row identity**, not just row content: for a fixture where a parent has several matching children, assert that the parent appears exactly once and that the reported total is the entity count. The general principle is worth stating plainly: in a composed query, a filter is not guaranteed to be grain-preserving, and whether it is depends on the path it reaches through. Treat *does this fragment change the grain* as a property every fragment must declare.