RYANSQL: Recursively Applying Sketch-based Slot Fillings for Complex Text-to-SQL in Cross-Domain Databases

Relational databases are widely used to maintain and query structured data sets in many fields such as healthcare (Hillestad et al. 2005), financial markets (Beck, Demirguc-Kunt, and Levine 2000), or customer relation management (Ngai, Xiu, and Chau 2009). Most relational databases support Structured Query Language (SQL) to access the stored data. Although SQL is expressive and powerful, it is quite difficult to master, especially for non-technical users.

Text-to-SQL is the task of generating an SQL query when a user question and a target database are given. The examples are shown in Figure 1. Recently proposed neural network architectures achieved more than 80% exact matching accuracy on the well-known Text-to-SQL benchmarks such as ATIS (Air Travel Information Service), GeoQuery, and WikiSQL (Xu, Liu, and Song 2017; Yu et al. 2018a; Shi et al. 2018; Dong and Lapata 2018; Hwang et al. 2019; He et al. 2018). However, those benchmarks have shortcomings that restrict their applications. The ATIS (Price 1990) and GeoQuery (Zelle and Mooney 1996) benchmarks assume the same database across the training and test data set, thus the trained systems cannot process a newly encountered database at inference time. The WikiSQL (Zhong, Xiong, and Socher 2017) benchmark assumes cross-domain databases. Cross-domain means that the databases for training and test data sets are different; the system should predict with an unseen database as its input during testing. Meanwhile, the complexity of SQL queries and databases in the WikiSQL benchmark is somewhat limited. WikiSQL assumes that an input database always has only one table. It also assumes that the resultant SQL is non-nested, and contains SELECT and WHERE clauses only. Figure 1(a) shows an example from the WikiSQL data set.

Different from those benchmarks, the Spider benchmark proposed by Yu et al. (2018c) contains complex SQL queries with cross-domain databases. The SQL queries in Spider benchmark could contain nested queries with multiple table JOINs, and clauses like ORDERBY, GROUPBY, and HAVING. Figure 1(b) shows an example from the Spider benchmark; Yu et al. (2018c) showed that the state-of-the-art systems for the previous benchmarks do not perform well on the Spider data set.

In this article, we propose a novel network architecture called RYANSQL (Recursively Yielding Annotation Network for SQL) to handle such complex, cross-domain Text-to-SQL problems. The proposed approach generates nested queries by recursively yielding their component SELECT statements. A sketch-based slot-filling approach is proposed to predict each SELECT statement. In addition, two simple but effective input manipulation methods are proposed to improve the overall system performance. Among the systems not using database content, the proposed RYANSQL and its variant RYANSQL v2, with the aid of BERT (Devlin et al. 2019), improve the previous state-of-the-art system SLSQL (Lei et al. 2020) by 2.5% and 4.9%, respectively, in terms of the test set exact matching accuracy. RYANSQL v2 is ranked at 3rd place among all systems including those using database content. Our contributions are summarized as follows:

• We propose a detailed sketch for the complex SELECT statements, along with a network architecture to fill the slots.

• Statement Position Code (SPC) is introduced to recursively predict nested queries with sketch-based slot-filling algorithm.

• We suggest two simple input manipulation methods to improve performance further.

The Text-to-SQL task considered in this article is defined as follows: Given a question with n tokens $Q={w1Q,…,wnQ}$ and a DB schema with t tables and f foreign key relations D = {T₁,…,T_t,F₁,…,F_f}, find S, the SQL translation of Q. Each table T_i consists of a table name with t_i words ${w1Ti,…,wtiTi}$⁠, and a set of columns {C_j,…,C_k}. Each column C_j consists of a column name ${w1Cj,…,wcjCj}$⁠, and a marker to check if the column is a primary key.

For an SQL query S we define a non-nested form of S, N(S) = {(P₁,S₁),…,(P_l,S_l)}. In the definition, P_i is the i-th SPC, and S_i is its corresponding SELECT statement. Table 1 shows examples of a natural language query Q, corresponding SQL translation S, and non-nested form N(S).

