CSR-SQL: Towards Table Content-Aware Text-to-SQL With Self-Retrieval

Abstract

Large language model-based (LLM-based) text-to-SQL methods have achieved important progress in generating SQL queries for real-world applications. When confronted with table content-aware questions in real-world scenarios, ambiguous data content keywords and nonexistent database schema column names within the question lead to the poor performance of existing methods. To solve this problem, we propose a novel approach towards table content-aware text-to-SQL with self-retrieval (TCSR-SQL). It leverages LLM's in-context learning capability to extract data content keywords within the question and infer possible related database schema, which is used to generate Seed SQL to fuzz search databases. The search results are further used to confirm the encoding knowledge with the designed encoding knowledge table, including column names and exact stored content values used in the SQL. The encoding knowledge is sent to obtain the final Precise SQL following multi- rounds of generation-execution-revision process. To validate our approach, we introduce a table-content-aware, question- related benchmark dataset, containing 2115 question-SQL pairs. Comprehensive experiments conducted on this benchmark demonstrate the remarkable performance of TCSR-SQL, achieving an improvement of at least 27.8% in execution accuracy compared to other state-of-the-art methods.

Keywords

artificial intelligence / data analysis / database technologies / natural language processing

Acknowledgements

This study is supported by the National Key Research and Development Program of China under (Grant 2023YFB3106504), Guangdong Pro-vincial Key Laboratory of Novel Security Intelligence Technologies under (Grant 2022B1212010005), the Major Key Project of PCL under (Grant PCL2023A09), Shenzhen Science and Technology Program un-der (Grants ZDSYS20210623091809029 and RCBS20221008093131089) and the project of Guangdong Power Grid Co. Ltd. under (Grants 037800KC23090005 and GD-KJXM20231042).

Conflicts of Interest

Yong Xu is an editorial board member for the journal, and was not involved in peer review process or the decision to publish this article. The authors declare that they have no confiict of interest.

Data Availability Statement

The data that support the findings of this study are available from the corresponding author upon reasonable request.

Endnotes

¹https://huggingface.co/DMetaSoul/Dmeta-embedding.

References

[1] V. Zhong, C. Xiong, and R. Socher, “Seq2sql: Generating Structured Queries From Natural Language Using Reinforcement Learning,” pre-print arXiv:1709.00103 (2017).

[2] S. Wu, O. Irsoy, S. Lu, et al., “Bloomberggpt: A Large Language Model for Finance,” preprint arXiv:2303.17564 (2023).

[3] T. Han, L. C. Adams, J.-M. Papaioannou, et al., “Medalpaca-an Open-Source Collection of Medical Conversational Ai Models and Training Data,” preprint arXiv:2304.08247 (2023).

[4] Z. Zheng, Y. Deng, D. Xue, Y. Zhou, F. Ye, and Q. Gu, “Structure- Informed Language Models Are Protein Designers,” in International Conference on Machine Learning (PMLR, 2023), 42317-42338.

[5] C. Shen, L. Cheng, Y. You, and L. Bing, “Are Large Language Models Good Evaluators for Abstractive Summarization?,” preprint arXiv: 2305.13091 (2023).

[6] L. Cheng, X. Li, and L. Bing, “Is GPT-4 a Good Data Analyst?,” preprint arXiv:2305.15038 (2023).

[7] X. Dong, C. Zhang, Y. Ge, et al., “C3: Zero-Shot Text-to-SQL With Chatgpt,” preprint arXiv:2307.07306 (2023).

[8] M. Pourreza and D. Rafiei, “Din-SQL: Decomposed in-Context Learning of Text-to-SQL With Self-Correction,” Advances in Neural In-formation Processing Systems 36 (2024): 36339-36348, https://doi.org/10.48550/arXiv.2304.11015.

[9] D. Gao, H. Wang, Y. Li, et al., “Text-To-SQL Empowered by Large Language Models: A Benchmark Evaluation,” preprint arXiv:2308.15363 (2023).

[10] C. Zhang, Y. Mao, Y. Fan, et al., “Finsql: Model-Agnostic Llms-Based Text-to-SQL Framework for Financial Analysis,” preprint arXiv: 2401.10506 (2024): 93-105, https://doi.org/10.1145/3626246.3653375.

[11] B. Wang, C. Ren, J. Yang, et al., “Mac-Sql: Multi-Agent Collabora-tion for Text-to-SQL,” preprint arXiv:2312.11242 (2023).

[12] H. Li, J. Zhang, H. Liu, et al., “Codes: Towards Building Open- Source Language Models for Text-to-SQL,” preprint arXiv:2402.16347 2, no. 3 (2024): 1-28, https://doi.org/10.1145/3654930.

