Source-linked AI summary
CrackSQL: A Hybrid SQL Dialect Translation System Powered by Large Language Models
Wei Zhou, Yuyang Gao, Xuanhe Zhou, Guoliang Li
TL;DR
SQL dialect translation must preserve query functionality across database systems, but existing manual, rule-based, and LLM approaches have limitations. CrackSQL combines rules and LLMs with query decomposition, syntax matching, and adaptive translation; on the reported benchmark it is described as producing fewer errors than other methods.
Problem
Manual and rule-based translation require substantial maintenance, while LLM translation can produce unreliable results on complex queries, making dialect translation challenging.
Source: §1 · p. 1Method
CrackSQL combines rule-based and LLM translation with functional query decomposition, cross-dialect syntax embeddings, and adaptive local-to-global translation.
Source: §1 · p. 2, §3 · pp. 2–3Results
On a modified real-world BIRD benchmark covering Oracle→MySQL and Oracle→PostgreSQL, CrackSQL is reported to outperform other methods with fewer errors and to record zero errors in four of five categories.
Source: §4.2 · p. 4Takeaways & Limitations
The system targets reduced manual intervention and more accurate, consistent translation by combining dialect specifications with focused, incremental translation.
Source: §1 · p. 2
Abstract
from arXiv · showhide
Dialect translation plays a key role in enabling seamless interaction across heterogeneous database systems. However, translating SQL queries between different dialects (e.g., from PostgreSQL to MySQL) remains a challenging task due to syntactic discrepancies and subtle semantic variations. Existing approaches including manual rewriting, rule-based systems, and large language model (LLM)-based techniques often involve high maintenance effort (e.g., crafting custom translation rules) or produce unreliable results (e.g., LLM generates non-existent functions), especially when handling complex queries. In this demonstration, we present CrackSQL, the first hybrid SQL dialect translation system that combines rule and LLM-based methods to overcome these limitations. CrackSQL leverages the adaptability of LLMs to minimize manual intervention, while enhancing translation accuracy by segmenting lengthy complex SQL via functionality-based query processing. To further improve robustness, it incorporates a novel cross-dialect syntax embedding model for precise syntax alignment, as well as an adaptive local-to-global translation strategy that effectively resolves interdependent query operations. CrackSQL supports three translation modes and offers multiple deployment and access options including a web console interface, a PyPI package, and a command-line prompt, facilitating adoption across a variety of real-world use cases
1 INTRODUCTION
SQL dialect translation must preserve functional equivalence across database systems, but existing manual, rule-based, and LLM approaches have practical limitations. CrackSQL combines rules and LLMs with query decomposition, cross-dialect syntax matching, and adaptive translation.
1 INTRODUCTION
- Dialect translation converts a query for one database system into a functionally equivalent query for another, supporting migration and cross-database applications.The passage names PostgreSQL-to-MySQL translation, NL2SQL, ETL, and cross-database analytics as examples.Source: §1 · p. 1
- Manual, rule-based, and LLM-based translation each have limitations, including maintenance effort for handcrafted rules and unreliable outputs on complex queries.LLMs may introduce non-equivalent operations or nonexistent target-dialect functions; existing tools also require ongoing rule maintenance.Source: §1 · p. 1, Table 1 · p. 1
- CrackSQL combines rule-based and LLM-based methods, decomposes queries into functional operations, and uses cross-dialect syntax embeddings and adaptive local-to-global translation.The embedding model matches syntax using code structure and textual specifications; the adaptive strategy expands translation scope to capture inter-operation dependencies.Source: §1 · p. 2
2 CRACKSQL OVERVIEW
CrackSQL routes translation requests from three access methods through a unified server that configures and orchestrates translation. Its Rule + LLM workflow processes snippets, uses database feedback and hybrid translation, then checks the translated SQL for remaining errors.
2 CRACKSQL OVERVIEW
- Users can submit requests through the web console, PyPI package, or command prompt, which route to a unified Translation Server.The server configures translation services according to user-specified parameters and operating mode.Source: §2 · p. 2
- In Rule + LLM mode, the server decomposes SQL into snippets, locates snippets with execution errors, and iteratively translates them using rules or an LLM.The Database Engine identifies snippets with errors, while the Computation Engine performs hybrid translation and syntax matching.Source: §2 · p. 2
- The system enriches LLM context with matched cross-dialect syntax specifications and returns translated snippets to the Database Engine for error checking.Successful snippets are forwarded to the User Interface when no execution errors remain.Source: §2 · p. 2
3 METHODOLOGY
CrackSQL decomposes and simplifies SQL operations, matches syntax across dialects with a retrieval-enhanced embedding model, and translates incrementally from local operations to broader query contexts. Parser checks and iterative scope expansion address incompatible or insufficiently specified operations.
3 METHODOLOGY
- Tree-based segmentation decomposes a query into functional operations, which rule-based normalization and abstraction simplify before translation.Abstraction can replace translation-irrelevant column or table names with generic non-terminal symbols.Source: §3 · p. 2
- The cross-dialect embedding model combines syntax-tree structure with textual specifications, and retrieval-enhanced contrastive learning trains it on equivalent pairs and hard negatives.Hard negatives are functionally different syntax elements with semantic similarity, helping distinguish true equivalence from superficial similarity.Source: §3 · pp. 2–3
- Local-to-global translation identifies incompatible or insufficient operations; incompatible operations use syntax matching and hybrid rule/LLM translation.Incompatibilities are detected by the BNF parser, while insufficiencies arise when local syntax information is incomplete, including after the LLM exceeds its trial limit.Source: §3 · p. 3
- For insufficient operations, the system expands scope by adding adjacent syntax elements until translated operations replace the original snippets in the final query.The stated goal is a target query that is syntactically valid and functionally equivalent to the source.Source: §3 · p. 3
- LLM translation is limited to segmented operations covered by prepared specifications, with parser checking and LLM reflection used to mitigate hallucinations.The specifications are prepared from official documents.Source: §3 · p. 3
4 DEMONSTRATION
CrackSQL is demonstrated through a configurable web interface and evaluation on Oracle-to-MySQL and Oracle-to-PostgreSQL translations. The reported results describe fewer errors than other methods, alongside distinct error patterns for rule-based tools and direct LLM prompting.
4 DEMONSTRATION
- CrackSQL is presented with a web-console demonstration and a Python package for local or programmatic access.The paper says the package is available through PyPI.Source: §4 · p. 3
4.1 Web Console Interface
- The web console lets users configure target databases, models, and an external knowledge base, then inspect settings, snippet-level process records, and final results.It also maintains a history of translation requests.Source: §4.1 · pp. 3–4
4.2 Experiment Result
- CrackSQL records zero errors in four of five categories on the modified real-world BIRD benchmark and is reported to outperform other methods with fewer translation errors.The four categories are syntax-rule violations, incorrect function or operator mappings, keyword misuse, and mismatched data types; the comparison covers Oracle→MySQL and Oracle→PostgreSQL.Source: Figure 3 · p. 4, §4.2 · p. 4
- Rule-based translators show inconsistent error distributions across Oracle→MySQL and Oracle→PostgreSQL, reflecting gaps in handcrafted mappings.The passage gives examples involving date/time calculations and keyword misuse or omission.Source: §4.2 · p. 4
- Direct GPT-4o translation can introduce incorrect renaming, data-type changes, or spurious clauses, while CrackSQL constrains and validates translated snippets.The passage notes that direct prompting can succeed on straightforward cases but fail on edge cases requiring close adherence to standards or specialized operators.Source: §4.2 · p. 4