Each SPC P could be considered as a sequence of p position code elements, $P=[c1P,…,cpP]$⁠. The possible set of position code elements is {NONE, UNION, IN- TERSECT, EXCEPT, WHERE, HAVING, PARALLEL }. NONE represents the outermost statement, and PARALLEL means the parallel elements inside a single clause, for example, the second element of the WHERE clause. Other position code elements represent corresponding SQL clauses.

Because it is straightforward to construct S from N(S), the goal of the proposed system is to construct N(S) for the given Q and D. To achieve the goal, the proposed system first sets the initial SPC P₁ = [NONE], and predicts its corresponding SELECT statement and nested SPCs. The system recursively finds out the corresponding SELECT statements for the remaining SPCs, until every SPC has its own corresponding SELECT statement.

In this section, the method to create the SELECT statement for the given question Q, database D, and SPC P is described. Section 3.1 describes the input encoder; the sketch-based slot-filling decoder is described in Section 3.2.

Figure 2 shows the overall network architecture of the input encoder. The input encoder consists of five layers: Embedding layer, Embedding Encoder layer, Question-Column Alignment layer, Table Encoder layer, and Question-Table Alignment layer.

To get the embedding vector for a word w in question, table names, or column names, its word embedding and character embedding are concatenated. The word embedding is initialized with d₁ = 300 dimensional pretrained GloVe (Pennington, Socher, and Manning 2014) word vectors, and is fixed during training. For character embedding, each character is represented as a trainable vector of dimension d₂ = 50, and we take maximum value of each dimension of component characters to get the fixed-length vector. The two vectors are then concatenated to obtain the embedding vector $ew∈ℝd1+d2$⁠. One layer highway network (Srivastava, Greff, and Schmidhuber 2015) is applied on top of this representation. For SPC P, each code element c is represented as a trainable vector of dimension d_p = 100.

A one-dimensional convolution layer with kernel size 3 is applied on top of SPC element embedding vectors ${e1P,…,epP}$⁠. Max-pooling is applied on the output to get the SPC vector $vP∈ℝdp$⁠. v^P is then concatenated to each question and column word embedding vector.

CNN with dense connection proposed in Yoon, Lee, and Lee (2018) is applied to encode each word of question and columns to capture local information. Parameters are shared across the question and columns. For each column, a max-pooling layer is followed; outputs are concatenated with their table name representations and projected to dimension d. Layer outputs are encoded question word vectors $VQ={v1Q,v2Q,…,vnQ}∈ℝn×d$⁠, and hidden column vectors $HC={h1C,…,hmC}∈ℝm×d$⁠.

Once the column vectors are updated with the question context, a transformer layer (Vaswani et al. 2017) is applied on top of F^C to capture contextual column information. Layer outputs are the encoded column vectors $VC={v1C,…,vmC}∈ℝm×d$⁠.

Outputs of the layer are the hidden table vectors $HT={h1T,h2T,…,htT}∈ℝt×d$⁠.

In this layer, the same network architecture as the Question-Column Alignment layer is used to model the table vectors with contextual information of the question. Layer outputs are the encoded table vectors $VT={v1T,v2T,…,vtT}∈ℝt×d$⁠.

Final outputs of the input encoder are as follows: (1) Encoded question word vectors $VQ={v1Q,…,vnQ}∈ℝn×d$⁠, (2) Encoded column vectors $VC={v1C,…,vmC}∈ℝm×d$⁠, (3) Encoded table vectors $VT={v1T,…,vtT}∈ℝt×d$⁠, and (4) Encoded SPC $vP∈ℝdp$⁠. Additionally, (5) Encoded question vector v^Q = f_s(V^Q) and (6) Encoded DB schema vector v^D = f_s(V^C) ∈ℝ^d are calculated for later use in the decoder.

