Session State Leaked Through a WHERE Clause Made Inventory Queries Flake Out
Functions with side effects inside a WHERE clause create a dependency on evaluation order that SQL engines do not guarantee. Combined with connection pooling, the resulting bugs are intermittent and nearly impossible to reproduce without methodical connection-lifecycle testing.
An inventory batch lookup would sometimes return rows and sometimes return nothing, despite identical code and data. The query placed two functions in the WHERE clause: one to write a batch number into session state via `set_config`, and another to read it back via `current_setting`. The writer assumed the setter would run first, but the getter appeared earlier in the clause and executed first on a fresh connection, reading an empty variable.
Worse, connection pooling masked the bug. A reused connection that had already run a prior query with the same batch number would carry stale session state, making the broken query appear to work. Only by disconnecting and reconnecting could the empty-result failure be reproduced reliably. The execution plan confirmed that both conditions were folded into a single filter expression, but the plan alone cannot prove the database will always honor the written order.
The fix separates the state-setting call from the read query into two distinct statements. A more durable solution eliminates session-variable passing entirely and passes the batch number as a direct parameter in the WHERE clause.
The bug survived in production because connection pooling turned it into a probabilistic failure — the same broken SQL would succeed or fail depending on which connection the pool handed out, making it look like a caching or data issue.
The execution plan showing conditions in written order is a red herring: it describes what happened on that run, not what the optimizer is allowed to do. Trusting it as proof of guaranteed order is a common diagnostic mistake.
The deeper design smell is using session-scoped variables to pass parameters that were already available in application memory. The session state was a workaround that introduced coupling between queries and connection lifecycle.