SQL-FRONTIER Benchmark Deep-Dive / Search & Retrieval / task_063_Q63_boolean_search_negation
← Prev: Search Autocomplete Sugge... ⊞ All 120 Queries Next: TF-IDF (Term Frequency - ... →
✔ PASS Search & Retrieval task_063_Q63_boolean_search_negation Scaled: 1M+ Records Ordered: Yes

High-Precision Boolean Search with Negation and Grouping

Candidate Latency (1M Rows)
85.86 ms
(1.1x faster than golden)
Golden Latency (1M Rows)
93.82 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: 9.9s
✔ 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

Legal e-discovery and patent search rely on exact Boolean syntax: (patent AND battery) NOT (lithium OR phone). Negations must be strictly applied without dropping valid candidate documents.

⚡ The Frontier Trap (Why LLMs Fail)

Poor operator precedence in SQL boolean expressions causes negated terms to drop unrelated valid results.

Prompt / User Request Given to Model
Legal e-discovery and patent search rely on exact Boolean syntax: (patent AND battery) NOT (lithium OR phone). Negations must be strictly applied without dropping valid candidate documents.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: patent_id, abstract.
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:

📊 patent_corpus 1,000,000 rows 22.9 MiB Data Volume
patent_id (INTEGER) abstract (VARCHAR)
Data Preview (showing sample rows from 1,000,000 total rows):
# patent_id abstract
1 1 Solid-state battery chemistry for electric vehicles and heavy transport
2 2 Lithium ion battery pack design for mobile phone handsets
3 3 Wireless charging pad for mobile phone and earbuds
4 3 Solid-state battery chemistry for electric vehicles and heavy transport
5 4 Lithium ion battery pack design for mobile phone handsets
6 5 Wireless charging pad for mobile phone and earbuds
7 6 Solid-state battery chemistry for electric vehicles and heavy transport
8 7 Lithium ion battery pack design for mobile phone handsets
9 8 Wireless charging pad for mobile phone and earbuds
10 9 Solid-state battery chemistry for electric vehicles and heavy transport
11 10 Lithium ion battery pack design for mobile phone handsets
12 11 Wireless charging pad for mobile phone and earbuds
13 12 Solid-state battery chemistry for electric vehicles and heavy transport
14 13 Lithium ion battery pack design for mobile phone handsets
15 14 Wireless charging pad for mobile phone and earbuds

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: 135.58 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
⏳ ORDER_BY 🔥 26.5% Cost
Latency: 35.87 ms
Rows In/Out: 333,334
Data Vol: 6.36 MB
⚠️ Heavy Execution Bottleneck (26.5% of query runtime)
📐 PROJECTION 0.1%
Latency: 0.13 ms
Rows In/Out: 333,334
Data Vol: 6.36 MB
📁 Projections: ['patent_id', 'abst...
🔍 TABLE_SCAN 🔥 73.5% Cost
Latency: 99.59 ms
Rows In/Out: 333,334
Data Vol: 6.36 MB
📁 memory.main.patent_corpus
⚠️ Heavy Execution Bottleneck (73.5% of query runtime)
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
TABLE_SCAN 73.5% 99.59 ms 333,334 6.36 MB memory.main.patent_corpus 🔥 BOTTLENECK
ORDER_BY 26.5% 35.87 ms 333,334 6.36 MB - 🔥 BOTTLENECK
PROJECTION 0.1% 0.13 ms 333,334 6.36 MB Projections: ['patent_id', 'abstract'] 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: 85.86 ms) 
SELECT patent_id,
       abstract
FROM patent_corpus
WHERE abstract ILIKE '%battery%'
  AND NOT (abstract ILIKE '%lithium%'
           OR abstract ILIKE '%phone%')
ORDER BY patent_id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 93.82 ms) 
-- Query: ('battery') AND NOT ('lithium' OR 'phone')

SELECT patent_id,
       abstract
FROM patent_corpus
WHERE abstract ILIKE '%battery%'
  AND NOT (abstract ILIKE '%lithium%'
           OR abstract ILIKE '%phone%')
ORDER BY patent_id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE patent_corpus (patent_id INT, abstract VARCHAR);