Inspired by the work of Hwang et al. (2019) and Guo et al. (2019), BERT (Devlin et al. 2019) is considered as another version of the input encoder. The input to BERT is constructed by concatenating question words, SPC elements, and column words as follows: [CLS], $w1Q$⁠, …, $wnQ$⁠, [SEP], $c1P$⁠, …, $cpP$⁠, [SEP], $w1C1$⁠, …, $wc1C1$⁠, [SEP], …, [SEP], $w1Cm$⁠, …, $wcmCm$⁠, [SEP].

Hidden states of the last layer are retrieved to form V^Q and V^C; for V^C, the state of each column’s last word is taken to represent an encoded column vector. Each table vector $vjT$ is calculated as a self-attended vector of its containing columns; v^Q, v^D, and v^P are calculated as the same.

Table 2 shows the proposed sketch for a SELECT statement. The sketch-based slot-filling decoder predicts values for slots of the proposed sketch, as well as the number of slots.

Eleven values b_g,b_o,b_l,b_w,b_h,n_g,n_o,n_s,n_w, n_h, and c_IUEN are classified by applying two fully connected layers on v^S. Binary values b_g,b_o,b_l,b_w,b_h represent the existence of GROUPBY, ORDERBY, LIMIT, WHERE, and HAVING, respectively. Note that FROM and SELECT clauses must exist to form a valid SELECT statement. n_g,n_o,n_s,n_w,n_h represent the number of conditions in GROUPBY, ORDERBY, SELECT, WHERE, and HAVING clauses, respectively. The maximal numbers of conditions N_g = 3, N_o = 3, N_s = 6, N_w = 4, and N_h = 2 are defined for GROUPBY, ORDERBY, SELECT, WHERE, and HAVING clauses, to solve the problem as n-way classification problem. The values of maximal condition numbers are chosen to cover all the training cases.

Finally, c_IUEN represents the existence of one of INTERSECT, UNION, or EXCEPT, or NONE if no such clause exists. If the value of c_IUEN is one of INTERSECT, UNION, or EXCEPT, the corresponding SPC is created, and the SELECT statement for that SPC is generated recursively.

In the equation, full₂ means the application of two fully connected layers, and table score vector s is from Equation (7).

During the inference, the $TBLs are classified first, and $COLs for other clauses are chosen among the columns of the classified $TBLs.

While W₁,W₂ ∈ℝ^d×d, $W3∈ℝNs×d$ are trainable parameters, and $VselQ∈ℝNs×d$ is the matrix of attended question vectors for N_s conditions. v^P is tiled to match the row of V^Q.

Equation (10) is reused to calculate $Psel_col2$⁠, with $VselQ[i]$ replaced by $Usel_col1Q[i]$⁠; then $Usel_col2Q$ is retrieved in the same way as Equation (11), and the probabilities of $DIST₂ and $AGG₂ are calculated in the same way as $DIST₁ and $AGG₁. Finally, the $DIST slot, DISTINCT marker for overall SELECT clause, is calculated by applying a fully connected layer on v^S.

Once all the slots are filled for N_s conditions, the decoder retrieves the first n_s conditions to predict the SELECT clause. This is possible because the CNN with Dense Connection used for question encoding (Yoon, Lee, and Lee 2018) captures relative position information. Due to the SQL consistency protocol of the Spider benchmark (Yu et al. 2018c), we expect that the conditions are ordered in the same way as they are presented in Q. For the data sets without such consistency protocol, the proposed slot-filling method could easily be changed to an LSTM-based model, as shown in Xu, Liu, and Song (2017).

The same network structure as a SELECT clause is applied. The only difference is the prediction of $ORD slot; this could be done by applying a fully connected layer on $Uob_col1Q$⁠, which is the correspondence of $Usel_col1Q$⁠.

The same network structure as a SELECT clause is applied. For the GROUPBY case, retrieving only the values of $Pgb_col1$ is enough to fill the necessary slots.

W₁, W₂ ∈ℝ^d×d, W₃ ∈ℝ^1×d are trainable parameters.

The same network structure as a SELECT clause is applied to get the attended question vectors $VwhQ∈ℝNw×d$⁠, and probabilities for $COL₁, $COL₂, $DIST₁, $DIST₂, $AGG₁, $AGG₂, and $ARI. A fully connected layer is applied on $Uwh_col1Q$ to get the probabilities for $CONJ, $NOT, and $COND.

