SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_067_Q67_search_negation_term_suppression
← Prev: Alphanumeric SKU Matching... ⊞ All 120 Queries Next: Multi-Lingual Catalog Rou... →
✔ PASS Search & Retrieval task_067_Q67_search_negation_term_suppression Scaled: 1M+ Records Ordered: Yes

Dynamic Negative Keyword Exclusion in E-Commerce Filters

Candidate Latency (1M Rows)
172.53 ms
(1.3x slower than golden)
Golden Latency (1M Rows)
130.58 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: 4.4s
✔ 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

A user searches 'monitor -curved' to find flat computer monitors. The engine must extract positive search terms and suppress any record containing the negative terms.

⚡ The Frontier Trap (Why LLMs Fail)

Negative keywords must not exclude items where the negative term appears with negation qualifiers (e.g. 'not curved').

Prompt / User Request Given to Model
A user searches 'monitor -curved' to find flat computer monitors. The engine must extract positive search terms and suppress any record containing the negative terms.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: item_id, title, description.
Results should be ordered as specified.

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:

📊 display_items 1,000,000 rows 42.0 MiB Data Volume
item_id (INTEGER) title (VARCHAR) description (VARCHAR)
Data Preview (showing sample rows from 1,000,000 total rows):
# item_id title description
1 1 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
2 2 Samsung Odyssey G9 Ultra-wide 49 inch curved gaming monitor
3 3 LG UltraFine 32 Flat panel OLED professional display
4 3 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
5 4 Samsung Odyssey G9 Ultra-wide 49 inch curved gaming monitor
6 5 LG UltraFine 32 Flat panel OLED professional display
7 6 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
8 7 Samsung Odyssey G9 Ultra-wide 49 inch curved gaming monitor
9 8 LG UltraFine 32 Flat panel OLED professional display
10 9 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
11 10 Samsung Odyssey G9 Ultra-wide 49 inch curved gaming monitor
12 11 LG UltraFine 32 Flat panel OLED professional display
13 12 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
14 13 Samsung Odyssey G9 Ultra-wide 49 inch curved gaming monitor
15 14 LG UltraFine 32 Flat panel OLED professional display

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: 324.70 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 13.5%
Latency: 43.98 ms
Rows In/Out: 333,334
Data Vol: 11.44 MB
📐 PROJECTION 0.1%
Latency: 0.18 ms
Rows In/Out: 333,334
Data Vol: 11.45 MB
📁 Projections: ['item_id', 'title'...
🎯 FILTER 🔥 81.9% Cost
Latency: 266.04 ms
Rows In/Out: 333,334
Data Vol: 11.45 MB
⚠️ Heavy Execution Bottleneck (81.9% of query runtime)
🔍 TABLE_SCAN 4.5%
Latency: 14.51 ms
Rows In/Out: 1,000,000
Data Vol: 34.33 MB
📁 memory.main.display_items
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
FILTER 81.9% 266.04 ms 333,334 11.45 MB - 🔥 BOTTLENECK
ORDER_BY 13.5% 43.98 ms 333,334 11.44 MB - Normal
TABLE_SCAN 4.5% 14.51 ms 1,000,000 34.33 MB memory.main.display_items Normal
PROJECTION 0.1% 0.18 ms 333,334 11.45 MB Projections: ['item_id', 'title', 'description'] Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted
Candidate SQL Executed by gemini-3.8-flash-low (Runtime on 1M Rows: 172.53 ms) 
SELECT item_id,
       title,
       description
FROM display_items
WHERE (lower(concat_ws(' ', title, description)) LIKE '%monitor%')
  AND NOT (lower(concat_ws(' ', title, description)) LIKE '%curved%')
ORDER BY item_id ASC;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 130.58 ms) 
-- Positive term: 'monitor', Negative term: 'curved'

SELECT item_id,
       title,
       description
FROM display_items
WHERE (title ILIKE '%monitor%'
       OR description ILIKE '%monitor%')
  AND NOT (title ILIKE '%curved%'
           OR description ILIKE '%curved%')
ORDER BY item_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE display_items (item_id INT, title VARCHAR, description VARCHAR);


INSERT INTO display_items
VALUES (1, 'Dell UltraSharp 27', 'Flat IPS 4K computer monitor with USB-C hub'),
       (2, 'Samsung Odyssey G9', 'Ultra-wide 49 inch curved gaming monitor'),
       (3, 'LG UltraFine 32', 'Flat panel OLED professional display');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (333,334 rows × 3 cols)
# item_id title description
1 1 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
2 3 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
3 6 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
4 9 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
5 12 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
6 15 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
7 18 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
8 21 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
9 24 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
10 27 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
11 30 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
12 33 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
13 36 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
14 39 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
15 42 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
16 45 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
17 48 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
18 51 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
19 54 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
20 57 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
21 60 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
22 63 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
23 66 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
24 69 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
25 72 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
Showing first 25 of 333,334 total rows.
Expected Golden Output (333,334 rows × 3 cols)
# item_id title description
1 1 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
2 3 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
3 6 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
4 9 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
5 12 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
6 15 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
7 18 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
8 21 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
9 24 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
10 27 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
11 30 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
12 33 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
13 36 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
14 39 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
15 42 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
16 45 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
17 48 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
18 51 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
19 54 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
20 57 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
21 60 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
22 63 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
23 66 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
24 69 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
25 72 Dell UltraSharp 27 Flat IPS 4K computer monitor with USB-C hub
Showing first 25 of 333,334 total rows.
← Prev: Alphanumeric SKU Matching... ⊞ Back to Benchmark Dashboard Next: Multi-Lingual Catalog Rou... →