CHASE-SQL: Multi-Path Reasoning and Preference Optimized Candidate Selection in Text-to-SQL

W5. Method Ensemble vs. Module Combination:

Ensemble components vs end2end methods (ours): Thank you for your comment and the reference. While studying single pipeline components is valuable—given that text-to-SQL pipelines typically involve multiple components or steps, and different combinations for each step can yield varying results, as demonstrated in NL2SQL360—our focus lies elsewhere. Specifically, we concentrate on the optimization of a “coarse-grained methods ensemble” rather than single pipeline optimization (referred to as a ‘method’ by the reviewer and ‘generator’ in our text). Our framework emphasizes building a diverse candidate pool by leveraging outputs from various generations, with pipeline optimization (or component ensemble) considered orthogonal to our approach. If a generator contributes unique answer candidates, it can be integrated into our framework to further enhance performance. As explained in W4, we selected three generators to demonstrate the effectiveness of our approach, showing that our “coarse-grained methods ensemble” can outperform the best results from other single generator baselines. Compared to NL2SQL360, our design search space is significantly larger, supporting multiple prompts or pre/post-processing techniques simultaneously rather than selecting a single configuration for each component. This broader flexibility enables our framework to achieve superior performance, with a 73% score on BIRD-bench compared to NL2SQL360's 58.5%, even though NL2SQL360 used both GPT-4 and fine-tuned PLMs, while we used Gemini-1.5. In summary, our work proposes a novel framework that improves Text-to-SQL accuracy, while leaving room for future pipeline optimizations to enhance our framework further when incorporated as additional generators.

W6. Self-Consistency and Self-Reflection Methods

Self-Consistency and Self-Reflection Methods are not our novel contribution: In our paper, we used self-consistency as a robust “baseline” to compare with our novel pairwise selector model; self-consistency was never presented as our contribution, and we will mention this in the updated paper to avoid confusion. Additionally, for value retrieval, we explicitly referenced in lines 144 and 145 that our approach aligns with that proposed in the CHESS paper. Similarly, regarding the self-correction module, as noted in line 250, we stated that self-reflection is a commonly used method for enhancing Text-to-SQL approaches. Our key contributions lie in the multipath candidate generation and the pairwise selector model, which not only outperforms the well-established self-consistency but also surpasses the LLM-as-judge approach, as shown in Table 7. Additionally, self-reflection, “fixer”, is one of working components of many text-to-SQL methods, is not our key novelty. We hope this clarification addresses any confusion.

W7. Algorithm 3 Execution Comparison

Algorithm 3 Execution Comparison: Large language models can exhibit order bias when selecting between candidates, as noted in prior works such as "Unveiling Selection Biases: Exploring Order and Token Sensitivity in Large Language Models"[2]. For example, when an LLM is presented with (ci, cj), it may choose ci, but when the order is reversed to (cj, ci), it may favor cj, reflecting a bias towards the first candidate. If we only consider one directional comparison, either (ci, cj) or (cj, ci), this bias can skew the selection process, favoring one query simply due to its position and thus affecting our scoring mechanism. By considering both orderings, we assign scores to each candidate more equitably, effectively reducing order bias. To illustrate this impact, we conducted a one-way comparison, and the final execution accuracy on the dev set dropped from 73.01% to 71.12%, underscoring the importance of mitigating order bias. We also included this experiment in the paper to help the readers understand the importance of two-way comparison.

[2]: Wei, S.-L., Wu, C.-K., Huang, H.-H., & Chen, H.-H. (2024). Unveiling Selection Biases: Exploring Order and Token Sensitivity in Large Language Models.

W8. Experiments

In order to address your concern about other Large language models, we have implemented the CHASE-SQL method using the Mistral-large model. For the pairwise selector model we trained a Qwen2.5-coder 7B model. The results highlight that our pairwise query selection approach significantly improves performance, achieving SOTA results with open-source models on the BIRD benchmark, as detailed below: We have included the open source table in the updated draft.

We hope our detailed response addresses your concern. We would greatly appreciate it if you could update the scores accordingly

Questions:

• We have already included the ablation studies with open-source models as provided above.
• Regarding the ensembling of different methods, we have included a detailed ablation study in Table 7, demonstrating how performance changes when each generator is removed from our proposed method.
• As stated in our response to w6, self-correction and database value retrieval are not contributions of our work, so comparisons with previous works on these aspects are not relevant. For candidate generators, as highlighted in the table above (our first comment), we compared our novel CoT approaches with two baselines, demonstrating the significance of each CoT. Please note that our key contributions are the pairwise selection and multi-path reasoning generation methods.