A fully connected layer is applied on $Uwh_col1Q$ and $Uwh_col2Q$ to determine if the condition value for each column is another nested SELECT statement or not. If the value is determined as a nested SELECT statement, the corresponding SPC is generated, and the SELECT statement for the SPC is predicted recursively. If not, the pointer network is used to get the start and end position of the value span from question tokens.

The same network structure as a WHERE clause is applied.

In this section, we introduce two input manipulation methods to improve the performance of our proposed system further.

In a FROM clause, some tables (and their columns) are not mentioned explicitly in the given question, but they are still required to make a “link” between other tables to form a proper SQL query. One such example is given in Table 3. The table writes is not explicitly mentioned in Q, but it is used in the JOIN clause to link between tables author and paper. Those “link” tables are necessary to create the proper SELECT statement, but they work as noise in aligning question tokens and tables because the link tables do not have the corresponding tokens in Q.

To reduce the training noises, only the non-link tables are considered as the $TBL slot values of FROM clause during training. A table of FROM clause is considered a link table if (1) all the $AGG values of the SELECT clause are none, and (2) none of its columns appears in other clauses’ slots. During the inference, the link tables could easily be recovered by using the foreign key relations of the extracted tables. More precisely, the system uses a heuristic of finding the shortest joinable foreign key relation “path” between the extracted tables. Once a path is found, tables in the path are added as the $TBLs of the FROM clause.

The goal of this method is to distinguish the link tables from non-link tables during the training phase. SyntaxSQLNet (Yu et al. 2018b) first predicts all the columns, and then chooses FROM tables based on the classified columns. As noted in Yu et al. (2018b), the approach cannot handle count queries with additional JOINs, for example, “SELECTT2.name, count(*)FROMsinger_in_concertAST1JOINsingerAST2ONT1.singer_id = T2.singer_idGROUP BYT2.singer_id.” Its corresponding user question is “List singer names and number of concerts for each singer.” GNN (Bogin, Gardner, and Berant 2019) handles the problem by turning the database schema into a graph; foreign key links between nodes help the system to distinguish between two types of tables. IRNet (Guo et al. 2019) and RAT-SQL (Wang et al. 2020) have the separated schema linking processing module to explicitly link columns with question tokens.

We supplement the column names with their table names to distinguish between columns with the same name but belonging to different tables and representing different meanings. Table names are concatenated in front of their belonging column names to form supplemented column names (SCNs), but if the stemmed form of a table name is wholly included in the stemmed form of a column name, the table name is not concatenated. Table 4 shows SCN examples; the three columns with the same name id are distinguished with their SCNs. We can also expect the SCNs to align better with question tokens, since a SCN contains more information about what the column actually refers to.

The method aims to integrate tables with their columns. To achieve the goal, IRNet (Guo et al. 2019) and RAT-SQL (Wang et al. 2020) separately encode tables and columns, and integrate the two embeddings on the network; GNN (Bogin, Gardner, and Berant 2019) represents database schema as a graph, generating links between tables and their columns, and directly processes the graph using graph neural network; EditSQL (Zhang et al. 2019) concatenates the table names with its column names, using a special character.

Most recent works on the Text-to-SQL task used the encoder-decoder model. Those works could be classified into three main categories, based on their decoder outputs. Sequence-to-Sequence translation approaches generate SQL query tokens. Dong and Lapata (2016) introduced the hierarchical tree decoder to prevent the model from generating grammatically incorrect semantic representations of the input sentences. Zhong, Xiong, and Socher (2017) used policy-based reinforcement learning to deal with the unordered nature of WHERE conditions.

