Spider 2.0 deleted a claim I made this morning

title: Spider 2.0 deleted a claim I made this morning
published: true

tags: sql, ai, database, python

This morning I wrote that swapping the hashed vectoriser in my schema
retriever for a sentence-transformer was a consistent win. I had numbers:
on a pooled Spider 1.0 catalog, strict recall at k=10 went from 82.6% to
88.1%. Consistent, I said. Larger than my own benchmarks suggested.

This evening I ran the same comparison on Spider 2.0 and the effect vanished.

What follows is the run, what I think happened, and the experiment that would
actually settle it, which I have not done yet.

Spider 2.0 is the benchmark this problem needed

Spider 1.0's databases have a median of three tables. That is why my
per-database numbers there are 100% and worthless: returning the entire schema
also scores 100%. I had to invent a pooled variant — every database merged into
one 876-table catalog — to make the task resemble retrieval at all, and an
invented variant is exactly the kind of thing a reader is right to discount.

Spider 2.0-lite needs no such construction. Measured from the distribution:
162 databases, 7,892 tables, a median of 15 tables per database and a maximum
of 785, drawn from real BigQuery and Snowflake warehouses. The databases are
already big.

spider2-lite ships 547 examples, but only the questions whose gold SQL is
public are usable — the rest is held out — which leaves 158 across 103
databases. Table recall on those:

top_k	all gold tables present	per-table recall
5	70.9%	81.7%
10	82.9%	88.5%
20	86.1%	90.5%

Nothing was tuned for this. I downloaded it, pointed the same code at it, and
those are the numbers.

With the caveat the sample size demands. n=158 gives a 95% confidence
interval of roughly [63.4, 77.4] on that 70.9%, and [76.3, 88.0] on the 82.9%.
So the honest comparison with my Spider 1.0 pooled figures is not "within a
point or two" — the intervals are too wide to resolve a point or two. It is
that the two are indistinguishable at this sample size, on databases an order
of magnitude larger and questions written to be hard. That is still the
interesting result. It is just a weaker sentence than the one I wanted to
write.

The claim that died

Spider 1.0 pooled, n=1,034, strict recall at k=10: hashed 82.6%, sentence
model 88.1%. That is the number I quoted this morning.

Spider 2.0-lite, n=158:

top_k	hashed	sentence model
5	70.9%	71.5%
10	82.9%	82.3%
20	86.1%	85.4%

Better at one k, worse at two, and here is the discipline I failed to apply to
my own good news this morning: every one of those differences is a single
question. 112 versus 113. 131 versus 130. I cannot call that a regression any
more than I could have called it a win. The correct statement is that there is
no measurable difference at n=158.

That still kills "consistent win," which is what I said and what was wrong.

The Spider 1.0 effect, meanwhile, is real: at n=1,034 the intervals around
82.6% and 88.1% do not overlap. So the finding is not "the embedder does
nothing." It is measurably useful on one benchmark and unmeasurable on the
other, and the reason matters.

What I think is going on

Spider 2.0's tables carry real descriptions, harvested from the warehouses'
own data dictionaries. Spider 1.0's tables carry none — just identifiers.

When a table has prose describing it, lexical scoring over that prose already
closes the gap between the words a user types and the words a schema uses. A
question about "revenue" finds a table whose description says revenue, without
any embedding involved. The sentence model was earning its keep on Spider 1.0
by compensating for the absence of that text. Give the corpus the text and
there is less left to compensate for.

This is a hypothesis fitted to two data points and I am labelling it as such.

It is also the same finding as the one before it

A few days ago I wrote up the opposite-looking result: adding LLM-generated
table descriptions to a 1,245-object schema made retrieval worse, because
generated prose inflated the document frequency of the domain's own nouns until
"contact" scored an IDF of 0.15 and the contacts table fell out of the top 40.

Those two results look unrelated. They are the same axis:

The value of any semantic layer — a learned encoder, or generated
descriptions — depends on how much prose the schema already has. Where prose
exists, lexical scoring over it does most of the work. Where it doesn't, you
need something to bridge the vocabulary gap. And if you add prose to the same
field as the identifiers, you damage the identifiers.

Which gives a practical rule I can actually stand behind:

• Bare schema, no comments — the common case, and the worst one. A sentence model helps. So do generated descriptions, in their own field.
• Schema with a real data dictionary — you already have the prose. Expect indexing cost from an encoder and little else. Spend the effort on keeping the fields separate instead.

The experiment that would settle it

Two benchmarks differing in everything is a correlation, not a mechanism. The
controlled version is one line of setup: strip the descriptions out of Spider
2.0 and re-run the same comparison on the same questions and the same
databases.

Spider 2.0-lite, k=10	hashed	sentence model
with data-dictionary prose	82.9%	82.3%
descriptions removed	?	?

If the encoder's advantage reappears once the prose is gone, the hypothesis is
confirmed inside one corpus with everything else held constant. If it doesn't,
the explanation is something else — dialect, question style, table size — and I
should stop telling this story.

I'll run it and post the row either way.

