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:

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.

Source: SQLite FTS5 quietly ANDs your search terms by Chad Priest, from Building Vodou in Public.