Grammar-based approaches generate a sequence of grammar rules and apply the generated rules sequentially to obtain the resultant SQL query. IRNet (Guo et al. 2019) defined a structural representation of an SQL query and a set of parse actions to handle the WikiSQL data set. IRNet defined the SemQL query, which is an abstraction of a SQL query in tree form. They also proposed a set of grammar rules to synthesize SemQL queries; synthesizing a SQL query from a SemQL tree structure is straightforward. RAT-SQL (Wang et al. 2020) improved the work of Guo et al. (2019) by proposing a relation-aware transformer to effectively encode relations between columns, tables, and question tokens. GNN (Bogin, Gardner, and Berant 2019) focused on the DB constraints selection problem during the grammar decoding process; they applied global reasoning between question words and database columns/tables. SLSQL (Lei et al. 2020) manually annotated link information between user questions and database columns to show the role of schema linking.

Sketch-based slot-filling approaches use a sketch, which aligns with the syntactic structure of a SQL query. A sketch should be defined generic enough to handle all SQL queries of interest. Once a sketch is defined, one can simply fill the slots of the sketch to obtain the resultant SQL query. SQLNet (Xu, Liu, and Song 2017) first introduced a sketch to handle the WikiSQL data set, along with attention-based slot-filling algorithms. The proposed sketch for WikiSQL is shown in Table 5. TypeSQL (Yu et al. 2018a) added category information such as named entity to better encode the input question. SQLova (Hwang et al. 2019) introduced BERT (Devlin et al. 2019) to encode the input question and database, and the encoded vectors were used to fill the slots of the sketch. X-SQL (He et al. 2018) aligned the contextual information with column tokens to better summarize each column. The sketch-based approaches for WikiSQL described here all used the sketch shown in Table 5, which is enough for the WikiSQL queries but oversimplified for general SQL queries, for example, those contained in the Spider benchmark.

The sketch-based approach on the more complex Spider benchmark showed relatively low performance compared to the grammar-based approaches so far. There are two major reasons: (1) It is hard to define a sketch for Spider queries since the allowed syntax of the Spider SQL queries is far more complicated than that of the WikiSQL queries. (2) Because the sketch-based approaches fill values for the predefined slots, the approaches have difficulties in predicting the nested queries. RCSQL (Lee 2019) tried to apply the sketch-based approach on the Spider data set; Table 6 shows the sketch proposed by Lee (2019). To predict a nested SELECT statement, RCSQL takes a temporal generated SQL query with a special token [SUB_QUERY ] in the corresponding location as its input. For example, for Case 1 of Table 1, RCSQL gets a temporal generated query string ”SELECT name FROM scientists EXCEPT [SUB_QUERY ]” as its input to generate the nested statement S₂, along with the user question and database schema.

Our proposed approach has three improvements compared to RCSQL. First, our sketch in Table 2 is more “complete” in terms of expressiveness. For example, because the RCSQL sketch lacks $ARI elements, the RCSQL cannot generate queries with arithmetic operations between columns, for example, “SELECTT1.nameFROMaccountsAST1JOINcheckingAST2ONT1.custid = T2.custidJOINsavingsAST3ONT1.custid = T3.custidORDER BYT2.balance + T3.balanceLIMIT 1.” Second, while our proposed approach directly predicts for the tables in FROM clause, the RCSQL heuristically predicts the tables using the extracted columns for other clauses. The RCSQL approach cannot generate count queries with additional table JOINs, for example, “SELECT count(*) FROMinstitutionAST1JOINproteinAST2ONT1.institution_id = T2.institution_idWHERET1.founded > 1880 ORT1.type = ‘Private’.” Third, RCSQL fails to generate the nested SELECT statements when two or more statements are on the same depth, for example, S₂ and S₃ in Case 2 of Table 1. Because RCSQL generates one SELECT statement for an input, it expects only one special token for a query.

In this article, we propose a more completed sketch compared to the WikiSQL (Table 5) and RCSQL (Table 6) sketches for complex SELECT statements, along with the Statement Position Code (SPC) to handle the nested queries more efficiently. Although our proposed sketch is tuned using the Spider data set, the sketch is based on the generic SQL syntax and could be applied to other SQL generation tasks.

