PaperlayerView exact revision on arXiv

Source-linked AI summary

NL2SQL-BUGs: A Benchmark for Detecting Semantic Errors in NL2SQL Translation

Xinyu Liu, Shuyu Shen, Boyan Li, Nan Tang, Yuyu Luo

arXiv:2503.11984v2cs.DB

TL;DR

NL2SQL systems still produce semantic errors that are difficult to detect, while existing benchmarks emphasize successful translation rather than error identification. The paper introduces NL2SQL-BUGs, a taxonomy-based benchmark for detecting and categorizing these errors, and reports limitations of current LLM-based detectors alongside previously hidden errors in BIRD and Spider.

  • Problem

    Semantic errors can survive SQL execution and remain difficult to detect, yet existing NL2SQL benchmarks primarily emphasize correct translation rather than semantic error identification.

  • Method

    The paper introduces NL2SQL-BUGs, using a two-level taxonomy and 2,018 expert-annotated instances to evaluate NL2SQL semantic error detection.

  • Results

    106 BIRD queries and 16 Spider queries contained previously unidentified semantic errors, representing 6.91% and 1.55% of their development sets, respectively.

  • Takeaways & Limitations

    The findings show that current LLM-based models remain limited for semantic error detection and motivate further development of automatic detection techniques.

Abstract

from arXiv · show

Natural Language to SQL (i.e., NL2SQL) translation is crucial for democratizing database access, but even state-of-the-art models frequently generate semantically incorrect SQL queries, hindering the widespread adoption of these techniques by database vendors. While existing NL2SQL benchmarks primarily focus on correct query translation, we argue that a benchmark dedicated to identifying common errors in NL2SQL translations is equally important, as accurately detecting these errors is a prerequisite for any subsequent correction-whether performed by humans or models. To address this gap, we propose NL2SQL-BUGs, the first benchmark dedicated to detecting and categorizing semantic errors in NL2SQL translation. NL2SQL-BUGs adopts a two-level taxonomy to systematically classify semantic errors, covering 9 main categories and 31 subcategories. The benchmark consists of 2,018 expert-annotated instances, each containing a natural language query, database schema, and SQL query, with detailed error annotations for semantically incorrect queries. Through comprehensive experiments, we demonstrate that current large language models exhibit significant limitations in semantic error detection, achieving an average detection accuracy of 75.16%. Specifically, our method successfully detected 106 errors (accounting for 6.91%) in BIRD, a widely-used NL2SQL dataset, which were previously undetected annotation errors. This highlights the importance of semantic error detection in NL2SQL systems. The benchmark is publicly available at https://nl2sql-bugs.github.io/.

1 Introduction

NL2SQL models often produce semantically incorrect yet executable queries, making semantic error detection essential for reliable translation. NL2SQL-BUGs addresses this need with a taxonomy, expert-annotated benchmark, and experiments revealing hidden benchmark errors and limits of current methods.

  • Motivation: Semantic errors are syntactically valid queries that fail to reflect the user’s intended meaning and are harder to detect than syntax errors.They can produce incorrect outputs without visible execution failures.
  • Motivation: 98.8% of CodeS errors in Spider and 98.7% in BIRD were semantic, based on analyses of 170 and 667 errors, respectively.The corresponding counts were 168 of 170 in Spider and 658 of 667 in BIRD.
  • Task and Contributions: The paper defines semantic error detection as checking discrepancies among the natural-language query, database schema, and generated SQL query.Detected discrepancies can trigger inspection, query revision, or automated correction.
  • Task and Contributions: NL2SQL-BUGs classifies semantic errors with a two-level taxonomy containing 9 main categories and 31 subcategories.The taxonomy is designed to organize common NL2SQL semantic errors systematically.
  • Task and Contributions: The benchmark contains 2,018 expert-annotated instances with natural-language queries, database schemas, SQL queries, and detailed error annotations.Annotations target semantically incorrect SQL queries.
  • Results: 16 Spider queries and 106 BIRD queries contained previously unidentified semantic errors, representing 1.55% and 6.91% of their development sets.GPT-4o was prompted using the taxonomy and benchmark demonstrations, and the findings motivate more robust detection methods.