W3. Instance-Aware Synthetic Example Generation

Thank you for the invaluable comments and also the references. We noticed the gap in our exposition as well as in the study, and we tried again to clarify the following regarding the novelty and effectiveness of our synthetic example generation strategy: In Section 3.3 > Online Synthetic Example Generation, we have clarified how our example question-SQL pairs are different from few-shot “demonstrations” proposed by DIN-SQL (please follow the citation in the text). Manually crafted few-shot demonstrations are effective in specifying the task and step-by-step process, whereas we generate/synthesize more common few-shot examples, illustrating the mapping between input and output. We have added more references, including your pointers. Our approach, unlike typical few-shot in-context learning (ICL) for Text-to-SQL, generates way more than 5 examples, and we draw the connection to the recent many-shot ICL work for other application domains (QnA. translation, summarization, etc.). While prior few-show ICL focuses on retrieval of relevant examples by question similarity or some variations of it (e.g., masking the keywords, as suggested in CodeS and others), we instead generate examples on-the-fly per incoming question. This means we bypass the error-prone selection process and pass all the examples we generate for the given question. We found this strategy and our recipe for example generation effective in terms of the final accuracy.

In the original text, we tried to emphasize that we are more strictly guiding the generation process for diversity of the SQL structures and the use of schema elements. This is very different from prior works for synthetic data augmentation, where LLMs are asked to come up with feasible questions and generate SQL for them; and then LLMs are asked to fill in the blanks using relevant schema elements given a universal set of SQL templates – here the templates are very limited in complexity (1 table, unnested queries only). Instead, we explicitly look to generate SQLs and examples following guidelines detailed in our text. Diversity is the key for us to help generations. To that end, thank you for the suggestion - we have also added another ablation study comparing our examples (ICL quality) vs. similar examples drawn from training dataset, which is the common techniques used for ICL works using BIRD bench (Appendix Table 9) – note that CodeS also uses training data for own ICL evaluation. Since they included a cross-domain data augmentation strategy (not evaluated for BIRD), we also try to implement the strategy (CodeS, thank you for the reference) to show how the limited complexity examples perform against us.

<Appendix Table 9. Comparison study of the proposed synthetic example vs. selected training data examples vs. example synthesis technique from CodeS>: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/OS_figure1.png

<Appendix Figure 26 to show how our generated example SQL feature distribution compares with the ground truth and other example generation strategy>: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/OS_figure2.png

W4. Method Ensemble

Ensemble and why not more: We should highlight that, based on our experiments reported in Table 1, our current operating regime represents a near-optimal trade-off between cost and accuracy. Though our framework is designed in a way that users can freely add more methods. As shown in Table 1, the Pass@21 performance with our three generators reaches approximately 83% accuracy, significantly surpassing the current SOTA performance. This highlights that our generators already deliver high performance, and the primary challenge lies on the selection side. Here, our innovative pairwise selector model achieves better performance than the self-consistency approach. Additionally, as detailed in our error analysis in the Appendix and similar analyses in works like CodeS and CHESS, there is a notable presence of ambiguous questions and incorrect golden SQL queries, underscoring that 83% accuracy is already very high. Thus, adding more generators of some variations would add marginal or no improvements, unless they perform differently from our generators on the ambiguous questions. Additionally, adding more candidate generators will induce more cost not only in the generation step but also in the selection step, which makes it less desirable given the diminishing return.

W2. Chain-of-Thought: The Chain-of-Thought (CoT) approach has also been extensively applied