The proposed RYANSQL is implemented with Tensorflow (Abadi et al. 2015). Layernorm (Ba, Kiros, and Hinton 2016) and dropout (Srivastava et al. 2014) are applied between layers, with a dropout rate of 0.1. Exponential decay with decay rate 0.8 is applied to the learning rate for every three epochs. On each epoch, the trained classifier is evaluated against the validation data set, and the training stops when the exact match score for the validation data set is not improved for 20 consequent training epochs. Minibatch size is set to 16; learning rate is set to 4e⁻⁴. Loss is defined as the sum of all classification losses from the slot-filling decoder. The trained network has 22M parameters.

For pretrained language model–based input encoding, we downloaded the publicly available pretrained model of BERT, BERT-Large, Uncased (Whole Word Masking), and fine-tuned the model during training. The learning rate is set to 1e⁻⁵, and minibatch size is set to 4. The model with BERT has 445M parameters.

The Spider data set (Yu et al. 2018c) is mainly used to evaluate our proposed system. We use the same data split as Yu et al. (2018c); 206 databases are split into 146 train, 20 dev, and 40 test. All questions for the same database are in the same split; there are 8,659 questions for train, 1,034 for dev, and 2,147 for test. The test set of Spider is not publicly available, so for testing our models are submitted to the data owner. For evaluation, we used exact matching accuracy, with the same definition as defined in Yu et al. (2018c).

Table 7 shows comparisons of the proposed system with several state-of-the-art systems; evaluation scores for dev and test data sets are retrieved from the Spider leaderboard.¹ The proposed system is compared with grammar-based systems GrammarSQL (Lin et al. 2019), Global-GNN (Bogin, Gardner, and Berant 2019), IRNet (Guo et al. 2019), and RATSQL (Wang et al. 2020). Also, we compared the proposed system with RCSQL (Lee 2019), which so far showed the best performance on the Spider data set using a sketch-based slot-filling approach.

Evaluation results are presented in three different groups, based on the use of pretrained language models and database content. Although the use of database content (i.e., cell values) could greatly improve the performance of a Text-to-SQL system (as shown in Wang et al. 2018; Hwang et al. 2019; He et al. 2018), a Text-to-SQL system could rarely have access to database content in real world applications due to various reasons such as personal privacy, business secrets, or legal issues. Because the use of database content improves the system performance but decreases the system availability, we put models using database content in a separated group.

For RYANSQL v2, we trained two networks called table network and slot network, with the same network architectures as the proposed RYANSQL. The table network is trained to maximize the $TBL classification accuracy on dev set; the slot network is trained to maximize the exact match accuracy on dev set as RYANSQL does, but the $TBL classification results are fetched from the table network (which is fixed during the training of the slot network). During the inference, the model first classifies $TBLs using the table network, and fills other slots using the slot network.

As can² be observed from the table, the proposed system RYANSQL improves the previous sketch-based slot-filling system RCSQL by a large margin of 15% on the dev set. Note that the RCSQL fine-tuned another well-known pretrained language model ELMo (Peters et al. 2018). With the use of BERT, among the systems without database content, the proposed systems RYANSQL + BERT and RYANSQL v2 + BERT outperform the previous state-of-the-art by 2.5% and 4.9%, respectively, on the hidden test data set, in terms of exact matching accuracy. The proposed system still shows competitive results compared to the systems using database content; RATSQL v3 + BERT outperforms the proposed system by better aligning user questions and database schemas using database content.

Table 8 compares the exact matching accuracies of the proposed systems and other state-of-the-art systems for each hardness level. The proposed RYANSQL + BERT outperforms the previous sketch-based approach RCSQL in every hardness level on dev set. Additionally, the proposed RYANSQL + BERT showed relatively poor performance for the test set at the Extra hardness level, compared to RATSQL v3 + BERT. This suggests that much test data at the Extra hardness level require database content to answer, since the two systems showed comparable results for the Extra hardness dev set.

