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

Brand & Name Fuzzy Search via Jaro-Winkler Prefix Similarity

Candidate Latency (1M Rows)

0.66 ms

(1.3x slower than golden)

Golden Latency (1M Rows)

0.50 ms

DuckDB in-memory engine

Underlying Data Volume

2,500 rows

58.6 KiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 13.8s

✔ 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

In e-commerce, users often mistype the suffixes of brand names ('Samsumg' vs 'Samsung'). Jaro-Winkler gives higher weight to prefix matches, making it ideal for proper names.

⚡ The Frontier Trap (Why LLMs Fail)

Standard Levenshtein treats mistakes at the start of a word the same as at the end.

Prompt / User Request Given to Model

In e-commerce, users often mistype the suffixes of brand names ('Samsumg' vs 'Samsung'). Jaro-Winkler gives higher weight to prefix matches, making it ideal for proper names.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: brand_id, brand_name, jaro_similarity.
Results should be ordered as specified.

2. Underlying Tables, Row Counts & Data Volume

1 Tables (2,500 rows • 58.6 KiB)

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

📊 brands 2,500 rows 58.6 KiB Data Volume

brand_id (INTEGER) brand_name (VARCHAR)

Data Preview (showing sample rows from 2,500 total rows):

#	brand_id	brand_name
1	1	Samsung
2	2	Samsonite
3	3	Apple
4	4	Sony
5	4	Samsung
6	5	Samsonite
7	6	Apple
8	7	Sony
9	8	Samsung
10	9	Samsonite
11	10	Apple
12	11	Sony
13	12	Samsung
14	13	Samsonite
15	14	Apple

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: 0.66 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders

📐 PROJECTION 6.4%

Latency: 0.04 ms

Rows In/Out: 1,250

Data Vol: 0.03 MB

📁 Projections: ['CAST(#0 AS INTEGE...

⏳ ORDER_BY 33.0%

Latency: 0.22 ms

Rows In/Out: 1,250

Data Vol: 0.03 MB

📐 PROJECTION 2.3%

Latency: 0.02 ms

Rows In/Out: 1,250

Data Vol: 0.03 MB

📁 Projections: ['CAST(#0 AS SMALLI...

📐 PROJECTION 22.0%

Latency: 0.15 ms

Rows In/Out: 1,250

Data Vol: 0.03 MB

📁 Projections: ['brand_id', 'brand...

🔍 TABLE_SCAN 36.3%

Latency: 0.24 ms

Rows In/Out: 1,250

Data Vol: 0.02 MB

📁 memory.main.brands

Operator	Cost %	Latency	Processed Rows	Data Volume	Details / Table	Status
TABLE_SCAN	36.3%	0.24 ms	1,250	0.02 MB	memory.main.brands	Normal
ORDER_BY	33.0%	0.22 ms	1,250	0.03 MB	-	Normal
PROJECTION	22.0%	0.15 ms	1,250	0.03 MB	Projections: ['brand_id', 'brand_name', 'jaro_si	Normal
PROJECTION	6.4%	0.04 ms	1,250	0.03 MB	Projections: ['CAST(#0 AS INTEGER)', '__internal	Normal
PROJECTION	2.3%	0.02 ms	1,250	0.03 MB	Projections: ['CAST(#0 AS SMALLINT)', '__interna	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT brand_id,
       brand_name,
       round(jaro_winkler_similarity(brand_name, 'Samsun'), 4) AS jaro_similarity
FROM brands
WHERE jaro_winkler_similarity(brand_name, 'Samsun') >= 0.85
ORDER BY jaro_similarity DESC;

-- Match brand query 'Samsun' with threshold >= 0.85

SELECT brand_id,
       brand_name,
       round(jaro_winkler_similarity(brand_name, 'Samsun'), 4) AS jaro_similarity
FROM brands
WHERE jaro_winkler_similarity(brand_name, 'Samsun') >= 0.85
ORDER BY jaro_similarity DESC;

CREATE TABLE brands (brand_id INT, brand_name VARCHAR);


INSERT INTO brands
VALUES (1, 'Samsung'),
       (2, 'Samsonite'),
       (3, 'Apple'),
       (4, 'Sony');

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (1,250 rows × 3 cols)

#	brand_id	brand_name	jaro_similarity
1	1	Samsung	0.9714
2	4	Samsung	0.9714
3	8	Samsung	0.9714
4	12	Samsung	0.9714
5	16	Samsung	0.9714
6	20	Samsung	0.9714
7	24	Samsung	0.9714
8	28	Samsung	0.9714
9	32	Samsung	0.9714
10	36	Samsung	0.9714
11	40	Samsung	0.9714
12	44	Samsung	0.9714
13	48	Samsung	0.9714
14	52	Samsung	0.9714
15	56	Samsung	0.9714
16	60	Samsung	0.9714
17	64	Samsung	0.9714
18	68	Samsung	0.9714
19	72	Samsung	0.9714
20	76	Samsung	0.9714
21	80	Samsung	0.9714
22	84	Samsung	0.9714
23	88	Samsung	0.9714
24	92	Samsung	0.9714
25	96	Samsung	0.9714

Showing first 25 of 1,250 total rows.

Expected Golden Output (1,250 rows × 3 cols)

#	brand_id	brand_name	jaro_similarity
1	1	Samsung	0.9714
2	4	Samsung	0.9714
3	8	Samsung	0.9714
4	12	Samsung	0.9714
5	16	Samsung	0.9714
6	20	Samsung	0.9714
7	24	Samsung	0.9714
8	28	Samsung	0.9714
9	32	Samsung	0.9714
10	36	Samsung	0.9714
11	40	Samsung	0.9714
12	44	Samsung	0.9714
13	48	Samsung	0.9714
14	52	Samsung	0.9714
15	56	Samsung	0.9714
16	60	Samsung	0.9714
17	64	Samsung	0.9714
18	68	Samsung	0.9714
19	72	Samsung	0.9714
20	76	Samsung	0.9714
21	80	Samsung	0.9714
22	84	Samsung	0.9714
23	88	Samsung	0.9714
24	92	Samsung	0.9714
25	96	Samsung	0.9714

Showing first 25 of 1,250 total rows.

← Prev: Typo-Tolerant Search with... ⊞ Back to Benchmark Dashboard Next: Multi-Field Weighted Full... →