Holmes-SQL: Benchmarking LLM Agents on Evidence-Aware Investigative Reasoning over Structured Databases¶
Conference: EMNLP 2026
Paper: ACL Anthology
Code: https://github.com/anpc849/Holmes-SQL
Area: LLM Reasoning
Keywords: Investigative reasoning, Database agents, Evidence hierarchy, Logical fallacies, State uncertainty
TL;DR¶
This paper introduces Holmes-SQL, the first benchmark evaluating evidence-aware urban crime investigation over relational databases across 500 cases, a 17-table schema, a 3-tier evidential hierarchy, and 10 logical-fallacy patterns. Empirical evaluations reveal that despite achieving over 97% SQL execution success, top LLM agents achieve at most a 49% solve rate while falsely accusing innocent fallacy-aligned suspects 39.6%–67.9% of the time, exposing evidence weighting under state uncertainty as the primary cognitive bottleneck.
Background & Motivation¶
Evaluation benchmarks for Large Language Models interacting with relational databases have rapidly evolved from single-turn Text-to-SQL semantic parsing toward multi-turn analytical database question answering, interactive debugging workflows, and tool-augmented database agents. Contemporary environments such as Spider 2.0, BIRD-INTERACT, and SWE-SQL have greatly elevated realism by demanding schema exploration, iterative error recovery, and interactive disambiguation. Nevertheless, existing SQL benchmarks almost universally rest on a simplifying operational assumption: all truthful records retrieved from the underlying schema are treated as equally informative and decisive. This "retrieve-to-answer" formulation decouples benchmark tasks from high-stakes real-world investigative decision-making.
In real-world domains such as financial fraud analysis, legal discovery, and criminal investigation, records that are entirely truthful at the observational level differ substantially in their inferential strength and evidentiary weight. For instance, an automated traffic camera sighting or an ATM cash withdrawal logged near a crime scene merely confirms physical asset utilization—a circumstantial and easily transferable trace. In contrast, an airline flight manifest or intensive hospital admission establishes a definitive spatiotemporal alibi that strongly precludes physical presence elsewhere. When autonomous agents navigate these heterogeneous data streams, they frequently succumb to superficial correlations and fallacious reasoning traps—such as mistaking proximity for culpability—because they treat all returned tuples with equal evidential weight.
Developing and evaluating agentic capabilities in these domains has been severely bottlenecked by the unavailability of standard benchmarks. Real-world criminal investigation records are strictly sequestered by privacy regulations, legal confidentiality, and data fragmentation, while narrative text-only murder mystery benchmarks bypass the fragmented digital traces that characterize modern IoT-enabled smart cities. To systematically evaluate whether LLM agents can conduct evidence-aware investigative reasoning over relational databases, a benchmark must combine state uncertainty, non-uniform evidential hierarchies, and fallacy-driven distractor mechanisms. Core idea: Introduce Holmes-SQL, the first benchmark for evidence-aware urban crime investigation over relational databases, combining an evidential hierarchy of spatiotemporal and behavioral constraints with 10 formal logical-fallacy templates, requiring agents to iteratively reconstruct the crime scene, identify the victim, prune suspect pools, and isolate the true perpetrator under severe initial state uncertainty.
Method¶
Overall Architecture¶
Holmes-SQL formalizes criminal investigation as a sequential decision-making process over an SQLite database \(\mathcal{D}\). An agent is provided with an initial narrative case description \(x\) (containing only partial clues) and a fixed 17-table schema \(\mathcal{S}\). At each interaction step \(t\), the agent conditions on accumulated trajectory history \(H_{t-1}\) to issue an SQL query \(q_t \sim \pi_\theta(\cdot \mid x, \mathcal{S}, H_{t-1})\), receives the execution output \(o_t = \text{Exec}(q_t, \mathcal{D})\), and updates its interaction context \(H_t = H_{t-1} \oplus \{(q_t, o_t)\}\) up to a maximum budget of \(T=50\) steps, ultimately predicting the murderer ID \(\hat{y}\). The benchmark consists of 500 cases operating over a unified 17-table schema covering location entities, incident reports, companies, airports, media archives, person profiles, employment records, bank accounts, driver licenses, vehicle registries, ATM transactions, flight schedules, passenger manifests, phone calls, hospital admissions, store invoices, and traffic camera logs.
The overall benchmark generation pipeline and agent investigative workflow are structured into an end-to-end framework:
%%{init: {'flowchart': {'rankSpacing': 24, 'nodeSpacing': 28, 'padding': 6, 'wrappingWidth': 400}}}%%
flowchart TD
A["Initial Partial Narrative + Fixed 17-Table Schema"] --> B["Staged Investigation Protocol<br/>Crime Scene → Victim Context → Suspect Disambiguation"]
B --> C["Three-Tier Evidential Hierarchy<br/>Tier 1 Mobility / Tier 2 Medical / Tier 3 Behavioral Traces"]
C --> D["Fallacy-Aligned Distractor Compilation<br/>10 Fallacy Patterns · 4-Suspect Role Matrix"]
D --> E["Two-Stage Controlled Synthesis Pipeline<br/>Diff-Patching · Spatiotemporal Physics · Double-Blind Review"]
E --> F["Deductive Culprit Resolution & Premature Defense"]
Key Designs¶
1. Staged Investigation Protocol: Overcoming Severe State Uncertainty without Step Constraints
Unlike standard Text-to-SQL tasks where the target entities and selection criteria are explicitly provided in the user prompt, Holmes-SQL forces agents to operate under severe state uncertainty. The investigation follows a three-stage progressive discovery protocol: the Crime Stage, the Victim Stage, and the Suspect Stage. In the Crime Stage, the agent parses vague dispatch descriptions (e.g., an unexplained death in a public park) to query crime_scene_report and location, retrieving precise timestamps and physical evidence items like dropped vehicle key fobs. In the Victim Stage, the agent resolves the decedent's identity via person and vehicle, querying income_record and article to uncover individuals holding professional grievances or financial disputes. In the Suspect Stage, the agent tracks activities, alibis, and physical feasibility across the 4 suspects. Crucially, this remains an end-to-end loop rather than a rigid linear pipeline: agents are free to backtrack across stages as newly uncovered evidence reshapes working hypotheses.
2. Three-Tier Evidential Hierarchy: Resolving Non-Uniform Inferential Strength across Relational Tables
To eradicate the unrealistic assumption that all retrieved database rows are equally diagnostic, Holmes-SQL formalizes a Three-Tier Evidential Hierarchy based on spatiotemporal bounding strength. Tier 1 comprises High-Certainty Mobility Evidence (flight, passenger), which binds a physical person to an in-transit aircraft, creating an airtight spatiotemporal alibi that definitively precludes presence elsewhere during the crime window. Tier 2 comprises Conditional Medical Evidence (hospital_visit), whose inferential weight is strictly modulated by clinical reason, admission time, and discharge time—minor outpatient checkups allow physical departures, whereas emergency surgery or severe trauma (e.g., broken limbs conflicting with heavy weapon use) establishes genuine physical incapacitation. Tier 3 comprises Indirect Behavioral Traces (atm_transaction, phone_call, purchase_invoice, traffic_cam_log), capturing physical asset movements (cards, handsets, vehicles) that can easily be borrowed, stolen, shared, or staged. Agents must corroborate weak Tier-3 traces with strong Tier-1/2 constraints, penalizing naive superficial attribution.
3. Fallacy-Aligned Distractor Compilation: Exposing Cognitive Biases in Agentic Hypothesis Tracking To evaluate whether agents can withstand misleading surface correlations, Holmes-SQL operationalizes 10 classic logical fallacies into SQL-solvable multi-table reasoning traps. These include False Cause (confusing temporal proximity of calls with crime commission), Hasty Generalization (matching hair dye/colored lenses to unverified driver licenses), Red Herring (distant large ATM withdrawals distracting from proximate clues), Anchoring (fixating on a suspect with abundant transaction logs while missing a sparse culprit), Confirmation Bias (ignoring flight alibis while focusing on card usage), Appeal to Probability, Affirming the Consequent, Argument from Ignorance, Guilt by Association, and Blind Loyalty. Each case features exactly four suspects with plausible motives, systematically partitioned into four distinct roles: an Innocent Suspect with Standard Fallacy, an Innocent Suspect with Transposed Fallacy, an Innocent Suspect with Contradicted Fallacy (where a hard constraint explicitly refutes the fallacy), and the True Murderer (who operated solo and camouflaged their guilt within benign routine patterns).
4. Two-Stage Controlled Synthesis Pipeline: Guaranteeing Physical Consistency and Unique Solvability To circumvent the legal and privacy barriers of real criminal data while avoiding hallucinated reasoning paths, the authors design a scalable two-stage generation pipeline. In Stage 1 (Script Generation), an instruction-tuned LLM conditioned on urban themes and fallacy seeds synthesizes structured scripts, refined via an LLM-powered diff-patching editor and vetted by two trained fourth-year IT student annotators (inter-annotator agreement \(\kappa=0.85\)). In Stage 2 (Database Compilation & Simulation), an urban simulation engine materializes entities on a \([-50, 50]\) coordinate grid (1 unit = 10 km) adhering to realistic speed constraints (30 km/h city travel, 500 km/h air travel plus 30-minute airport buffers). Background noise is injected across high-frequency behavior tables (e.g., invoices, call logs), resulting in an average table size where ground-truth tuples represent an ultra-sparse 0.004% to 2.19% of records. The engine exports ground-truth tuples, evidence checklists, and validated gold SQL reference paths (averaging 12 steps).
Benchmark & Evaluation Protocol¶
Experiments are executed under the Kaggle Benchmarks framework with deterministic decoding (temperature 0.0). Agents receive schema descriptions and case narratives without few-shot demonstrations, constrained to 10 rows per query, 60-second execution timeouts, and a 50-step limit, with mandatory progress summarization every 10 steps. Beyond Solve Rate (proportion of cases where the true murderer ID \(\hat{y} = y^*\) is identified), the benchmark decouples syntax failures from cognitive failures via ErrorQ (fraction of syntactically/execution-failed queries) and EmptyQ (fraction of queries returning 0 rows). Evidence coverage is tracked via Recall@k against the checklist, scored using an LLM-as-a-Judge protocol (Gemini-2.5-Pro, validated with \(\kappa = 0.85 \pm 0.04\) against human annotations). To measure vulnerability to fallacy traps, the False Accusation Rate (FAR) is defined as: $$ \text{FAR} = \frac{\sum_{c \in \mathcal{C}} \mathbb{I}[A_c = 1 \wedge \hat{m}_c \in \mathcal{I}c]}{\sum $$ where }} \mathbb{I}[A_c = 1]\(A_c = 1\) indicates that the agent successfully recovered the crime report, victim, and all 4 ground-truth suspects in case \(c\), \(\hat{m}_c\) is the predicted culprit, and \(\mathcal{I}_c\) denotes the set of innocent fallacy-aligned suspects.
Key Experimental Results¶
Main Results¶
Eight representative frontier commercial proprietary models and competitive open-source models were benchmarked within the baseline ReAct framework over all 500 cases of Holmes-SQL:
| Model Family | Model Backbone | Solve Rate (↑) | ErrorQ (↓) | EmptyQ (↓) | Recall@50 (↑) | FAR (↓) (Denominator n) |
|---|---|---|---|---|---|---|
| Proprietary | gemini-3.1-pro-preview | 0.4900 | 0.0033 | 0.1380 | 0.5169 | 0.3964 (n = 169) |
| gpt-5.4-2026-03-05 | 0.3940 | 0.0028 | 0.2776 | 0.4159 | 0.5733 (n = 225) | |
| glm-5 | 0.3760 | 0.0075 | 0.1642 | 0.4393 | 0.5350 (n = 200) | |
| gemini-3-flash-preview | 0.3220 | 0.0136 | 0.1998 | 0.5166 | 0.6592 (n = 179) | |
| Open-source | gemma-4-31b | 0.4080 | 0.0085 | 0.1792 | 0.4654 | 0.5282 (n = 195) |
| deepseek-v3.2 | 0.3340 | 0.0142 | 0.2737 | 0.5068 | 0.5545 (n = 220) | |
| qwen3-235b-a22b-instruct-2507 | 0.2080 | 0.0281 | 0.3460 | 0.3056 | 0.6330 (n = 109) | |
| qwen3-coder-480b-a35b-instruct | 0.1880 | 0.0163 | 0.4122 | 0.3007 | 0.6789 (n = 109) |
Note: On a representative sample of 100 cases, human evaluators achieve an average Solve Rate of 87.5% (averaging 23.97 min/case), heavily outperforming the strongest configuration (CoT+ReAct with Gemini 3.1 Pro at 52.0% in 4.84 min/case).
Ablation & Architectural Analysis¶
To dissect how agent architectures, database background noise, and query row limits impact investigative accuracy, the authors evaluated multiple agent paradigms and control settings:
| Evaluation Dimension | Architecture / Setting | Solve Rate (↑) | ErrorQ (↓) | EmptyQ (↓) | Recall@50 (↑) | FAR (↓) | Avg. Cost ($/case) |
|---|---|---|---|---|---|---|---|
| Agent Architectures (on Gemma-4-31B) |
Base + ReAct | 0.3380 | 0.0042 | 0.1917 | 0.3766 | 0.5204 (n = 98) | 0.0183 |
| CoT + ReAct | 0.4080 | 0.0085 | 0.1792 | 0.4654 | 0.5282 (n = 195) | 0.0150 | |
| CoT + PlanAndExecute | 0.4680 | 0.0117 | 0.1477 | 0.4930 | 0.4894 (n = 423) | 0.0149 | |
| CoT + Reflexion | 0.4440 | 0.0075 | 0.1911 | 0.4903 | 0.4975 (n = 394) | 0.0123 | |
| DB Scope / Retention (Retention: 0 / 0.25 / 0.5) |
Gemma-4-31B | 0.4580 / 0.2860 / 0.3140 | — | — | — | — | — |
| DeepSeek-V3.2 | 0.4240 / 0.3980 / 0.4300 | — | — | — | — | — | |
| Qwen3-235B | 0.2700 / 0.2220 / 0.1900 | — | — | — | — | — | |
| SQL Row Limit (Row Limit: 5 / 10 / 20) |
Gemma-4-31B | 0.3580 / 0.4080 / 0.4500 | — | — | — | — | — |
| DeepSeek-V3.2 | 0.3400 / 0.3340 / 0.4120 | — | — | — | — | — | |
| Qwen3-Coder | 0.1620 / 0.1880 / 0.1580 | — | — | — | — | — |
Note: Across the pure reasoning task vs. suspect-only investigation vs. full investigation, Gemini 3.1 Pro achieves 0.7380 in pure reasoning, dropping to 0.6150 (-0.1230) in suspect-only investigation, and plummeting to 0.4900 (-0.2480) in full investigation. Similar steep drops occur across all models (e.g., GPT-5.4 drops from 0.6340 to 0.3940, -0.2400).
Key Findings¶
- High SQL executability does not translate to investigative success (RQ1): Across all 8 models, ErrorQ remains below 3.0% (ranging from 0.28% in GPT-5.4 to 2.81% in Qwen3-235B), indicating over 97% execution reliability. Yet the highest Solve Rate reaches only 49.00%. The case-level Pearson correlation between ErrorQ and Solve Rate is statistically significant but negligible (\(r = -0.0341, p = 0.0314\)), proving that syntactic Text-to-SQL proficiency is merely a prerequisite rather than a guarantee of correct investigative reasoning.
- Rampant premature commitments and inhibitory control failures (RQ2): Even when controlling for discovery noise by providing the complete suspect pool upfront (suspect-only setting), the average False Accusation Rate remains alarmingly high at 56.16% (and 56.98% in full investigation). When encountering plausible circumstantial traces (e.g., a phone call or car plate logged nearby), models exhibit an inhibitory control failure—committing prematurely to innocent suspects without verifying higher-tier alibis or medical constraints.
- Evidence collection collapses in downstream suspect reasoning (RQ3): While models recover crime scenes and victims with 71%–98% accuracy (Tab. 18), recovering the complete suspect set drops precipitously to 21.8%–45.1%. Conditional error analysis (Tab. 13–14) reveals that 75.15% of fatal query errors occur in the suspect stage, with 65.67% of errors being
no_such_columnschema-linking failures. Clue fragmentation across multiple tables dramatically elevates schema-grounding overhead as the investigation deepens. - Explicit planning and verbal reflection mitigate false leads: On Gemma-4-31B, switching from standard ReAct to
CoT+PlanAndExecuteboosts Solve Rate from 40.80% to 46.80% and lowers FAR from 52.82% to 48.94% while cutting EmptyQ from 0.1792 to 0.1477, demonstrating that structured forward planning steers exploration away from misleading distractors at lower overall cost ($0.0149/case).
Highlights & Insights¶
- Formalizing evidence non-uniformity as a core evaluation dimension: Instead of assuming all retrieved tuples carry equal weight, Holmes-SQL establishes a rigorous 3-tier hierarchy (Tier 1 Mobility alibis > Tier 2 Clinical capacity > Tier 3 Behavioral traces), introducing a realistic paradigm for testing how agents weigh conflicting evidence.
- Operationalizing philosophical fallacies into executable relational puzzles: By compiling 10 classic logical fallacies into cross-table SQL traps with a 4-suspect matrix, the benchmark systematically diagnoses cognitive blind spots (e.g., anchoring bias, confirmation bias, affirming the consequent) in LLM agents.
- Isolating the cognitive tax of active investigation: Controlled comparisons between upfront single-step reasoning and multi-turn database exploration demonstrate that the process of formulating hypotheses, searching sparse tables, and integrating cues incurs a massive 11.2%–27.4% performance drop across backbones.
Limitations & Future Work¶
- Synthetic urban ontology vs. real-world complexity: The benchmark relies on synthetic cases operating within a closed 17-table schema and a single-perpetrator assumption. Real-world investigations involve multi-party conspiracies, corrupt or falsified evidence chains, unstructured witness transcripts, and open-ended temporal boundaries.
- Absence of explanation faithfulness evaluation: Current evaluation centers on final culprit accuracy, intermediate evidence recall, and false accusation rates, but does not formally measure the faithfulness, coherence, or logical validity of the agent's generated chain-of-thought rationales.
- Reliance on a single LLM judge for recall scoring: While Gemini-2.5-Pro achieves high human agreement (\(\kappa=0.85\)), LLM-based evaluation metrics can inherit subtle verbosity or positional biases; future iterations could employ multi-model evaluator ensembles or domain-fine-tuned scoring verifiers.
Related Work & Insights¶
- vs. Spider 2.0 / BIRD-INTERACT / SWE-SQL: Modern agentic SQL benchmarks focus heavily on workflow automation, enterprise database debugging, and interactive schema navigation, treating database contents primarily as data to retrieve. Holmes-SQL reframes database records as legal evidence requiring critical appraisal and hypothesis testing under state uncertainty.
- vs. WhodunitBench / AR-Bench / ExCyTIn-Bench: Narrative whodunit benchmarks operate over unstructured text stories, whereas cybersecurity benchmarks (e.g., ExCyTIn-Bench) focus on log traversal and graph exploration. Holmes-SQL bridges the gap by evaluating evidence-aware investigative reasoning directly over large-scale relational database schemas.
Rating¶
- Novelty: ⭐⭐⭐⭐⭐ Pioneering integration of relational database agents, evidential hierarchies, and formal logical-fallacy compilation.
- Experimental Thoroughness: ⭐⭐⭐⭐⭐ Rigorous benchmarking across 8 frontier models, stage-level attribution, FAR metrics, and architectural ablations.
- Writing Quality: ⭐⭐⭐⭐⭐ Flawless conceptual framing, lucid formalizations of evidential hierarchies, and insightful empirical discussions.
- Value: ⭐⭐⭐⭐⭐ Serves as a vital diagnostic benchmark exposing why current LLM agents cannot be safely deployed for high-stakes investigative screening.