SQLite FTS5 quietly ANDs your search terms

Someone asked my search box a normal human question and got back {"results":[]}.

Not an error. Not a timeout. An empty array, in 8ms, with a 200. The corpus it searched has 52,058 chunks in it and definitely contains the answer. I know it does, because a two-word version of the same question returned twenty hits.

I spent the first twenty minutes assuming this was a ranking problem, because that’s the interesting kind of problem and I wanted to have it. It was not a ranking problem. It was one space character.

The symptom: results decay to zero as the query gets longer

Here’s the thing that made it obvious, once I stopped theorizing and just ran the same query at increasing lengths:

Query	Hits
daemon	1,521
daemon socket	162
daemon socket memory	153
daemon socket memory search	27
daemon socket memory search Tuesday	0

Monotonic decay. Every word you add can only ever remove results. That is not what relevance looks like: relevance is noisy, it goes up and down. A clean staircase to zero is a set intersection, and set intersections come from AND.

The cause: space-joined tokens are an implicit AND

The query builder was doing something that reads as completely reasonable:

const tokens = q.split(/\s+/)
  .map(t => t.replace(/[^\w'-]/g, ''))
  .filter(Boolean)
  .map(t => `"${t}"`);

// ...
WHERE memory_fts MATCH ?   // tokens.join(' ')

Split the query, strip punctuation so it can’t blow up the parser, quote each token so a stray OR or NEAR in user text is treated as a literal, join it back together. Four defensive decisions, all individually correct.

The problem is the last one. In FTS5, whitespace between two terms is the implicit AND operator. "daemon" "socket" doesn’t mean “look for these two words.” It means “return only rows containing both.” That’s documented FTS5 behavior and it’s the right default for a query language. It is an absolutely terrible default for a search box, because a search box receives sentences.

Do the math on a real query. Someone types:

I was thinking about the deploy pipeline yesterday and also need to remember to renew the domain on Tuesday, plus what was that socket timeout we hit, and can you check the graph frontend branch

That’s 33 tokens. For that to return anything, one single document has to contain all 33 of those words. deploy and domain and socket and branch, in one chunk. No such document exists in any corpus, which means this query returns zero results, and so does every other query of that shape. The failure rate isn’t “sometimes”: it’s 100% of prose queries, forever, with no error message.

The reason it survived so long is that it works beautifully for the case you test it on. You type daemon and get results. You type dogs name and get one stray hit, and you assume the data is missing.

The second bug hiding under the first: dogs is not dog's

While measuring the above, I hit a separate cliff:

dog          → 94 hits
dog's name   → 23 hits
dogs name    →  1 hit

The sanitizer keeps apostrophes ([^\w'-]), which is deliberate: you want don't to survive as one token. But that means dogs and dog's are two different tokens, and only one of them is meaningfully in the index. Nobody types the apostrophe. Every real user query is the broken spelling.

Two independent near-zero paths, both invisible, both looking exactly like “we never stored that.”

The debugging lesson: measure the shape, not the instance

The thing that actually cracked this wasn’t reading code. I read that code twice and it looked fine both times, because each line is fine.

What cracked it was running the query five times at five lengths and looking at the shape of the curve. A ranking bug produces noise. A tokenizer bug produces a cliff. An AND bug produces a staircase. You can identify the class of failure from the shape before you know anything about the cause, and that tells you which file to open.

I’d generalize it like this: when a query returns nothing, don’t debug the query: debug the family it belongs to. Vary one dimension (length, casing, punctuation, term rarity) and plot it. Five curl calls got me to the right line faster than an hour of reading would have.

The fix: tokens.join(’ OR ’) alone trades zero hits for garbage

The obvious patch is tokens.join(' OR '). Don’t ship only that: you’ll trade zero results for garbage, because now a stopword like the pulls in half the corpus and the top hit is whatever document says “the” the most.

What you want is a tiered fallback:

// 1. Try AND — if the user typed 2-3 precise terms, this is the best answer.
let rows = search(tokens.join(' '));

// 2. Zero hits? Fall back to OR, but cap tokens and drop stopwords first,
//    so ranking has something to work with instead of everything.
if (rows.length === 0) {
  const terms = tokens.filter(t => !STOP.has(t)).slice(0, 8);
  rows = search(terms.join(' OR '));
}

AND-first preserves precision for the short queries where precision is what you want. OR-fallback means a sentence degrades into worse ranking instead of no results. Those two failure modes feel completely different to a user: one is a search engine having an off day, the other is a broken product.

And normalize possessives at index and query time both, or you will keep shipping the dogs bug in new places.

Zero results as the query grows means intersection, not search

If a search interface returns zero results more often as the input gets longer, it is doing set intersection and calling it search.

Any search box wired directly to MATCH has this bug. It will pass every test you write, because you write tests with keywords, and it will fail every query a human types, because humans type sentences. Go paste a full sentence into your own search box right now. If you get an empty state, you know what to look for.