When a search filter arrives empty, why should a composed query omit its predicate instead of adding an always-true condition?

## What a composed statement is A search endpoint usually accepts several **optional** inputs - a status, a date range, a text fragment, an owner - and any subset of them may be present on a given call. A dynamic query builder turns that subset into one statement at runtime. The mechanics are always the same: 1. Start from a **base statement**: the select list, the driving table, and every predicate that is unconditional - tenant scoping, the exclusion of soft-deleted rows, the caller's visibility rules. 2. For each **supplied** input, append one predicate fragment together with the value or values it binds. 3. Join the collected fragments with `AND`, and emit a `WHERE` clause only when the collection is non-empty. 4. Append ordering and paging, both chosen by the code rather than pasted in from the request. The discipline that keeps this honest is that the builder collects **pairs** - a fragment of statement text and the values that fragment binds - never a finished string with the value already inside it. Everything below follows from that shape. ## The rule An input that was not supplied contributes **no predicate**. Not a weaker one, not a disabled one: nothing. When no optional input arrives at all, the emitted statement is the base statement and returns exactly what the base statement returns. That sounds too obvious to state, until you meet the three constructs teams reach for instead. ### The constant-true pad `WHERE 1=1` followed by a run of `AND ...` fragments exists for exactly one reason: it lets every appended fragment start with `AND` without the builder tracking whether it is writing the first one. Most planners fold a constant predicate away, so this is rarely a runtime cost. It is a **signal**, though: a builder that needs the pad is concatenating text, which is the same code path that eventually concatenates a value. Collecting fragments in a list removes the pad and the temptation together. ### The self-comparison pad `WHERE status = status` looks like the same trick with better manners. It is not a no-op. A comparison involving a column that holds no value evaluates to **unknown** rather than true, and rows that evaluate to unknown are not returned. So this pad silently deletes every row whose column is empty. A padding clause that changes the result set is a defect, not a style choice. ### The parameter-driven catch-all `WHERE (? IS NULL OR status = ?)` disables itself when the caller binds nothing. Semantically it does cover both cases. Operationally it is a **different statement**: the predicate's truth now depends on a parameter, so one compiled form has to serve every combination of supplied and absent filters, and the emitted text no longer tells a reader which filters were actually in play. The trade - one shape reused everywhere against many precise shapes - is real, and sometimes worth taking. What is wrong is believing the two are the same statement written more compactly. | Strategy | Emitted text | Result set | Distinct shapes | |---|---|---|---| | Omit the predicate | Only supplied filters appear | Correct | One per supplied subset | | Constant-true pad | Carries a meaningless clause | Correct | One per supplied subset | | Self-comparison pad | Carries a real comparison | Drops rows holding no value | One per supplied subset | | Parameter catch-all | Every filter always present | Correct | One, covering all combinations | ## Missing, empty, and a value are three states Most transport encodings blur these. A field absent from a request body, a field present with an explicit no-value marker, and a field present as an empty string are three different messages, and a careless builder collapses them into one. - **Not sent** - add no predicate. - **Sent as an explicit no-value** - add a predicate testing the column for the absence of a value (`owner_id IS NULL`). This is a real filter, and it is the only way a caller can ask for *unassigned* rows. - **Sent with a value** - the ordinary comparison, with the value bound. Collapse the first two and *rows with no owner* becomes unreachable through the API. Treat an empty string as a value and every list request from a form with blank boxes returns nothing. ## Empty collections and half-open ranges Two cases sit at the same edge. A set filter supplied as an **empty collection** cannot be emitted as an empty in-list, which is not valid standard SQL; the builder must either treat it as *no filter* or short-circuit the whole query to *matches nothing*. Both readings are defensible. The bug is letting the meaning depend on which branch of the builder happened to run. A **range** filter with only one bound supplied emits one comparison, not two - the missing bound is an absent filter like any other, and inventing a sentinel bound for it is the constant-true pad in numeric clothing. ## How to test it - Assert the **emitted text and the bind list** for: no filters, each filter alone, and all filters together. - Cover the empty-string and explicit-no-value cases as separate assertions, because they take different branches. - Add one end-to-end check that the no-filter call returns the same rows as the base statement, which catches a pad that quietly filters, and assert that the unconditional predicates survive every combination.