INSERT INTO patent_corpus
VALUES (1, 'Solid-state battery chemistry for electric vehicles and heavy transport'),
       (2, 'Lithium ion battery pack design for mobile phone handsets'),
       (3, 'Wireless charging pad for mobile phone and earbuds');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (333,334 rows × 2 cols)
# patent_id abstract
1 1 Solid-state battery chemistry for electric vehicles and heavy transport
2 3 Solid-state battery chemistry for electric vehicles and heavy transport
3 6 Solid-state battery chemistry for electric vehicles and heavy transport
4 9 Solid-state battery chemistry for electric vehicles and heavy transport
5 12 Solid-state battery chemistry for electric vehicles and heavy transport
6 15 Solid-state battery chemistry for electric vehicles and heavy transport
7 18 Solid-state battery chemistry for electric vehicles and heavy transport
8 21 Solid-state battery chemistry for electric vehicles and heavy transport
9 24 Solid-state battery chemistry for electric vehicles and heavy transport
10 27 Solid-state battery chemistry for electric vehicles and heavy transport
11 30 Solid-state battery chemistry for electric vehicles and heavy transport
12 33 Solid-state battery chemistry for electric vehicles and heavy transport
13 36 Solid-state battery chemistry for electric vehicles and heavy transport
14 39 Solid-state battery chemistry for electric vehicles and heavy transport
15 42 Solid-state battery chemistry for electric vehicles and heavy transport
16 45 Solid-state battery chemistry for electric vehicles and heavy transport
17 48 Solid-state battery chemistry for electric vehicles and heavy transport
18 51 Solid-state battery chemistry for electric vehicles and heavy transport
19 54 Solid-state battery chemistry for electric vehicles and heavy transport
20 57 Solid-state battery chemistry for electric vehicles and heavy transport
21 60 Solid-state battery chemistry for electric vehicles and heavy transport
22 63 Solid-state battery chemistry for electric vehicles and heavy transport
23 66 Solid-state battery chemistry for electric vehicles and heavy transport
24 69 Solid-state battery chemistry for electric vehicles and heavy transport
25 72 Solid-state battery chemistry for electric vehicles and heavy transport
Showing first 25 of 333,334 total rows.
Expected Golden Output (333,334 rows × 2 cols)
# patent_id abstract
1 1 Solid-state battery chemistry for electric vehicles and heavy transport
2 3 Solid-state battery chemistry for electric vehicles and heavy transport
3 6 Solid-state battery chemistry for electric vehicles and heavy transport
4 9 Solid-state battery chemistry for electric vehicles and heavy transport
5 12 Solid-state battery chemistry for electric vehicles and heavy transport
6 15 Solid-state battery chemistry for electric vehicles and heavy transport
7 18 Solid-state battery chemistry for electric vehicles and heavy transport
8 21 Solid-state battery chemistry for electric vehicles and heavy transport
9 24 Solid-state battery chemistry for electric vehicles and heavy transport
10 27 Solid-state battery chemistry for electric vehicles and heavy transport
11 30 Solid-state battery chemistry for electric vehicles and heavy transport
12 33 Solid-state battery chemistry for electric vehicles and heavy transport
13 36 Solid-state battery chemistry for electric vehicles and heavy transport
14 39 Solid-state battery chemistry for electric vehicles and heavy transport
15 42 Solid-state battery chemistry for electric vehicles and heavy transport
16 45 Solid-state battery chemistry for electric vehicles and heavy transport
17 48 Solid-state battery chemistry for electric vehicles and heavy transport
18 51 Solid-state battery chemistry for electric vehicles and heavy transport
19 54 Solid-state battery chemistry for electric vehicles and heavy transport
20 57 Solid-state battery chemistry for electric vehicles and heavy transport
21 60 Solid-state battery chemistry for electric vehicles and heavy transport
22 63 Solid-state battery chemistry for electric vehicles and heavy transport
23 66 Solid-state battery chemistry for electric vehicles and heavy transport
24 69 Solid-state battery chemistry for electric vehicles and heavy transport
25 72 Solid-state battery chemistry for electric vehicles and heavy transport
Showing first 25 of 333,334 total rows.
← Prev: Search Autocomplete Sugge... ⊞ Back to Benchmark Dashboard Next: TF-IDF (Term Frequency - ... →