Novelty in DC and QP CoT: Thank you to the reviewer for highlighting relevant works in Chain-of-Thought (CoT) approaches. While our proposed CoT methods are conceptually similar to previous works, they differ significantly in the design of the reasoning process, as the reasoning steps and implementation of the 'chain of thought' in our approach are fundamentally distinct: DC prompt reasons the task of Text-to-SQL as solving sub-SQL and writing pseudoSQLs recursively; QP prompt reasons the task “Text-to-SQL” as the way database engine execute the SQL; all of these reasoning methods are significantly different compared with CoE-SQL, DIN-SQL, and ACT-SQL. We note that CoE-SQL is inherently designed for multi-turn Text-to-SQL, where it depends on iterative unit edits across dialogue turns, which differs from our single-turn task on datasets like BIRD and Spider, so this approach is not considered as CoT for single-turn text-to-SQL. Similarly, CHESS uses zero-shot CoT without intermediate reasoning steps, which we have already addressed in our comparative analysis and compared with as our baseline (Table 4) and table below. Finally, ACT-SQL proposes a specific CoT prompt, which is similar to DIN-SQL, so we decided to compare our CoTs with this method to showcase our innovation. For this comparison, as ACT-SQL is only proposed for the Spider dataset, we implement their CoT for the BIRD benchmark using the same set of few-shot samples as our CoTs and provide the results in the table below. Our approach outperformed ACT-SQL CoT, demonstrating the effectiveness of our CoT design for Text-to-SQL. Results below demonstrate the effectiveness of our proposed CoT designs in comparison to the previous works, where the detailed step-by-step decomposition resulted in roughly 2% improvement over the well-established ACT-SQL (DIN-SQL) CoT. Additionally, our online example generation approach significantly outperforms the baseline with 6% gap:

Finally, we would like to also compare our approach directly to the works mentioned by the reviewer,using execution accuracy as our metric, to showcase our improvements:

We sincerely thank the reviewer for their insightful comments and valuable suggestions. With your valuable inputs, the quality of our paper can be improved significantly.

Novelty Concerns: In response, we would like to further clarify the unique contributions of our CHASE-SQL method. Our key contributions lie in the multipath candidate generation and the pairwise selector model framework. The proposed pairwise selector model, along with its selection algorithm detailed in Algorithm 3 outperforms both the well-established self-consistency method and the LLM-as-judge approach, as demonstrated in Table 7. Additionally, we introduce three novel query generators capable of producing a diverse set of candidate queries, achieving a notable upper bound accuracy of 83%. There are different designs of CoT, and our two CoT-based methods outperform prior CoT approaches for Text-to-SQL; in particular, our divide-and-conquer prompting method extends to arbitrarily complex questions by recursively breaking them into simpler sub-problems. Furthermore, we propose a novel online synthetic example generation method that dynamically generates examples based on the input question during inference. This innovative approach outperforms previous retrieval-based methods, further advancing the field.

Below and in our next comments we will provide detailed responses to the mentioned weaknesses:

W1. Divide-and-Conquer: This concept has been widely utilized in various NL2SQL studies ...

Novelty in Divide and Conquer prompt: We appreciate reviewer bringing other "decomposition methods”. We first wish to differentiate our divide-and-conquer chain-of-thought prompting from the approaches mentioned by the reviewer, such as DTS-SQL and DEA-SQL, which are different ``decomposition’’ approaches than ours. These methods, as the reviewer noted, rely on a decomposition approach in handling the Text-to-SQL task by breaking it into multiple stages—typically schema linking, classification, and SQL generation—based on the intuition that LLMs may struggle with large amounts of information in a single prompt. While effective in some aspects, all pipeline-based approaches introduce challenges related to error propagation. Specifically, any error in an early stage, such as schema linking, can cascade, potentially impacting all subsequent stages. Recent studies, such as "The Death of Schema Linking? Text-to-SQL in the Age of Well-Reasoned Language Models [1]," highlight how such pipeline-based approaches may diminish performance with advanced LLMs like Gemini and GPT-4, which can now handle complex reasoning tasks with larger token capacities. Our method, in contrast, does not segment the task across multiple steps or require intermediary schema-linking phases. Instead, we incorporate all necessary context directly into the SQL generation phase and employ divide-and-conquer logic within a single LLM prompting call. This chain-of-thought prompting minimizes error propagation by ensuring a holistic approach where decomposition happens solely at the SQL generation step, improving overall reasoning performance, as evidenced in Table 4 of our results.

[1] Maamari, K., Abubaker, F., Jaroslawicz, D., & Mhedhbi, A. (2024). The Death of Schema Linking? Text-to-SQL in the Age of Well-Reasoned Language Models.

Furthermore, among other approaches that decompose within the SQL generation phase, such as TKK, our method diverges in that we do not rely on fine-tuning. While TKK performs decomposition via multi-task learning for each SQL clause, it still generates only a SQL query, without chain-of-thought decomposition. Moreover, fine-tuning techniques, although beneficial in certain contexts, can reduce diversity in output, limiting the effectiveness of ensemble and selection strategies such as ours. Our experiments, where we fine-tuned the Gemini-1.5-pro model and compared its Pass@20 performance with our combined generator approach, demonstrate the advantages of our model’s inherent diversity and reinforce the effectiveness of our method, which is demonstrated below:

