Source-linked AI summary
Towards Complex Text-to-SQL in Cross-Domain Database with Intermediate Representation
Jiaqi Guo, Zecheng Zhan, Yan Gao, Yan Xiao, Jian-Guang Lou, Ting Liu, Dongmei Zhang
TL;DR
Complex, cross-domain Text-to-SQL must handle SQL implementation details omitted from questions and many out-of-domain schema words. IRNet uses schema linking and a grammar-based intermediate representation, SemQL, before deterministic SQL inference; on Spider, it achieves 46.7% exact matching accuracy and a 19.5% absolute improvement over previous state-of-the-art approaches.
Problem
Spider challenges Text-to-SQL with implementation details absent from questions and out-of-domain schema words that complicate column prediction.
Method
IRNet performs schema linking, grammar-based SemQL synthesis, and deterministic SQL inference from SemQL using domain knowledge.
Results
46.7% exact matching accuracy and a 19.5% absolute improvement over previous state-of-the-art approaches are reported on Spider.
Takeaways & Limitations
Learning to synthesize SemQL rather than SQL can substantially benefit other neural Text-to-SQL approaches.
Takeaways & Limitations
23.9% of failed examples are caused by complicated nested queries, while Extra Hard queries comprise about 20% of the training set.
Abstract
from arXiv · showhide
We present a neural approach called IRNet for complex and cross-domain Text-to-SQL. IRNet aims to address two challenges: 1) the mismatch between intents expressed in natural language (NL) and the implementation details in SQL; 2) the challenge in predicting columns caused by the large number of out-of-domain words. Instead of end-to-end synthesizing a SQL query, IRNet decomposes the synthesis process into three phases. In the first phase, IRNet performs a schema linking over a question and a database schema. Then, IRNet adopts a grammar-based neural model to synthesize a SemQL query which is an intermediate representation that we design to bridge NL and SQL. Finally, IRNet deterministically infers a SQL query from the synthesized SemQL query with domain knowledge. On the challenging Text-to-SQL benchmark Spider, IRNet achieves 46.7% accuracy, obtaining 19.5% absolute improvement over previous state-of-the-art approaches. At the time of writing, IRNet achieves the first position on the Spider leaderboard.
1 Introduction
Spider exposes two challenges for complex, cross-domain Text-to-SQL: SQL implementation details may be absent from questions, while many schema words are out of domain. IRNet addresses these challenges through schema linking and SemQL, an intermediate representation between NL and SQL.
- Spider includes nested queries and clauses such as GROUPBY and HAVING, making its SQL more complicated than WikiSQL.
- The SQL may group by a column such as ‘student id’ that the question never mentions, creating a mismatch between user intent and implementation details.
- 35% of words in Spider development-set database schemas are absent from training schemas, compared with 22% in WikiSQL.
- IRNet decomposes synthesis into schema linking, grammar-based SemQL generation, and deterministic SQL inference using domain knowledge.Schema linking recognizes mentioned columns and tables and assigns column types based on how they appear in the question.
- On Spider, IRNet achieves 46.7% exact matching accuracy and a 19.5% absolute improvement over previous state-of-the-art approaches.With BERT, IRNet reaches up to 54.7% accuracy.
- Learning to synthesize SemQL rather than SQL can substantially benefit SQLNet, TypeSQL, and SyntaxSQLNet.The results support designing intermediate representations to bridge NL and SQL for complex, cross-domain Text-to-SQL.
2 Approach
IRNet addresses Text-to-SQL mismatch and lexical challenges by linking question spans to schemas, generating tree-structured SemQL, and deterministically inferring SQL. Its neural model combines NL and schema encoders with a grammar-based decoder that selects columns and tables using schema-linking information and memory.
- Intermediate Representation: IRNet decomposes synthesis into schema linking, SemQL generation, and deterministic SQL inference rather than directly generating SQL.SemQL serves as an intermediate representation between natural language and SQL.
- Intermediate Representation: SemQL hides SQL implementation details by eliminating clauses such as GROUPBY, HAVING, and FROM while uniformly representing filtering conditions.The omitted SQL details are deterministically inferred from SemQL.
- Intermediate Representation: SemQL requires each column to be declared with its table, helping distinguish duplicated column names and associate the special column ‘*’ with a table.The table declaration also supports inference of database relations during SQL reconstruction.
- Schema Linking: Schema linking identifies table, column, and value entities through descending-length n-gram matching, producing a non-overlapping span sequence with entity types.The method assumes database cell values are unavailable.
- Schema Linking: Linked columns receive EXACT MATCH or PARTIAL MATCH types, while value-linked columns receive VALUE EXACT MATCH or VALUE PARTIAL MATCH using ConceptNet relations.The method searches ConceptNet categories including ‘is a type of’ and ‘related terms’ to connect values with schema columns.
- Model: IRNet addresses column-selection difficulty with schema-linking features and a memory-augmented pointer network that decides whether to select from memory or the schema.The memory mechanism is motivated by observations that vanilla pointer networks tend to select the same columns.
- Model: The model encodes typed question spans with a bidirectional LSTM and encodes typed schema columns and tables into representations.The NL encoder uses word and span-type embeddings, while the schema encoder constructs column and table representations.
- Model: A grammar-based LSTM decoder generates SemQL through APPLYRULE, SELECTCOLUMN, and SELECTTABLE actions.The decoder operates over SemQL’s tree structure and selects columns and tables from the schema.
3 Experiment
IRNet is evaluated on Spider through benchmark comparisons, component and hardness analyses, SemQL experiments, ablations, and error analysis. Results show gains from IRNet, SemQL, schema linking, memory augmentation, coarse-to-fine decoding, and BERT, while identifying remaining failure sources.
- Experiment Setup: Spider evaluation uses database splits with 8,625 training, 1,034 development, and 2,147 test question-SQL pairs, measuring SQL exact matching and component matching.The database split contains 206 databases: 146 training, 20 development, and 40 testing.
- Experimental Results: IRNet outperforms all baselines, achieving 27.0% absolute improvement over SyntaxSQLNet and 19.5% over SyntaxSQLNet(augment) on test-set exact matching.BERT substantially improves both SyntaxSQLNet and IRNet and widens their accuracy gap on development and test sets.
- Experimental Results: 18.2% absolute improvement occurs on every SQL component except KEYWORDS, while BERT further boosts IRNet, especially on WHERE.Component matching compares SyntaxSQLNet and IRNet with and without BERT on the test set.
- Experimental Results: 23.3% absolute improvement over SyntaxSQLNet appears at the Hard level, with IRNet outperforming it across all four hardness levels with or without BERT.The hardness-level analysis uses test-set exact matching accuracy.
- Ablation Study: 6.6% to 14.4% absolute exact-matching improvements occur when baselines generate SemQL instead of SQL on the development set.SyntaxSQLNet gains 8.6% and surpasses SyntaxSQLNet(augment); TypeSQL and SQLNet improve less because their slot-filling models support only part of SemQL.
- Ablation Study: Schema linking improves IRNet by 8.5% and IRNet(BERT) by 6.4%, WHERE F1 by 12.5%, and memory augmentation reduces repeated-column errors by 70%.The coarse-to-fine framework provides an additional performance boost; remaining failures include column prediction, nested queries, operators, and incomplete schema relations.
4 Discussion
IRNet’s discussion identifies performance gaps and failure modes tied to difficult SQL distributions, operator prediction, and schema assumptions.
- Operator: 12.4% of failed examples involve incorrect operators requiring common knowledge, including aggregation, WHERE operators, and sorting orders.For example, “from old to young” implies descending rather than ascending order.
- Other Errors: Some incorrect FROM clauses arise when ground-truth queries join tables without declared foreign-key relations.This violates IRNet’s assumption that database schemas are precise and complete.
- Performance Gap: IRNet exhibits a development–test performance gap that may reflect differing distributions of Hard and Extra Hard SQL queries.The authors construct pseudo test sets to investigate this hypothesis.
5 Related Work
Related work spans database-specific and reusable natural-language interfaces, intermediate representations, and entity-linking approaches. IRNet differs by synthesizing SemQL before inferring SQL.
- Natural Language Interface to Database: Early NLIDB systems were often hand-crafted for specific databases, while later work sought reusable systems across multiple databases.Recent neural methods and cross-domain benchmarks renewed interest in Text-to-SQL.
- Natural Language Interface to Database: Unlike end-to-end neural approaches, IRNet first synthesizes a SemQL query and then infers a SQL query from it.This separates intermediate semantic representation from final SQL generation.
- Intermediate Representations in NLIDB: Earlier intermediate representations used predicates designed for specific databases, whereas SemQL targets wider adoption without human effort in a new domain.This distinction separates SemQL from database-specific representations such as those in LUNAR and MASQUE.
- Entity Linking: Schema linking in IRNet is related to entity-linking methods developed for knowledge-base question answering and semantic parsing.In Text-to-SQL, the linked entities include columns, tables, and cell values.
6 Conclusion
The paper presents SemQL and schema linking as complementary mechanisms for addressing lexical and mismatch problems in complex, cross-domain Text-to-SQL. Experiments on Spider demonstrate IRNet’s effectiveness.
- Conclusion: IRNet combines an intermediate representation with schema linking to address lexical and mismatch problems in complex, cross-domain Text-to-SQL.The approach is presented as a neural method called SemQL in the conclusion passage.
- Conclusion: Experimental results on the challenging Spider benchmark demonstrate the effectiveness of IRNet.The conclusion identifies Spider as the evaluation benchmark for the approach.
7 Supplemental Material
The supplemental material describes SemQL’s deterministic conversion to SQL, its coarse-to-fine synthesis framework, training objective, encoder, and supporting experiments and examples.
- SemQL-to-SQL Inference: SemQL queries are converted to SQL by traversing their trees in preorder and mapping production rules to SQL components.The mapping covers structures such as set operations, clauses, selected columns, aggregates, ordering, and tables.
- SemQL-to-SQL Inference: SemQL represents SQL structures through nodes such as Z, R, Select, A, Superlative, and Order.Production rules determine set operations, clauses, selected columns, aggregates, tables, and ordering behavior.
- SQL-to-SemQL Transformation: SQL queries are transformed into SemQL by initializing a Z node and recursively attaching nodes according to set operations, selections, ordering, and filters.Column and table nodes encode schema assignments, including handling for special columns and unassigned FROM tables.
- Coarse-to-Fine Framework: The SemQL skeleton removes all nodes under each A node, leaving details to be filled later.This skeleton is the intermediate structure used by the coarse-to-fine framework.
- Coarse-to-Fine Framework: The coarse-to-fine framework first decodes a SemQL skeleton, then fills missing columns and tables with a detail decoder.The generation probability is factorized into skeleton generation and detail generation.
- Training: The model is trained by maximizing the log-likelihood of ground-truth action sequences, with γ set to 1 in the experiment.The objective balances skeleton and detail generation probabilities.
- Experiments and Examples: BERT is used as an encoder for questions and column names, and supplemental tables report hardness distributions and exact-matching accuracy.Additional experiments compare development, pseudo-test, and baseline results, including SyntaxSQLNet variants.