Next, ablation studies are conducted on the proposed methods to clarify the con- tribution of each feature. The results are presented in Table 9. It turns out that the use of SPC greatly improves the performances for Hard and Extra hardness levels. The result shows that the SPC plays an important role in generating the nested SQL queries. The SPC also slightly increases the performance for Easy and Medium hardness levels. This is because the SPC helps the model to distinguish between each nested SELECT statement, thus removing noise on aligning question tokens and columns.

The use of SCN moderately improves the accuracies for all hardness levels. This is expected, since SCN helps a database column to better align with question tokens by supplementing the column name with its table information.

The JOIN table filtering (JTF) increases performance only when the other two features SPC and SCN are used together. Analysis shows that for some cases, the link tables removed by JTF actually have their corresponding question tokens. One example is the SQL query “SELECTT3.amenity_nameFROMdormAST1JOINhas_amenityAST2ONT1.dormid = T2.dormidJOINdorm_amenityAST3ONT2.amenid = T3.amenidWHERET1.dorm_name = ‘Smith Hall’ ” for question “Find the name of amenities Smith Hall dorm have.” Table has_amenity is considered as a link table, but there exist corresponding clues in the question. Removing the table from $TBL list according to the JTF feature would introduce alignment noise during training. But the evaluation result also shows that, by better aligning question and database schema using the other two features SPC and SCN, the model can recover from the alignment noise introduced by JTF, improving the overall system performance.

Proposed models are also evaluated without all three features SPC, SCN, and JTF to separately see the contribution of our newly proposed sketch. Without the three features, RYANSQL shows 35.5% accuracy on dev set, which is a 6.7% improvement compared to another sketch-based slot-filling model RCSQL; RYANSQL + BERT shows 52.6% dev set accuracy.

There exists a huge performance gap of 23.2% on dev set between RYANSQL and RYANSQL + BERT. SQL component matching F1 scores for the two models are shown in Table 10 to figure out the reason. For the item keyword (which measures the existence of SQL predefined keywords such as SELECT or GROUP BY), the performance gap between the two models is 6.2%, which is relatively small compared to the overall performance gap of 23.2%. Meanwhile, the performance gaps on clause components such as WHERE are similar to or larger than the overall performance gap. These evaluation results suggest that the use of a pretrained language model mainly improves the column classification performance, rather than base structures classification accuracy of a SQL query.

Next, a series of experiments is conducted to see if additional performance improvements could be gained by applying different pretrained language models. Table 11 shows the evaluation results with four different pretrained language models, namely, BERT-base, BERT-large, RoBERTa (Liu et al. 2019), and ELECTRA (Clark et al. 2020). Although RoBERTa and ELECTRA are generally known to perform better than BERT, the evaluation results showed no performance improvement.

The two proposed input manipulation methods SCN and JTF are applied on IRNet (Guo et al. 2019) to see their generalities. We downloaded the source code from the author’s homepage,³ and trained to obtain the dev set accuracy. Evaluation results are shown in Table 12. The performance improvements due to the two input manipulation methods were almost ignorable; because IRNet has the separated schema linking preprocessing module, whose purpose is to link columns with question tokens, the role of SCN and JTF are greatly reduced.

We conducted experiments on WikiSQL (Zhong, Xiong, and Socher 2017) and CSpider (Min, Shi, and Zhang 2019) data sets to test the generalization capability of the proposed model to new data sets. Table 13 shows the comparison between the proposed RYANSQL + BERT and other WikiSQL state-of-the-art systems. Only the systems without execution-guided decoding (EGD) (Wang et al. 2018) are compared, since EGD makes use of the database content. As can be observed from the table, the proposed RYANSQL + BERT showed comparable results to other WikiSQL state-of-the-art systems.

Next, we⁴ evaluated the proposed models on the CSpider data set. CSpider (Min, Shi, and Zhang 2019) is a Chinese-translated version of the Spider benchmark. Only the question of the Spider data set is translated; database table names and column names remain in English. Evaluation on the CSpider data set will show whether the proposed model could be applied on the different languages, even when the question language and database schema language are different. To handle the case, we used multilingual BERT, which has the same network architecture with BERT-base but is trained using a multilingual corpus. Table 14 shows the comparisons between the proposed system and other state-of-the-art systems on the leaderboard. Compared to the exact matching accuracy 51.4% of RYANSQL + BERT-base on Spider data set, the multilingual version shows 10% lower accuracy on dev set, but still shows comparable results to other state-of-the-art systems that are designed for CSpider data set. Our proposed system showed 34.7% test accuracy on the test set, and ranked 2nd place on the leaderboard.