Fine-tuning vs CHASE-SQL prompts Pass@20: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/finetuning_vs_chase.png

Finally, below we include the detailed comparison of our work with the methods mentioned by the reviewer using execution accuracy:

Thank you for addressing my previous feedback and providing the new experiments. Based on your response, I’d like to raise my score to 5.

We sincerely thank the reviewer for their valuable comments and suggestions, we really appreciate it.

The main concern is the cost and latency of CHASE-SQL ...

Regarding the cost analysis of our approach, we compared it to the CHESS method, a former SOTA method on text-to-SQL benchmarks, and demonstrated that our method consumes fewer tokens, as shown below. Additionally, the cost of generating queries with human annotation remains significantly higher than the cost achieved with our proposed approach. Moreover, we should consider that LLM cost has been reduced significantly during the past years, so we speculate that approaches with higher inference-time computation like ours will be adopted more. Additionally, we provided a detailed latency analysis of our proposed methodology on all of the BIRD development set databases. Based on this analysis, we identified that for the databases where query fixing was required more than the other databases the latency is higher as the query fixing step is a sequential process. To address concerns about token usage and latency, we have included a detailed analysis below:

Detailed Cost Analysis of CHASE-SQL: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/token_usage.png

Detailed Latency Analysis of CHASE-SQL: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/Latency_Analysis.png

O(n^2) LLM calls, where n is the total number of generated candidates ...

For SQL generation, the 21 candidates are iid samples, allowing for parallel generation with negligible overhead compared to single-candidate generation. While the pairwise comparison step could have a worst-case complexity of O(n^2), as noted in line 3 of Algorithm 3, comparisons are skipped for queries with identical execution results, significantly reducing computational time since most candidate queries yield the same results.

We hope our response addresses your concern. We would greatly appreciate it if you could update the scores accordingly.

Questions:

• How many examples: For each of the Query plan CoT and the divide and conquer CoT we included 8 examples in the prompt. For OS, we generated a total of 75 examples per user question. We discuss the choice in Appendix A.12 and Tables 8 and 9.

• What is the total number of tokens processed: Figure above provides the average number of tokens used for our approach on all of the BIRD databases.

I appreciate the authors' effort in adding new results to the paper. However, I have concerns regarding the token cost analysis presented for the Chase-SQL Generator.

According to the figure, for the financial database, the total number of input tokens for the Chase-SQL Generator amounts to approximately 0.16 million across 106 questions. This calculates to an average of about 1,510 tokens per question. This number is significantly lower than what I expected.

To better understand this, I analyzed the generator input tokens, which comprise the lengths of the Divide-and-Conquer Prompt, Query Plan Prompt, and Few-Shot Prompt. Specifically, I generated a database description string for the financial database, which includes basic schema information, simple column descriptions, and a few value examples, totaling 1,204 tokens.

Upon integrating this database description into the provided prompt from your paper, I calculated the prompt token length for a single question as follows:

Divide-and-Conquer Prompt: 2,055 tokens Query Plan Prompt: 1,814 tokens Few Shots Prompt: I compute the token length for one example in the paper, which has 126 tokens. The author says they generate 75 examples for each user question. Thus, this prompt is at least 9450 tokens.

Based on these components, the total token count per question exceeds 13319 tokens (much larger than the figure shows). Could you please clarify this, or have I misunderstood the token computation method described?

Thank you so much for your detailed comments that have helped us to improve our submission, we truly appreciate them. We hope the detailed answers and additional results provided below address your concerns. We kindly ask you to consider the possibility of a score adjustment

it is not possible to verify whether it is good to fine-tune open source for the above methods ...

To address your point regarding the performance of our proposed methodology on open-source models, we have included new results using the Mistral-large model in combination with the fine-tuned Qwen2.5-coder model as the pairwise selector. These results align with the findings reported in the paper (Table 4 and Table 6) for other models, demonstrating that our proposed methods—Divide-and-Conquer Prompt, Query Plan, and Online Synthetic Prompt—consistently outperform the Basic Prompt by a significant margin. Furthermore, our pairwise selection approach surpasses the well-established self-consistency method when evaluated with both the fine-tuned Gemini-1.5-flash and Qwen-2.5-coder models.