[13] S. Talaei, M. Pourreza, Y.-C. Chang, A. Mirhoseini, and A. Saberi, “Chess: Contextual Harnessing for Efficient Sql Synthesis,” preprint arXiv:2405.16755 (2024).

[14] S. Patnaik, H. Changwal, M. Aggarwal, S. Bhatia, Y. Kumar, and B. Krishnamurthy, “Cabinet: Content Relevance Based Noise Reduction for Table Question Answering,” preprint arXiv:2402.01155 (2024).

[15] V. Kumar, Y. Gupta, S. Chemmengath, et al., “Multi-Row, Multi- Span Distant Supervision for Table+ Text Question Answering,” in Proceedings of the 61st Annual Meeting of the Association for Computa-tional Linguistics, Vol. 1 (Long Papers), 2023), 8080-8094, https://doi.org/10.18653/v1/2023.acl-long.449.

[16] P. Lewis, E. Perez, A. Piktus, et al., “Retrieval-Augmented Gener-ation for Knowledge-Intensive Nlp Tasks,” Advances in Neural Infor-mation Processing Systems 33 (2020): 9459-9474, https://doi.org/10.48550/arXiv.2005.11401.

[17] T. Yu, R. Zhang, K. Yang, et al., “Spider: A Large-Scale Human- Labeled Dataset for Complex and Cross-Domain Semantic Parsing and Text-to-SQL Task,” preprint arXiv:1809.08887 (2018).

[18] J. Li, B. Hui, G. Qu, et al., “Can Llm Already Serve as a Database Interface? a Big Bench for Large-Scale Database Grounded Text-to-Sqls,” Advances in Neural Information Processing Systems 36 (2024), https://doi.org/10.48550/arXiv.2305.03111.

[19] H. Li, J. Zhang,C. Li, and H. Chen, “Resdsql: Decoupling Schema Linking and Skeleton Parsing for Text-to-Sql,” in Proceedings of the AAAI Conference on Artificial Intelligence 37, no. 11, 2023, 13067-13075, https://doi.org/10.1609/aaai.v37i11.26535.

[20] H. Zhang, R. Cao, L. Chen, H. Xu, and K. Yu, “Act-Sql: In-Context Learning for Text-to-Sql With Automatically-Generated Chain-of- Thought,” preprint arXiv:2310.17342 (2023): 3501-3532, https://doi.org/10.18653/v1/2023.findings-emnlp.227.

[21] W. Zhai, X. Zhao, J. Liao, and Z. Chen, “Learn From Mistakes: Guidance on Zero-Shot Conversational Text-to-Sql,” in Proceedings of the 33rd ACM International Conference on Information and Knowledge Management, (2024), 4243-4247.

[22] Y. Xie, X. Jin, T. Xie, et al., “Decomposition for Enhancing Attention: Improving Llm-Based Text-to-Sql Through Workfiow Paradigm,” pre-print arXiv:2402.10671 (2024): 10796-10816, https://doi.org/10.18653/v1/2024.findings-acl.641.

[23] T. Ren, Y. Fan, Z. He, et al., “Purple: Making a Large Language Model a Better Sql Writer,” preprint arXiv:2403.20014 (2024): 15-28, https://doi.org/10.1109/icde60146.2024.00009.

[24] G. Qu, J. Li, B. Li, et al., “Before Generation, Align It! a Novel and Effective Strategy for Mitigating Hallucinations in Text-to-Sql Genera-tion,” preprint arXiv:2405.15307 (2024): 5456-5471, https://doi.org/10.18653/v1/2024.findings-acl.324.

[25] R. Sun, S. O. Arik, H. Nakhost, et al., “Sql-Palm: Improved Large Language Modeladaptation for Text-to-Sql,” preprint arXiv:2306.00739 (2023).

[26] R. Anil, A. M. Dai, O. Firat, et al., “Palm 2 Technical Report,” preprint arXiv:2305.10403 (2023).

[27] E. J. Hu, Y. Shen, P. Wallis, et al., “Lora: Low-Rank Adaptation of Large Language Models,” preprint arXiv:2106. 09685 (2021).

[28] H. Touvron, L. Martin, K. Stone, et al., “Llama 2: Open Foundation and Fine-Tuned Chat Models,” preprint arXiv:2307.09288 (2023).

[29] A. Yang, B. Xiao, B. Wang, et al., “Baichuan 2: Open Large-Scale Language Models,” preprint arXiv:2309.10305 (2023).

[30] M. Pourreza and D. Rafiei, “Dts-Sql: Decomposed Text-to-Sql With Small Large Language Models,” preprint arXiv:2402.01117 (2024): 8212-8220, https://doi.org/10.18653/v1/2024.findings-emnlp.481.