We analyzed 345 failed examples of the RYANSQL + BERT on the development set. We were able to categorize 195 of those examples according to failure types.

The most common cause of failure is column selection failure; 68 out of 195 cases (34.9%) suffered from the error. In many of these cases, the correct column name is not mentioned in a question; for example, for the question “What is the airport name for airport ‘AKO’?”, the decoder chooses column AirportName instead of AirportCode as its WHERE clause condition column. As mentioned in Yavuz et al. (2018), cell value examples for each column will be helpful to solve this problem.

The second frequent error is table number classification error; 49 out of 195 cases (25.2%) belong to the category. The decoder occasionally chooses too many tables for the FROM clause, resulting in unnecessary table JOINs. Similarly, 22 out of 195 cases (11.3%) were due to condition number classification error. Those errors could be handled by observing and updating the extracted slot values as a whole; for example, for a user question “List the maximum weight and type for each type of pet.” the system generates SQL query “SELECTPetType, max(weight), weightFROMPetsGROUP BYPetType.” If the system could observe the extracted slot values as a whole, it would figure out that extracting weight and max(weight) together for SELECT clause is unlikely. Our future work will mainly focus on solving this issue.

The remaining 150 errors were hard to be classified into one category, and some of them were due to different representations of the same meaning, for example: “SELECTmax(age)FROMDogs” vs. “SELECTageFROMDogsORDER BYageDESCLIMIT 1.”

Next, we tried to see if the proposed model could handle user questions in which words are different from database column and table names. We define an overlap score $O(Q,S)=size(w(Q)∩w(S))size(w(S))$ between a user question Q and its SQL translation S. In the equation, w(Q) is the set of stemmed words in Q, and w(S) is the set of stemmed words from column names and table names used in S. Intuitively, the score measures how much overlap exists between the column/table names of SQL query S and user question Q.

Overlap scores are calculated for question-SQL query pairs in the Spider dev set. The data set is divided into five categories based on the calculated overlap scores; Table 15 shows exact matching accuracies of the proposed RYANSQL for those categories. As can be seen from the table, the proposed system shows relatively low performance on the examples with low overlap scores. This suggests one limitation of the proposed system: Even with the aid of pre-trained language models, the system frequently fails to link between question tokens and database schema when their words are different. Better alignment methods between question tokens and database schema should be studied as a future work to further improve the system performance.

Another limitation of the proposed model is that the model does not use foreign keys during encoding; foreign keys are used only for JOIN table filtering. We analyzed the correlation between the number of foreign keys and exact matching accuracies in Figure 3, to figure out the effect of such limitation. The number of foreign keys and exact matching accuracy shows weak negative correlation, with Pearson correlation coefficient ρ = −0.22. Based on the analysis result, in future work we will try to integrate the foreign keys into the encoding process, for example, by using the relation aware transformer proposed in Wang et al. (2020), to improve the proposed model further.

In this article, we proposed a sketch-based slot-filling algorithm for complex, cross-domain Text-to-SQL problems. A detailed sketch for complex SELECT statement prediction is proposed, along with the Statement Position Code to handle nested queries. Two simple but effective input manipulation methods are additionally proposed to enhance the overall system performance further. The system achieved 3rd place among all systems and 1st place among the systems not using database content, on the challenging Spider benchmark data set.

Based on the error analysis results, as a next step of the research we will focus on globally updating the extracted slots by considering the slot prediction values as a whole. The analysis results also show the need to encode the relation structures of the database schema, for example, foreign keys, to improve the performance. We will also work on a method to effectively use the database content instead of using only the database schema, to further improve the system performance for the cases when database content is available.