✔ PASS Warehouse & Statistical task_097_Q97_sargable_date_boundary_pruning Scaled: 1M+ Records Ordered: Yes

SARGable Partition Pruning: Exact Half-Open Date Boundary Ranges

Candidate Latency (1M Rows)

0.10 ms

(1.0x faster than golden)

Golden Latency (1M Rows)

0.11 ms

DuckDB in-memory engine

Underlying Data Volume

1,000,000 rows

11.4 MiB across 1 table

Evaluated Model

gemini-3.8-flash-low

Gen Latency: 11.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

When filtering analytical partitions for a calendar month, `BETWEEN '2026-01-01' AND '2026-01-31'` leaks into February 1st if timestamps contain hours/minutes, while `strftime('%m', ts) = '01'` disables partition pruning entirely.

⚡ The Frontier Trap (Why LLMs Fail)

Subtle boundary leakage or full table scans caused by non-SARGable WHERE predicates.

Prompt / User Request Given to Model

When filtering analytical partitions for a calendar month, `BETWEEN '2026-01-01' AND '2026-01-31'` leaks into February 1st if timestamps contain hours/minutes, while `strftime('%m', ts) = '01'` disables partition pruning entirely.

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

2. Underlying Tables, Row Counts & Data Volume

1 Tables (1,000,000 rows • 11.4 MiB)

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

📊 audit_events 1,000,000 rows 11.4 MiB Data Volume

event_id (INTEGER) created_at (TIMESTAMP)

Data Preview (showing sample rows from 1,000,000 total rows):

#	event_id	created_at
1	1	2026-01-31 23:59:59
2	2	2026-02-01 00:00:00
3	3	2026-01-15 12:00:00
4	3	2026-03-01 09:30:00.000002
5	4	2026-03-01 09:30:00.000029
6	5	2026-03-01 09:30:00.000053
7	6	2026-03-01 09:30:00.000061
8	7	2026-03-01 09:30:00.000085
9	8	2026-03-01 09:30:00.000102
10	9	2026-03-01 09:30:00.000126
11	10	2026-03-01 09:30:00.000142
12	11	2026-03-01 09:30:00.000161
13	12	2026-03-01 09:30:00.000190
14	13	2026-03-01 09:30:00.000214
15	14	2026-03-01 09:30:00.000230

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

⏳ ORDER_BY 78.8%

Latency: 0.08 ms

Rows In/Out: 2

Data Vol: 24 B

📐 PROJECTION 0.6%

Latency: 0.00 ms

Rows In/Out: 2

Data Vol: 24 B

📁 Projections: ['event_id', 'creat...

🔍 TABLE_SCAN 20.7%

Latency: 0.02 ms

Rows In/Out: 2

Data Vol: 24 B

📁 memory.main.audit_events

Operator	Cost %	Latency	Processed Rows	Details / Table	Status
ORDER_BY	78.8%	0.08 ms	2	-	Normal
TABLE_SCAN	20.7%	0.02 ms	2	memory.main.audit_events	Normal
PROJECTION	0.6%	0.00 ms	2	Projections: ['event_id', 'created_at']	Normal

4. Queries Executed (Candidate vs Golden Ground Truth)

Beautified & Syntax Highlighted

SELECT event_id,
       created_at
FROM audit_events
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND created_at < TIMESTAMP '2026-02-01 00:00:00'
ORDER BY created_at;

-- Exact SARGable half-open January 2026 boundary query

SELECT event_id,
       created_at
FROM audit_events
WHERE created_at >= TIMESTAMP '2026-01-01 00:00:00'
  AND created_at < TIMESTAMP '2026-02-01 00:00:00'
ORDER BY created_at;

CREATE TABLE audit_events (event_id INT, created_at TIMESTAMP);


INSERT INTO audit_events
VALUES (1, '2026-01-31 23:59:59'),
       (2, '2026-02-01 00:00:00'),
       -- Should NOT be in January
 (3, '2026-01-15 12:00:00');

5. Query Results & Expected Ground Truth

Tabular Comparison

Actual Candidate Output (2 rows × 2 cols)

#	event_id	created_at
1	3	2026-01-15 12:00:00
2	1	2026-01-31 23:59:59

Expected Golden Output (2 rows × 2 cols)

#	event_id	created_at
1	3	2026-01-15 12:00:00
2	1	2026-01-31 23:59:59

← Prev: Financial Ledger Running ... ⊞ Back to Benchmark Dashboard Next: Window Peer Frame Exclusi... →