Interactive Text-to- SQL Generation via Editable Step-by-Step Explanations (2023)

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 natural language interfaces significantly lower the barrier to accessing databases and performing data analytics tasks for users who are not familiar with database query languages. many approaches have been proposed for generating sql queries from natural language @xcite @xcite @xcite . using recent large language models, systems have reached 86.6% execution accuracy @xcite on the spider benchmark @xcite . however, the rate of improvement has slowed, with a gain of only 10% since mid-2021. this is partly due to the inherent ambiguity of natural language and the complex structure of sql queries (e.g., nested or joined queries). thus, it is challenging to generate a fully correct query in one step, especially for complex tasks @xcite . there has been growing interest in developing "human-in-the-loop" approaches that elicit user feedback to guide sql generation. however, most approaches only support feedback in constrained forms, e.g., answering multiple-choice questions @xcite @xcite , changing sql elements in a drop-down menu @xcite , etc. such constrained feedback is not sufficient to fix many complex errors in real-world sql tasks. one exception is nl-edit @xcite , which allows users to provide feedback as new utterances. however, since the feedback is open-ended, interpreting it can be just as hard as processing the original request. in this paper, we seek to strike a balance between constrained feedback and open-ended feedback by proposing a new interaction mechanism: editable step-by-step explanations. fig. 1 illustrates our idea. this mechanism consists of three core components: (a) a text-to-sql model, (b) an explanation generation method, and (c) a sql correction model. our key insight is that using a step-by-step explanation as the basis to suggest fixes allows users to precisely specify where the error is and how to fix it via direct edits. this not only saves users' time but also makes it easier for the model to locate the error and apply fixes. based on this idea, we implemented an interactive sql generation and refinement system called steps. steps adopts a rule-based method to generate step-by-step explanations and uses a hybrid rule/neural method to convert a user-corrected explanation back to a sql query. an evaluation with a simulated user on spider @xcite shows that steps can achieve 97.9% exact set match accuracy, outperforming prior interactive text-to-sql systems-misp, diy, and nl-edit-by 33.5%, 33.2%, and 31.3% respectively. we further evaluate steps on other datasets, including spider-dk @xcite , spider-syn @xcite , and wik-isql @xcite . steps consistently achieves at least 96% exact set match accuracy and execution accuracy across all datasets. finally, we conducted a within-subjects user study with 24 real users. we found that within the same amount of time, steps helped users complete almost 2x and 4x more tasks correctly than diy and misp respectively, foot_0 with significantly higher self-reported confidence and lower mental load. this work makes the following contributions: (1) we propose a new interaction mechanism for the text-to-sql task; (2) we develop an interactive text-to-sql system based on the new interaction mechanism and a new training method for sql correction; (3) we conduct a comprehensive evaluation with both simulated and real users and demonstrate its effectiveness over state-of-the-art interactive systems. our dataset and code are publicly available. ## approach fig. 2 provides an overview of steps. given a natural language (nl) question, steps invokes a text-to-sql model to generate an initial sql query. then, it decomposes the generated sql query into individual query clauses and re-orders them based on their execution order. each clause is then translated into an nl description of the underlying data operation, which is then used to form a step-by-step explanation. by reading the nl explanation along with the query result, users can easily understand the behavior of the generated query and locate any errors, even if they are unfamiliar with sql. if one step is incorrect, users can directly edit its explanation to specify the correct behavior. steps will then regenerate the clause based on the usercorrected explanation and update the sql query, rather than regenerate the entire query from scratch. if multiple steps are incorrect, the user can add, remove, and modify all steps as needed. ## experiment to evaluate the performance of steps, we conducted quantitative experiments on the spider benchmark @xcite with three sota interactive sql generation approaches-misp @xcite , diy @xcite , and nl-edit @xcite . we also explored the impact on steps of different text-to-sql models, different task difficulties, and four different benchmarks @xcite @xcite . finally, we conducted an ablation study for our hybrid method. ## user study to evaluate the usability and accuracy of steps when interacting with real users, we conducted a within-subjects user study with 24 participants. foot_4 ## analysis of post-study survey responses we analyzed the post-task survey responses and interview recordings to understand why participants performed much better when using steps compared with using misp and diy. 17 participants strongly agreed that seeing the natural language explanation helped them understand the sql query, and 22 participants explicitly wrote that they highly appreciated the step-by-step explanations provided by steps, since these explanations made sql queries more understandable, editable, and learnable. p12 wrote, "i liked that it shows the steps in human language so if there is a mistake i can edit it easily. also, it was nice to see the generated sql code i believe i could learn sql using this tool also." in contrast, 14 of 24 participants reported it was hard to understand and validate the generated sql queries when using misp or diy. p1 wrote, "sometimes it generates very complex sql that is difficult to read and check." p9 wrote, "sometimes it gives the wrong answer. as i'm no expert in sql, i couldn't tell instantly if the queries were wrong, so i had to go back to the data and check manually." 12 participants reported that the feedback elicitation mechanism in misp was not very efficient. p16 wrote, "i have to keep answering yes or no questions when using misp." 11 of them reported the drop-down menus provided by diy limited their ability to make changes. p3 said, "[it is] hard to know how to make changes / resolve issues that were not covered by the drop-down menus." ## discussion both the quantitative experiments and the user study demonstrate steps can significantly improve the accuracy of sql generation. this is largely attributed to the interaction design, which allows users to precisely pinpoint which part of the sql is wrong and only regenerates the incorrect clauses rather than the entire sql query. in contrast, existing approaches do not support expressive ease or error isolation. users either cannot regenerate new content (e.g., diy), or can only regenerate the entire query rather than just the erroneous part (e.g., misp). @xcite showed that this lack of error isolation often introduces new errors, which frustrates users and makes errors hard to fix. error analysis. while simple errors are prevalent in sql generation, our ablation study ( in our automated user simulation, steps failed in a few cases when the text-to-clause model predicted the wrong clause type. for example, the paraphrased ground truth explanation of one step was: "ensure that all categories where the total cost of therapy exceeds 1000 are included." the text-to-clause model predicted a where clause instead of a having clause. in the user study, one common challenge arose when multiple tables in the database had the same column name. if users did not look carefully at the database schema, they may have not explicitly indicated the table to be used. that creates an ambiguity for the model. other datasets and domains. our system should work for any sql dataset, as our approach is domain-agnostic and covers general sql structures. for other forms of code, such as webapi @xcite and sparql @xcite , the general idea is applicable, but new models would be needed for (a) code generation, (b) explanation generation, and (c) code correction. ## conclusion this work presents steps, a new interactive approach for text-to-sql generation. steps decomposes a text-to-sql task into smaller text-to-clause tasks and enables users to validate and refine a generated query via editable explanations. experiments on four benchmarks and a user study show steps can significantly boost the accuracy of end-to-end models by incorporating user feedback. steps significantly outperforms three stateof-the-art approaches for interactive sql generation across all metrics considered.