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

Zero-Result Search Query Relaxation (Strict AND -> Loose OR Fallback)

Candidate Latency (1M Rows)
262.53 ms
(1.1x slower than golden)
Golden Latency (1M Rows)
245.20 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: 11.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

If a multi-word search query (e.g. 'ergonomic mechanical wireless split keyboard') returns zero results under strict AND matching, modern engines gracefully relax to OR matching.

⚡ The Frontier Trap (Why LLMs Fail)

Failing to return fallback results leads to user bounce and abandoned sessions.

Prompt / User Request Given to Model
If a multi-word search query (e.g. 'ergonomic mechanical wireless split keyboard') returns zero results under strict AND matching, modern engines gracefully relax to OR matching.

Execute the required analytical SQL calculation in DuckDB.
Expected output columns: id, title, match_score, match_tier.
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:

📊 keyboard_catalog 1,000,000 rows 22.9 MiB Data Volume
id (INTEGER) title (VARCHAR)
Data Preview (showing sample rows from 1,000,000 total rows):
# id title
1 1 Ergonomic Split Keyboard Wired
2 2 Mechanical Wireless Office Keyboard
3 3 Standard Membrane Keyboard
4 3 Ergonomic Split Keyboard Wired
5 4 Mechanical Wireless Office Keyboard
6 5 Standard Membrane Keyboard
7 6 Ergonomic Split Keyboard Wired
8 7 Mechanical Wireless Office Keyboard
9 8 Standard Membrane Keyboard
10 9 Ergonomic Split Keyboard Wired
11 10 Mechanical Wireless Office Keyboard
12 11 Standard Membrane Keyboard
13 12 Ergonomic Split Keyboard Wired
14 13 Mechanical Wireless Office Keyboard
15 14 Standard Membrane Keyboard

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: 233.44 ms) Nodes consuming ≥ 20% of query runtime are highlighted with glowing borders
📐 PROJECTION 0.1%
Latency: 0.27 ms
Rows In/Out: 666,667
Data Vol: 25.43 MB
📁 Projections: ['#0', '#1', 'CAST(...
⏳ ORDER_BY 🔥 39.2% Cost
Latency: 91.57 ms
Rows In/Out: 666,667
Data Vol: 23.52 MB
⚠️ Heavy Execution Bottleneck (39.2% of query runtime)
📐 PROJECTION 0.2%
Latency: 0.43 ms
Rows In/Out: 666,667
Data Vol: 25.44 MB
📁 Projections: ['#0', '#1', 'CAST(...
📐 PROJECTION 0.6%
Latency: 1.51 ms
Rows In/Out: 666,667
Data Vol: 27.35 MB
📁 Projections: ['id', 'title', 'ma...
📐 PROJECTION 🔥 22.4% Cost
Latency: 52.24 ms
Rows In/Out: 666,667
Data Vol: 15.26 MB
📁 Projections: ['id', 'title', 'ma...
⚠️ Heavy Execution Bottleneck (22.4% of query runtime)
🔍 TABLE_SCAN 🔥 37.4% Cost
Latency: 87.41 ms
Rows In/Out: 666,667
Data Vol: 12.72 MB
📁 memory.main.keyboard_catalog
⚠️ Heavy Execution Bottleneck (37.4% of query runtime)
Operator Cost % Latency Processed Rows Data Volume Details / Table Status
ORDER_BY 39.2% 91.57 ms 666,667 23.52 MB - 🔥 BOTTLENECK
TABLE_SCAN 37.4% 87.41 ms 666,667 12.72 MB memory.main.keyboard_catalog 🔥 BOTTLENECK
PROJECTION 22.4% 52.24 ms 666,667 15.26 MB Projections: ['id', 'title', 'match_score'] 🔥 BOTTLENECK
PROJECTION 0.6% 1.51 ms 666,667 27.35 MB Projections: ['id', 'title', 'match_score', 'mat Normal
PROJECTION 0.2% 0.43 ms 666,667 25.44 MB Projections: ['#0', '#1', 'CAST(#2 AS TINYINT)', Normal
PROJECTION 0.1% 0.27 ms 666,667 25.43 MB Projections: ['#0', '#1', 'CAST(#2 AS INTEGER)', 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: 262.53 ms) 
-- Query tokens: 'ergonomic', 'mechanical', 'wireless'
WITH scored_matches AS
  (SELECT id,
          title,
          (CASE
               WHEN title ILIKE '%ergonomic%' THEN 1
               ELSE 0
           END + CASE
                     WHEN title ILIKE '%mechanical%' THEN 1
                     ELSE 0
                 END + CASE
                           WHEN title ILIKE '%wireless%' THEN 1
                           ELSE 0
                       END) AS match_score
   FROM keyboard_catalog)
SELECT id,
       title,
       match_score,
       CASE
           WHEN match_score = 3 THEN 'STRICT_ALL_MATCH'
           ELSE 'RELAXED_PARTIAL_MATCH'
       END AS match_tier
FROM scored_matches
WHERE match_score >= 1
ORDER BY match_score DESC,
         id;
Golden Reference Ground Truth SQL (Runtime on 1M Rows: 245.20 ms) 
-- Query tokens: 'ergonomic', 'mechanical', 'wireless'
 WITH scored_matches AS
  (SELECT id,
          title,
          (CASE
               WHEN title ILIKE '%ergonomic%' THEN 1
               ELSE 0
           END + CASE
                     WHEN title ILIKE '%mechanical%' THEN 1
                     ELSE 0
                 END + CASE
                           WHEN title ILIKE '%wireless%' THEN 1
                           ELSE 0
                       END) AS match_score
   FROM keyboard_catalog)
SELECT id,
       title,
       match_score,
       CASE
           WHEN match_score = 3 THEN 'STRICT_ALL_MATCH'
           ELSE 'RELAXED_PARTIAL_MATCH'
       END AS match_tier
FROM scored_matches
WHERE match_score >= 1
ORDER BY match_score DESC,
         id;
DuckDB Fixture Setup DDL & Data Population 
CREATE TABLE keyboard_catalog (id INT, title VARCHAR);


INSERT INTO keyboard_catalog
VALUES (1, 'Ergonomic Split Keyboard Wired'),
       (2, 'Mechanical Wireless Office Keyboard'),
       (3, 'Standard Membrane Keyboard');

5. Query Results & Expected Ground Truth

Tabular Comparison
Actual Candidate Output (666,667 rows × 4 cols)
# id title match_score match_tier
1 2 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
2 4 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
3 7 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
4 10 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
5 13 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
6 16 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
7 19 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
8 22 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
9 25 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
10 28 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
11 31 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
12 34 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
13 37 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
14 40 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
15 43 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
16 46 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
17 49 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
18 52 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
19 55 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
20 58 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
21 61 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
22 64 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
23 67 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
24 70 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
25 73 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
Showing first 25 of 666,667 total rows.
Expected Golden Output (666,667 rows × 4 cols)
# id title match_score match_tier
1 2 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
2 4 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
3 7 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
4 10 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
5 13 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
6 16 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
7 19 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
8 22 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
9 25 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
10 28 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
11 31 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
12 34 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
13 37 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
14 40 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
15 43 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
16 46 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
17 49 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
18 52 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
19 55 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
20 58 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
21 61 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
22 64 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
23 67 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
24 70 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
25 73 Mechanical Wireless Office Keyboard 2 RELAXED_PARTIAL_MATCH
Showing first 25 of 666,667 total rows.
← Prev: Multi-Lingual Catalog Rou... ⊞ Back to Benchmark Dashboard Next: Sequential Multi-Stage Us... →