2 Problem and Real-world Cases

The paper formulates semantic error detection as judging whether SQL is semantically equivalent to a natural-language query over a database, then classifying detected errors. Applying GPT-4o to Spider and BIRD uncovered previously unidentified semantic errors in both benchmarks.

  • Problem Formulation: Semantic error detection evaluates whether an SQL query is semantically equivalent to the natural-language query over the given database.The task takes a natural-language question, relational database, and SQL query as input.
  • Problem Formulation: The detector returns a binary decision indicating whether the SQL query is semantically correct or wrong with respect to the natural-language question.A detected error is represented by F(NL, DB, SQL) = False.
  • Problem Formulation: Semantic error classification maps an erroneous NL2SQL triple to a predefined error type in the taxonomy.This classification follows the initial detection decision.
  • Detected Errors in Popular Benchmarks: GPT-4o evaluated natural-language queries, database schemas, and SQL queries from Spider and BIRD for semantic correctness.The model’s predictions were subsequently manually validated.
  • Detected Errors in Popular Benchmarks: 16 Spider queries and 106 BIRD queries contained previously unidentified semantic errors, corresponding to 1.55% and 6.91% of their development sets.The validated errors demonstrate the utility of semantic error detection for finding hidden benchmark issues.

3 NL2SQL Semantic Errors Taxonomy

The taxonomy organizes NL2SQL semantic mistakes by query structure, translation failure, and impact on query meaning. It covers schema mapping, joins, values, operators, conditions, functions, clauses, and subqueries through named error categories and examples.

  • Taxonomy Overview: The taxonomy uses SQL structure, common translation mistakes, and effects on query semantics to identify errors at different stages of query generation.Its hierarchical categories and subcategories support more precise diagnosis of model weaknesses.
  • Schema and Value Errors: Attribute-related errors arise when models map natural-language requirements to incorrect, redundant, or missing database attributes.These failures reflect difficulty aligning natural-language expressions with schema fields.
  • Schema and Value Errors: Table-related errors include incorrect table selection, unnecessary or missing tables, join-condition mismatches, and join-type mismatches.A representative case requires LEFT JOIN rather than INNER JOIN to retain students without courses and uses the correct student identifier in the join.
  • Schema and Value Errors: Value-related errors occur when models misinterpret attribute values or fail to match their database representations and formats.The taxonomy distinguishes value mismatch from data-format mismatch.
  • Operators and Conditions: Operator-related errors include incorrect comparison operators and misinterpreted logical relationships in SQL conditions.The examples distinguish operators such as > versus >= and show how AND precedence can change query meaning.
  • Operators and Conditions: Condition-related errors include missing or mismatched explicit conditions and mishandled implicit conditions.They arise when models fail to fully interpret conditions expressed in the natural-language query.
  • Functions and Clauses: Function-related errors involve incorrect use of aggregate, window, or date/time functions, while clause-related errors involve missing or redundant SQL clauses.The affected clauses include GROUP BY, ORDER BY, and HAVING.
  • Subqueries: Subquery-related errors include missing or mismatched subqueries and partial queries that omit required parts of the final result.One example returns student identifiers and counts but requires an external join to return student names.

4 NL2SQL-BUGs Overview

NL2SQL-BUGs is a benchmark for detecting and classifying semantic errors in NL2SQL translations. It combines expert-annotated correct and incorrect examples with hierarchical error labels and a multi-step curation process.

  • NL2SQL-BUGs distinguishes semantically correct from incorrect NL2SQL translations for error-detection research.
  • Attribute-related and table-related errors are most frequent, while function-related and clause-related errors are less frequent but can cause major semantic discrepancies.
  • Each instance includes a natural-language query, database, SQL query, correctness label, and detailed error types for incorrect queries.
  • The dataset was curated from BIRD through gold-SQL verification, model-generated queries, execution-result comparison, deduplication, and expert annotation.
  • Errors are classified hierarchically into 9 major categories and 31 subcategories after executable queries with incorrect results are identified as semantic errors.
  • The benchmark contains 1,019 correct examples and 999 incorrect examples with semantic errors.

