✔ PASS
Search & Retrieval
task_066_Q66_alphanumeric_sku_normalization
Scaled: 1M+ Records
Ordered: No
Alphanumeric SKU Matching with Punctuation & Case Invariance
Candidate Latency (1M Rows)
321.96 ms
(1.0x faster than golden)
Golden Latency (1M Rows)
325.47 ms
DuckDB in-memory engine
Underlying Data Volume
1,000,000 rows
42.0 MiB across 1 table
Evaluated Model
gemini-3.8-flash-low
Gen Latency: 15.6s
✔ 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 TrapBusiness Scenario & Problem
Customers search for model numbers in messy formats: 'wh-1000xm5', 'WH1000-XM5', 'wh 1000 xm5'. Robust search normalizes punctuation and whitespace before matching.
⚡ The Frontier Trap (Why LLMs Fail)
Exact string equality misses 90% of technical product searches due to hyphens and spaces.
Prompt / User Request Given to Model
Customers search for model numbers in messy formats: 'wh-1000xm5', 'WH1000-XM5', 'wh 1000 xm5'. Robust search normalizes punctuation and whitespace before matching. Execute the required analytical SQL calculation in DuckDB. Expected output columns: sku_id, raw_sku, product_title. Row order does not matter.
2. Underlying Tables, Row Counts & Data Volume
1 Tables (1,000,000 rows • 42.0 MiB)Production-scale fixtures loaded into the isolated DuckDB sandbox with at least 1,000,000 records per table:
📊 inventory_skus
1,000,000 rows
42.0 MiB Data Volume
sku_id (INTEGER) raw_sku (VARCHAR) product_title (VARCHAR)Data Preview (showing sample rows from 1,000,000 total rows):
| # | sku_id | raw_sku | product_title |
|---|---|---|---|
| 1 | 1 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 2 | 2 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 3 | 2 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 4 | 3 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 5 | 4 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 6 | 5 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 7 | 6 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 8 | 7 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 9 | 8 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 10 | 9 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 11 | 10 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 12 | 11 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 13 | 12 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 14 | 13 | AIR-MAX-90-W | Nike Air Max 90 White Sneaker |
| 15 | 14 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
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:
0.0%
Latency: 0.29 ms
Rows In/Out: 500,000
Data Vol: 17.17 MB
📁 Projections: ['sku_id', 'raw_sku...
🔥 100.0% Cost
Latency: 603.08 ms
Rows In/Out: 500,000
Data Vol: 17.17 MB
📁 memory.main.inventory_skus
| Operator | Cost % | Latency | Processed Rows | Data Volume | Details / Table | Status |
|---|---|---|---|---|---|---|
| TABLE_SCAN | 100.0% | 603.08 ms | 500,000 | 17.17 MB | memory.main.inventory_skus | 🔥 BOTTLENECK |
| PROJECTION | 0.0% | 0.29 ms | 500,000 | 17.17 MB | Projections: ['sku_id', 'raw_sku', 'product_titl | Normal |
4. Queries Executed (Candidate vs Golden Ground Truth)
Beautified & Syntax HighlightedSELECT sku_id,
raw_sku,
product_title
FROM inventory_skus
WHERE lower(regexp_replace(raw_sku, '[^a-zA-Z0-9]', '', 'g')) = lower(regexp_replace('wh 1000-xm5/b', '[^a-zA-Z0-9]', '', 'g'));
-- Cleanse search query 'wh1000xm5b' by stripping non-alphanumeric characters
SELECT sku_id,
raw_sku,
product_title
FROM inventory_skus
WHERE lower(regexp_replace(raw_sku, '[^a-zA-Z0-9]', '', 'g')) = lower(regexp_replace('wh 1000-xm5/b', '[^a-zA-Z0-9]', '', 'g'));
CREATE TABLE inventory_skus (sku_id INT, raw_sku VARCHAR, product_title VARCHAR);
INSERT INTO inventory_skus
VALUES (1, 'WH-1000XM5/B', 'Sony Premium Wireless Headphones Black'),
(2, 'AIR-MAX-90-W', 'Nike Air Max 90 White Sneaker');
5. Query Results & Expected Ground Truth
Tabular Comparison
Actual Candidate Output
(500,000 rows × 3 cols)
| # | sku_id | raw_sku | product_title |
|---|---|---|---|
| 1 | 1 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 2 | 2 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 3 | 4 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 4 | 6 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 5 | 8 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 6 | 10 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 7 | 12 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 8 | 14 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 9 | 16 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 10 | 18 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 11 | 20 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 12 | 22 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 13 | 24 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 14 | 26 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 15 | 28 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 16 | 30 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 17 | 32 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 18 | 34 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 19 | 36 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 20 | 38 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 21 | 40 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 22 | 42 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 23 | 44 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 24 | 46 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 25 | 48 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
Showing first 25 of 500,000 total rows.
Expected Golden Output
(500,000 rows × 3 cols)
| # | sku_id | raw_sku | product_title |
|---|---|---|---|
| 1 | 1 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 2 | 2 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 3 | 4 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 4 | 6 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 5 | 8 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 6 | 10 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 7 | 12 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 8 | 14 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 9 | 16 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 10 | 18 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 11 | 20 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 12 | 22 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 13 | 24 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 14 | 26 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 15 | 28 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 16 | 30 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 17 | 32 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 18 | 34 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 19 | 36 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 20 | 38 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 21 | 40 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 22 | 42 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 23 | 44 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 24 | 46 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
| 25 | 48 | WH-1000XM5/B | Sony Premium Wireless Headphones Black |
Showing first 25 of 500,000 total rows.