The bug that only real data finds

Spider 2.0 carries description as a list for some tables. That crashed
indexing with:

TypeError: sequence item 2: expected str instance, list found

Naming neither the object nor the field. A catalog of 800 tables was
unindexable because one of them described itself in a list instead of a string.

Fixed at the boundary rather than defensively at each reader — ObjectDoc
normalises description, hint and column comments once on construction,
and None stays None.

That bug would have hit anyone pointing this at a real data dictionary. It did
not show up across six schemas I wrote myself, my own test suite, or a live run
against a 127-object Oracle database, because I built all of those and I would
never have thought to put a list there. It took ninety seconds to find on
someone else's data.

Which is the actual argument for running public benchmarks, more than any
number in the tables above. They are not there to prove you are good. They are
there to be data you did not shape.

---

The library is schemagate,
Apache-2.0, currently 0.1.48. The benchmark scripts are in benchmarks/ and
the data comes from the original sources.
BENCHMARKS.md
carries Spider, BIRD and Spider 2.0, and quotes both embedder results rather
than the flattering one.

Comments

[Raknaos]

Same-day retraction with the numbers left visible is rarer than it should be — most benchmark posts never correct their morning claim at all. And the discipline detail is the right one: 82.9% vs 82.3% is one question at n=158, so refusing to say "slightly worse" is the same discipline you wish you'd applied to the morning's good news.

The prose-compensation hypothesis matches what I keep seeing on real warehouses: where tables carry actual data-dictionary prose, lexical scoring over that prose does most of the retrieval work and an encoder earns little, while a bare identifier-only schema is where the sentence model earns its keep. That makes your strip-the-descriptions-and-rerun experiment the only row that matters — it isolates the mechanism inside one corpus. Bonus points for the list-typed description crash; boundary normalization beats defensive casts at every reader. Have you run the stripped version yet, or is the ? row still pending?

[Ashish sinha]

Ran it a few hours ago. The ? row is filled, and it refuted the hypothesis.

n=158 in all four cells, and the two with-prose cells reproduce the published numbers exactly, so the comparison is clean. Stripping the descriptions out of Spider 2.0 and re-running, strict recall at k=10: hashed 82.9% -> 85.4%, MiniLM 82.3% -> 86.7%.

MiniLM's advantage over hashed goes from -1 question to +2. Spider 1.0's gap was about +9 questions. So it does not reappear at anything like that magnitude, and prose is not what explains the Spider 1.0 vs 2.0 difference. I don't yet know what does — dialect, question phrasing, table size, or per-database versus pooled — and I'm not going to replace one guess with another in the document.

The part I didn't predict is the more interesting one. I expected removing prose to lower the hashed number, since BM25 loses a channel. It rose, at every cut, for both embedders — six cells out of six in the same direction. Those descriptions weren't failing to help, they were costing recall.

The coverage measurement says why: Spider 1.0 has 0/876 tables described, BIRD 0/75, Spider 2.0 4,338/7,892 — with a median description of 155 words, a 90th percentile of 7,939 and a maximum of 16,537. That field holds documentation pages, not one-sentence summaries. So my earlier post's finding replicates here on human-written data-dictionary text rather than generated text, which is a stronger result than the one I set out to get.

Caveat I'd attach: those deltas are +4 to +7 questions on n=158 and I've only reported margins, not discordant pairs. McNemar on the paired data is what decides whether they're real, and I haven't run it yet. Doing that before I claim a size effect.

---

Correction, 13 Sep. I ran McNemar, and then found something that undercuts the whole cell block above. Both go the same way, so here it is in the same thread rather than quietly in the repo.

The significance. Nothing clears it. Hashed embedder, descriptions deleted: k=5 b=8 c=4 p=0.388, k=10 b=7 c=3 p=0.344, k=20 b=9 c=2 p=0.065. Truncating instead of deleting is indistinguishable from leaving them alone — p=1.000 at caps of 200 and 1,000, on one to four discordant pairs. Twelve small margins pointing the same way in seven cells of twelve is a direction, not a result, and n=158 cannot carry it. So "those descriptions weren't failing to help, they were costing recall" was me reading a net margin as an effect, which is exactly what I said I wouldn't do two paragraphs earlier.

The data underneath it. Worse, and mine. The ablation harness reads each table's JSON with a bare except Exception: continue, and 3,056 of Spider 2.0's 7,892 table files have paths over 260 characters, which Windows refuses to open. So it ran against a schema missing 39% of its tables — which is also why n came out 158 when the fixed scorer resolves gold for 203 of 247. That is the same bug that made my headline Spider 2.0 number 86.1%; counted properly it is 67.2% at k=20. I fixed it in the scoring script and did not fix it in the ablation script, then quoted the ablation here. The 4,338-of-7,892 coverage figure and the 155-word median came from that same reader, so treat them as floors, not counts.

Which means the sentence I was proudest of in this comment — that it replicates on human-written text and is a stronger result than the one I set out to get — is not supported. It may survive a correct rerun. It hasn't had one.