[31] R. Sun, S. Ö. Arik, R. Sinha, et al., “Sqlprompt: In-Context Text-to- Sql With Minimal Labeled Data,” preprint arXiv:2311.02883 (2023): 542-550, https://doi.org/10.18653/v1/2023.findings-emnlp.39.

[32] S. Volvovsky, M. Marcassa, and M. Panbiharwala, “Dfin-Sql: Inte-grating Focused Schema With din-sql for Superior Accuracy in Large- Scale Databases,” preprint arXiv:2403.00872 (2024).

[33] Z. Li, X. Wang, J. Zhao, et al., “Pet-Sql: A Prompt-Enhanced Two- Stage Text-to-Sql Framework With Cross-Consistency,” preprint arXiv:2403.09732 (2024).

[34] D. Lee, C. Park, J. Kim, and H. Park, “Mcs-Sql: Leveraging Multiple Prompts and Multiple-Choice Selection for Text-to-Sql Generation,” preprint arXiv:2405.07467 (2024).

[35] J. Chen, “Sqlcritic: Correcting Text-to-Sql Generation via Clause- Wise Critic,” preprint arXiv:2503.07996 (2025).

[36] C. Guo, Z. Tian, J. Tang, et al. “Retrieval-Augmented Gpt-3.5-Based Text-to-Sql Framework With Sample-Aware Prompting and Dynamic Revision Chain,” , in International Conference on Neural Information Processing (Springer, 2023), 341-356.

[37] H. Xia, F. Jiang, N. Deng, et al., “Sql-Craft: Text-To-Sql Through Interactive Refinement and Enhanced Reasoning,” preprint arXiv:2402.14851 (2024).

[38] Z. Hong, Z. Yuan, H. Chen, Q. Zhang, F. Huang, and X. Huang, “Knowledge-To-Sql: Enhancing Sql Generation With Data Expert Llm,” preprint arXiv:2402.11517 (2024): 10997-11008, https://doi.org/10.18653/v1/2024.findings-acl.653.

[39] D. G. Thorpe,A. J. Duberstein, and I. A. Kinsey, “Dubo-Sql: Diverse Retrieval-Augmented Generation and Fine Tuning for Text-to-Sql,” preprint arXiv:2404.12560 (2024) [Online], https://api.semanticscholar.org/CorpusID:269282528.

[40] R. Liu, S. Yuan, A. Dai, et al., “Few-Shot Table Understanding: A Benchmark Dataset and Pre-Training Baseline,” in Proceedings of the 29th International Conference on Computational Linguistics, (2022), 3741-3752.

[41] A. Askari, C. Poelitz, and X. Tang, “Magic: Generating Self- Correction Guideline for in-Context Text-to-Sql,” preprint arXiv: 2406.12692 (2024).

[42] J. Cen, J. Liu, Z. Li, and J. Wang, “Sqlfixagent: Towards Semantic- Accurate Sql Generation Via Multi-Agent Collaboration,” preprint arXiv:2406.13408 (2024).

[43] K. Serebryany, “Continuous Fuzzing With Libfuzzer and Address-sanitizer,” in 2016 IEEE Cybersecurity Development (Secdev) IEEE, 2016), 157.

[44] J. Liu, C. S. Xia, Y. Wang, and L. Zhang, “Is Your Code Generated by Chatgpt Really Correct? Rigorous Evaluation of Large Language Models for Code Generation,” Advances in Neural Information Processing Sys-tems 36 (2024), https://doi.org/10.48550/arXiv.2305.01210.

[45] X. Chen, M. Lin, N. Schärli, and D. Zhou, “Teaching Large Lan-guage Models to Self-Debug,” preprint arXiv:2304.05128 (2023).

[46] Y. Gan, X. Chen, and M. Purver, “Exploring Underexplored Limi-tations of Cross-Domain Text-to-Sql Generalization,” in Proceedings of the 2021 Conference on Empirical Methods in Natural Language Pro-cessing, (2021), 8926-8931.

[47] Y. Gan, X. Chen, Q. Huang, et al., “Towards Robustness of Text-to- Sql Models Against Synonym Substitution,” in Proceedings of the 59th Annual Meeting of the Association for Computational Linguistics and the 11th International Joint Conference on Natural Language Processing, Vol. 1 (Long Papers, 2021), 2505-2515.

[48] X. Deng, A. Hassan, C. Meek, O. Polozov, H. Sun and M. Richardson, “Structure-Grounded Pretraining for Text-to-Sql ” in Pro-ceedings of the 2021 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technolo-gies, (2021), 1337-1350.

[49] S. Chang, J. Wang, M. Dong, et al., “Dr. Spider: A Diagnostic Evaluation Benchmark Towards Text-to-Sql Robustness,” in The Elev-enth International Conference on Learning Representations, (2022).

[50] OpenAI. Chatgpt. 2023, https://chat.openai.com/chat.