There is a category of bug I like more than all the others: the ones that throw no exception, reach no log, and survive in production for weeks because the system technically works.
This is one of them.
The symptom
On an e-commerce site, a customer searches for “chianti” and finds nothing. They search for “Chianti” and find nine products. No error, no support ticket: whoever typed lowercase simply concludes the product is not stocked, and leaves.
The cause
The code was this:
query = query.Where(p => p.Name.Contains(term));It works on SQL Server, whose default collations are case-insensitive. It does not work on
PostgreSQL: Contains compiles to a LIKE, and LIKE in PostgreSQL is case-sensitive. The
exact same C# has two different behaviours depending on the database underneath, and neither one
announces itself.
The fix
PostgreSQL has ILIKE, which Npgsql exposes as EF.Functions.ILike. Tokenising by word as well, so
that “red wine” matches “Rosso di Montalcino — still red wine”:
foreach (var token in SearchTextUtil.Tokenize(term))
{
var pattern = $"%{SearchTextUtil.EscapeLikePattern(token)}%";
query = query.Where(p => EF.Functions.ILike(p.Name, pattern)
|| EF.Functions.ILike(p.Description, pattern));
}Two details that are easy to skip:
Escape the user’s wildcards. In a LIKE pattern, % and _ are special. A search for “100%”
without escaping becomes a pattern that matches everything. It is not a security problem — the values
are still parameterised — but it is a wrong answer.
No index, for now. An ILIKE '%…%' cannot use a B-tree index. Across a few dozen products a
sequential scan costs less than maintaining a pg_trgm index would. Approaching a thousand rows the
calculation changes — and that is the moment to redo it, not before.
The collateral damage
The part that struck me was not the search itself: it was the statistics. The system logged every search with its result count, and I used that data to work out which products were missing from the catalogue. Half of the “no results” searches were not unmet demand at all: they were the same bug, quietly advising me to buy stock I already had.
A silent bug does not only cost you the feature it breaks. It costs you every decision made while looking at the data it poisoned.