Interactive Text-to-SQL Generation via Editable Step-by-Step Explanations

Yuan Tian, Toby Jia-Jun Li, Jonathan K. Kummerfeld, Tianyi Zhang

Abstract

Relational databases play an important role in business, science, and more. However, many users cannot fully unleash the analytical power of relational databases, because they are not familiar with database languages such as SQL. Many techniques have been proposed to automatically generate SQL from natural language, but they suffer from two issues: (1) they still make many mistakes, particularly for complex queries, and (2) they do not provide a flexible way for non-expert users to validate and refine incorrect queries. To address these issues, we introduce a new interaction mechanism that allows users to directly edit a step-by-step explanation of a query to fix errors. Our experiments on multiple datasets, as well as a user study with 24 participants, demonstrate that our approach can achieve better performance than multiple SOTA approaches. Our code and datasets are available at https://github.com/magic-YuanTian/STEPS.

Introduction

While Large Language Models (LLMs) have become remarkably proficient at generating SQL from natural language, they still struggle with complex, nested, or ambiguous queries. When a model makes a mistake, non-expert users face a "black box" problem: they often cannot identify why the query is wrong and lack an intuitive way to fix it. Existing human-in-the-loop solutions are often either too constrained (e.g., fixed menus) or too open-ended (e.g., requiring new natural language prompts). This paper introduces STEPS, a framework that bridges this gap through editable, step-by-step explanations in plain English.

Methodology

The STEPS framework follows a three-stage pipeline designed to make SQL generation transparent and interactive.

The STEPS interactive workflow: (1) Ask, (2) Understand, and (3) Repair.

1. Explanation Generation

When a user submits a question, STEPS generates an initial SQL query and uses a rule-based method to decompose it into individual clauses. Crucially, it reorders these clauses to match the SQL execution order (e.g., starting with FROM, then WHERE, then SELECT) rather than the standard syntactic order. This creates a logical narrative that is much easier for humans to follow and debug.

The process of decomposing and reordering SQL into natural language steps based on execution order.

2. Interactive Editing

The user reviews these steps alongside the query results. If a step is incorrect (e.g., a filter condition is reversed), the user directly edits the text. This "divide and conquer" approach allows users to pinpoint and isolate errors, preventing the model from introducing new mistakes in unrelated parts of the query during the correction phase.

3. SQL Correction and Composition

To translate edited text back into code, STEPS uses a hybrid approach:

  • Direct Transformation: For simple "atomic" edits like changing a column name or a numerical value, a fast algorithm updates the SQL directly. This method is approximately 22,000 times faster than neural models.
  • Text-to-Clause Model: For more complex edits, a neural model generates a new SQL clause from the corrected explanation.
  • Rewriting Rules: Finally, the system merges the corrected clauses back into a valid SQL query, handling joins and redundant clauses.

Evaluation

The researchers evaluated STEPS using several prominent Text-to-SQL benchmarks, including Spider (complex cross-domain queries), Spider-DK, Spider-Syn, and WikiSQL. Additionally, a user study was conducted with 24 participants ranging from novices with no SQL knowledge to fluent experts.

Results

  • State-of-the-Art Performance: In simulations on the Spider dataset, STEPS reached 97.9% exact set match accuracy, outperforming previous interactive SOTA systems by over 30%.
  • Human Efficiency: In the user study, participants using STEPS completed 2x more tasks correctly than those using a drop-down menu system and 4x more than those using a multiple-choice system.
  • Expertise Independence: One of the most striking findings was that users with zero SQL knowledge performed as well as experts when using STEPS, effectively leveling the playing field through the natural language bridge.

Conclusion

STEPS demonstrates that providing editable, execution-ordered explanations significantly improves the accuracy and user experience of Text-to-SQL systems. While currently limited to single-turn interactions, future work will extend the system to support multi-turn dialogues and a broader range of advanced SQL operations.

Summary updated

Abstract

Relational databases play an important role in business, science, and more. However, many users cannot fully unleash the analytical power of relational databases, because they are not familiar with database languages such as SQL. Many techniques have been proposed to automatically generate SQL from natural language, but they suffer from two issues: (1) they still make many mistakes, particularly for complex queries, and (2) they do not provide a flexible way for non-expert users to validate and refine incorrect queries. To address these issues, we introduce a new interaction mechanism that allows users to directly edit a step-by-step explanation of a query to fix errors. Our experiments on multiple datasets, as well as a user study with 24 participants, demonstrate that our approach can achieve better performance than multiple SOTA approaches. Our code and datasets are available at https://github.com/magic-YuanTian/STEPS.
BibTeX
@inproceedings{tian2023interactive,
  author = {Yuan Tian and Toby Jia-Jun Li and Jonathan K. Kummerfeld and Tianyi Zhang},
  title = {{Interactive Text-to-SQL Generation via Editable Step-by-Step Explanations}},
  booktitle = {Conference on Empirical Methods in Natural Language Processing},
  year = {2023},
  url = {https://openreview.net/forum?id=ZWAzcpbhF7},
}

Loading related content...

Figure