We note that the reported results below are the SOTA performance with open-source LLMs on the BIRD benchmark. We have reported the performance by generating the candidate queries with the Mistral large model and included two selectors of Gemini-flash and Qwen-2.5-coder models below: We also included this open source table results in our updated paper.

Questions:

• Open Source model: Please see the results above for open-source LLMs which is also included in the updated paper.
• Query-specific database values retrieval: We include an example here and also updated the Appendix section to include more details. Value retrieval is an important step in our proposed pipeline as it can help to identify the relevant columns and tables to the user’s question and also provides the correct filtering for the SQL conditions. For the question: “What is the highest eligible free rate for K-12 students in the schools in Alameda County?” from the “california_schools” database, using the value retrieval, we get the following results, showing the retrieved database values from different columns:
  • SOC Type: Preschool
  • EIL Name: Preschool
  • Schools: Preschool, MethodSchools, Alameda County Community, Alameda County Opportunity, Alameda High
  • Mail Street Address: 4600 Student Lane
  • Street Address: 4600 Student Lane
  • Mail City: Alameda
  • City: Alameda
  • Grades Served: K-12
  • Grades Offered: K-12
  • County: Alameda
  • Districts: Alameda Unified, Tri-County ROP
• Design choice of schema union in Selection Agent: For the pairwise comparison of the candidates, we have decided to use the union schema, as columns in the database that are not used by both candidates would not affect the comparison decision. This way we also reduce the token usage and avoid any irrelevant information in the prompt.

We sincerely thank the reviewer for their valuable feedback and comments on our paper.

The paper does not include the cost of the proposed framework ...

As for the cost analysis, we have conducted a detailed token usage (noting that dollar costs are proportional to that) estimation comparing our method with the CHESS method, demonstrating that our approach uses fewer input tokens than previous SOTA approaches, as provided below: We have also included the figure in appendix of updated draft.

Link to the Cost Analysis experiment: https://anonymous.4open.science/r/CHASE-SQL-REBUTTAL-7EF5/token_usage.png

selection of open-source models has not been discussed ...

As requested by the reviewer, to further demonstrate the effectiveness of our approach with open-source large language models, we have conducted the performance analysis of CHASE-SQL using the Mistral-large model. For the pairwise selector model we trained a Qwen-2.5-coder 7B model and also compared it with Gemini-1.5-flash model. The results highlight that our pairwise query selection approach significantly improves performance compared to well-established self-consistency, achieving SOTA performance with open-source models on the BIRD benchmark, as detailed below: We have included the open source table in the updated draft.

The description of the Query Fixer module is relatively brief ...

Thank you for your insightful suggestion. We have updated the paper by adding a limitations section and details about the query-fixing algorithm. Regarding the limitations of our work, as you noted, most current text-to-SQL systems assume that user questions are inherently answerable, and we have now included this as a limitation in the paper. Furthermore, the current framework has several limitations that are open avenues for future work. Adapting to additional SQL dialects poses challenges due to their unique syntactic and semantic variations, necessitating automated adaptation techniques for the proposed modules. Reducing latency is another critical area, which could be achieved through optimized prompt engineering and the use of smaller models via distillation. Finally, integrating agentic design into foundation model development could mitigate train-test mismatches by enabling models to actively query and refine their understanding during training, thereby enhancing robustness and alignment with real-world applications.

We hope our response adequately addresses your concerns. If all your concerns have been resolved, we would greatly appreciate it if you could update the scores accordingly.

Questions:

• error analysis: Sure, we will bring a portion of the error analysis section in the main paper for the final version of our paper.
• Appendix A.3: Thank you for bringing this to our attention. We believe this figure requires additional explanation to avoid confusion. In this section, we present the number of samples across different databases where only one of the candidate generators produces a correct result, meaning the other two generators fail to provide a correct answer. A value of zero for any generator in this figure indicates that whenever that generator produces a correct result, the other two generators also manage to generate at least one correct answer. We updated this section in the Appendix section accordingly.
• prompt used in the Query Fixer: As you suggested, including examples of each error type can certainly enhance the performance of the fixer module. In our approach, since the majority of the error cases we observed were related to "column not found" errors, we focused on including most of the few-shot samples from this category.
• "CHASE" abbreviation: Thank you for your suggestion regarding the name and your consideration of potential confusion. Our method has already been submitted to public leaderboards and gained visibility under its current name, so changing it at this stage might be less ideal. However, we will carefully evaluate your feedback and give it further thoughtful consideration.