5 Experiment

The experiments evaluate large language models on binary semantic-error detection and fine-grained error-type identification. Models achieve roughly 75% accuracy for distinguishing correct from incorrect SQL but remain substantially weaker at identifying specific error types.

  • The study evaluates state-of-the-art open- and closed-source LLMs on the NL2SQL-BUGs semantic-error detection task.
  • The binary task uses overall accuracy plus positive and negative precision and recall to evaluate whether SQL queries are correct or erroneous.
  • GPT-4o and Claude-3.5-Sonnet are the most balanced models across semantic-error types, while Gemini-2.0-Flash reaches 76.81% accuracy with imbalanced recalls.
  • Models achieve around 75% accuracy in distinguishing correct and incorrect SQL but below 50% accuracy in identifying specific error types.
  • Condition-related and value errors are detected relatively well, whereas subquery-related and Other Errors show markedly weaker detection.
  • GPT-4o and Gemini-2.0-Flash share 76.81% overall accuracy, but GPT-4o performs substantially better at error categorization.
  • Fine-grained results show weaknesses involving explicit condition errors, join-type mismatches, and function-related errors requiring database knowledge.

6 Related Work

Prior NL2SQL research progressed from rule-based and neural methods to pretrained language models and LLMs, while related error-analysis approaches addressed explanation, collaboration, and visualization.

  • Early NL2SQL systems relied on rule-based templates and semantic parsing, which often struggled with complex queries.
  • Deep learning and pretrained language models substantially improved NL2SQL translation accuracy.
  • LLM-based NL2SQL systems still face significant errors in complex scenarios and real-world deployments.
  • Prior NL2SQL error-analysis approaches include natural-language explanation, human-machine collaboration, and visual analytics.

7 Conclusion

The paper introduces semantic error detection for NL2SQL, proposes the NL2SQL-BUGs benchmark and taxonomy, and reports previously undetected errors in BIRD and Spider while highlighting LLM limitations.

  • 106 BIRD errors (6.91%) and 16 Spider errors (1.55%) were uncovered as previously undetected semantic errors.
  • The proposed two-level taxonomy organizes semantic errors into 9 main categories and 31 subcategories.
  • NL2SQL-BUGs contains 2,018 expert-annotated examples for evaluating NL2SQL semantic error detection.
  • Experiments highlight limitations of current LLM-based models in detecting NL2SQL semantic errors.

A Detected Errors in NL2SQL Benchmarks

The paper identifies semantic errors in the BIRD and Spider development benchmarks and documents them through indexed queries and concrete annotated examples.

  • Table 3 lists indices of BIRD and Spider development queries identified as containing semantic errors.
  • These errors can make query results deviate from the answers expected from the natural language question.
  • Table 4 provides examples containing the natural-language query, incorrect SQL statement, and color-coded error annotations.

B.1 Value-related Errors

The examples illustrate value-related and condition-related semantic errors, including mismatched formats, types, attributes, values, and omitted conditions.

  • Value-related Errors: The value-related example uses MM/DD/YY instead of YYYY-MM-DD for the date and treats numeric 30 as the string ‘30’.
  • Condition-related Errors: The condition-related example uses the wrong attribute and value instead of matching the course name.
  • Condition-related Errors: The same condition-related query omits the grade IS NOT NULL check required for completed courses.

B.3 Function-related Errors

The function-related example shows semantic errors involving aggregate filtering and numeric precision, while the benchmark tables document detected errors and samples.

  • Function-related Errors: The incorrect query places AVG in WHERE instead of HAVING and omits ROUND for two-decimal precision.
  • Function-related Errors: The corrected query applies ROUND(AVG(e.grade), 2) and filters grouped averages with HAVING AVG(e.grade) > 85.
  • Table 3 is identified as listing semantic errors in the BIRD and Spider benchmarks, while Table 4 presents samples of detected errors.
Loading 2503.11984v2…