✔ PASS Search & Retrieval task_074_Q74_did_you_mean_spell_suggestion Scaled: 1M+ Records Ordered: Yes

'Did You Mean?' Query Spell-Correction Suggestion Engine

Candidate Latency (1M Rows)

98.10 ms

(1.2x slower than golden)

Golden Latency (1M Rows)

82.56 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

22.9 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 27.0s

✔ Correctness Verified on 1M+ Records

Candidate SQL output matches ground truth golden query across all multiset rows and numeric tolerances (tol=1e-3). Zero data skew or anti-patterns detected on full dataset scale.

1. Analytical Significance & What It Means

Scenario & Frontier Trap

Business Scenario & Problem

When a query produces few results, search engines recommend a spell-corrected candidate word from the catalog vocabulary that has the lowest edit distance and highest document frequency.

⚡ The Frontier Trap (Why LLMs Fail)

Suggesting rare typos back to users rather than verified popular vocabulary terms.

Prompt / User Request Given to Model

When a query produces few results, search engines recommend a spell-corrected candidate word from the catalog vocabulary that has the lowest edit distance and highest document frequency.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: suggested_correction, doc_frequency, edit_distance.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 22.9 MiB)

Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:

📊 dictionary_terms 1,000,000 rows 22.9 MiB Data Volume

term (VARCHAR) doc_frequency (INTEGER)

Data Preview (showing sample rows from 1,000,000 total rows):

#	term	doc_frequency
1	wireless	50,000
2	wiretap	50
3	tireless	120
4	wireless	1
5	wiretap	13,820
6	tireless	19,206
7	wireless	22,310
8	wiretap	16,215
9	tireless	5,523
10	wireless	9,290
11	wiretap	25,055
12	tireless	27,618
13	wireless	1
14	wiretap	6,809
15	tireless	3,277

3. Graphical Query Execution Plan & Cost Bottlenecks

Physical Operator Tree (DuckDB 2.0 Profiler)

Visual flowchart of the physical execution pipeline. Operators with high relative costs (≥ 20% of query runtime or heavy latency) are visually highlighted:

Pipeline Latency Distribution (Total Plan Time: 373.61 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

✂️ TOP_N 1.3%

Latency: 4.97 ms

Rows In/Out: 1

Data Vol: 28 B

📐 PROJECTION 🔥 38.9% Cost

Latency: 145.50 ms

Rows In/Out: 666,667

Data Vol: 17.80 MB

📁 Projections: ['suggested_correct...

🔍 TABLE_SCAN 🔥 59.7% Cost

Latency: 223.14 ms

Rows In/Out: 666,667

Data Vol: 12.72 MB

📁 memory.main.dictionary_terms

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
TABLE_SCAN	59.7%	223.14 ms	666,667	12.72 MB	memory.main.dictionary_terms	🔥 BOTTLENECK
PROJECTION	38.9%	145.50 ms	666,667	17.80 MB	Projections: ['suggested_correction', 'doc_frequ	🔥 BOTTLENECK
TOP_N	1.3%	4.97 ms	1	0.00 MB	-	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

-- Given misspelled query 'wireles', propose top dictionary replacement with edit distance <= 1

SELECT term AS suggested_correction,
       doc_frequency,
       levenshtein(term, 'wireles') AS edit_distance
FROM dictionary_terms
WHERE levenshtein(term, 'wireles') <= 2
ORDER BY edit_distance ASC,
         doc_frequency DESC
LIMIT 1;

-- Given misspelled query 'wireles', propose top dictionary replacement with edit distance <= 1

SELECT term AS suggested_correction,
       doc_frequency,
       levenshtein(term, 'wireles') AS edit_distance
FROM dictionary_terms
WHERE levenshtein(term, 'wireles') <= 2
ORDER BY edit_distance ASC,
         doc_frequency DESC
LIMIT 1;

CREATE TABLE dictionary_terms (term VARCHAR, doc_frequency INT);


INSERT INTO dictionary_terms
VALUES ('wireless', 50000),
       ('wiretap', 50),
       ('tireless', 120);

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1 rows × 3 cols)

#	suggested_correction	doc_frequency	edit_distance
1	wireless	139,488	1

Expected Golden Output (1 rows × 3 cols)

#	suggested_correction	doc_frequency	edit_distance
1	wireless	139,488	1

← Prev: Search Term Co-Occurrence... ⊞ Back to Benchmark Dashboard Next: Hybrid Search with Hard C... →