# A COMPARATIVE STUDY OF DEEP REINFORCEMENT LEARNING MODELS: DQN VS PPO VS A2C

Neil de la Fuente<EMAIL_ADDRESS>Autonomous University of
BarcelonaBarcelonaSpain Computer Vision CenterBarcelonaSpain and  Daniel A.
Vidal Guerra<EMAIL_ADDRESS>Autonomous University of
BarcelonaBarcelonaSpain Computer Vision CenterBarcelonaSpain

(2024)

###### Abstract.

This study conducts a comparative analysis of three advanced Deep
Reinforcement Learning models – Deep Q-Networks (DQN), Proximal Policy
Optimization (PPO), and Advantage Actor-Critic (A2C) – exclusively within the
BreakOut Atari game environment. Our research aims to assess the performance
and effectiveness of these models in a singular, controlled setting. Through
rigorous experimentation, we examine each model’s learning efficiency,
strategy development, and adaptability under the game’s dynamic conditions.
The findings provide critical insights into the practical applications of
these models in game-based learning environments and contribute to the broader
understanding of their capabilities in specific, focused scenarios. Code is
publicly available: github.com/Neilus03/DRL_comparative_study

Deep Reinforcement Learning, BreakOut Atari Game, DQN, PPO, A2C

††copyright: acmlicensed††journalyear: 2024††conference: ; August 25-29, 2024;
††isbn: 978-x-xxxx-xxxx-x/24/08

##  1\. Introduction

In this comprehensive study, we explore the complex domain of Deep
Reinforcement Learning (DRL), focusing on a thorough comparison of three well-
known models: Deep Q-Networks (DQN), Proximal Policy Optimization (PPO), and
Advantage Actor-Critic (A2C), specifically within the BreakOut Atari game
environment. To ensure consistency and robustness in our experiments, we
exclusively utilized the well-established implementations of DQN, PPO, and A2C
from the Stable Baselines3 (SB3) framework (Raffin et al., 2021). This
methodology provides a clear and equitable platform for comparing the nuances
of each model’s learning strategy, adaptability, and efficiency within a
controlled environment.

Our methodology included a detailed exploration of the hyperparameter settings
for each model to comprehend their impact on performance. Specifically, we
varied the learning rates across the DQN, PPO, and A2C models from SB3 to
identify how these rates influenced the speed and efficiency of learning, as
well as the overall strategy development within the game, and gamma discount
factors to examine how short-term versus long-term reward prioritization
affected the models’ decision-making processes and overall performance.

This aspect of our study was key for revealing each model’s capability to
balance immediate rewards with future ones, a critical consideration in many
real-world applications of DRL. Through this comprehensive approach, our
research provides insights into the optimal configuration of these models for
efficient and effective learning in complex environments.

##  2\. Related Work

Deep Reinforcement Learning has emerged as a significant field combining Deep
Learning (DL) with Reinforcement Learning (RL), notably impacting areas like
gaming and robotics. The development of DQNs (Mnih et al., 2013) marked a
turning point in DRL by demonstrating the effective use of neural networks
with Q-learning to play Atari 2600 video games. It set a new precedent for
applying DL to approximate Q-values. Subsequent enhancements, such as Double
DQN (Van Hasselt et al., 2016) and Dueling DQN (Wang et al., 2016), addressed
key issues like Q-value overestimation and improved DQN’s stability.

PPO, introduced in (Schulman et al., 2017), advanced policy gradient methods.
PPO is especially noted for its efficient balance between sample efficiency
and ease of implementation. Its stable and reliable policy updates have made
it one of the preferred choice in the DRL community.

A2C, a simplified version of the Asynchronous Advantage Actor-Critic (A3C),
has been influential in actor-critic methods. A2C, while maintaining the dual
advantages of learning policy and value functions, removes the complexity of
asynchronous operations, as described in (Mnih et al., 2016).

Comparative studies in gaming environments, such as the one conducted in
(Henderson et al., 2018), provide insight into the practical applications of
these models and help to understand their strengths and weaknesses. However,
challenges like sample efficiency, stability, and generalization still persist
in DRL. Current research efforts in this field are focused on addressing these
issues to enhance the efficiency, scalability, and broader applicability of
DRL models.

##  3\. Methodology

Our experiments focused on the influence of varying hyperparameters. We tested
four to five learning rates for each model to observe the effects on
convergence and training robustness, and gamma values to explore the
prioritization of long-term versus immediate rewards. The training for each
model was consistently conducted over a fixed number of episodes, with episode
reward and learning stability as our primary performance metrics. This
approach facilitated a controlled and replicable comparison across the models,
ensuring that any observed differences in performance were attributable to the
models’ intrinsic characteristics and the selected hyperparameters.

###  3.1. Implementation of the models

Opting for SB3 allowed us to ensure that each of the three models adhered to
the highest standards and best practices within the field of DRL.

The DQN implementation is directly inspired by the architecture outlined in
(Mnih et al., 2013). The consistent and reliable performance of the SB3
version of DQN offered a robust foundation for our comparative exploration,
particularly within the context of the BreakOut game. PPO is particularly
notable for its balance between sample efficiency and simplicity in
implementation, aligning with the original design principles laid out by
(Schulman et al., 2017). Finally, the A2C model maintains the core advantages
of A3C while removing the need for asynchronous operations, as detailed in
(Mnih et al., 2016).

###  3.2. Hyperparameter Variations

We explored a spectrum of learning rates to evaluate the different speeds of
learning and robustness of strategy development. In tandem, we manipulated the
gamma discount factor with two distinct values, 0.99 and 0.90, to assess the
models’ sensitivity to short-term versus long-term rewards.

Our structured approach provides a foundation for an in-depth evaluation of
the learning dynamics and performance efficacy of each model, which we will
further develop in the ’Experiment Setup’ and ’Results’ sections.

##  4\. Hypotheses and Theoretical Considerations

We formed several hypotheses based on the theoretical foundations of the
models in question and their known strengths and weaknesses. Here we reflect
both the predictive and the conceptual nature of the content. In the
following, we describe our hypotheses for the performance of the different
models implemented.

###  4.1. Alignment of BreakOut Dynamics with DQN’s Value Estimation

In the BreakOut game, the dynamics are particularly well suited for the
Q-learning algorithm, upon which the DQN is based. Q-learning seeks to learn a
value function $Q(s,a)$, representing the expected return of taking an action
$a$ in a state $s$ and following the optimal policy thereafter. For BreakOut,
the state $s$ can be described by the position of the paddle, the ball, and
the configuration of the bricks, while the action $a$ corresponds to moving
the paddle left, right, or staying in place.

The game’s immediate and clear reward structure—points gained from breaking
bricks—aligns with the value estimation approach of DQN. The Q-function for
DQN is updated using the following rule:

$Q_{\text{new}}(s_{t},a_{t})\leftarrow
Q(s_{t},a_{t})+\alpha\left[r_{t+1}+\gamma\max_{a^{\prime}}Q(s_{t+1},a^{\prime})-Q(s_{t},a_{t})\right]$

where $\alpha$ is the learning rate, $\gamma$ is the discount factor,
$r_{t+1}$ is the immediate reward, and
$\max_{a^{\prime}}Q(s_{t+1},a^{\prime})$ is the maximum predicted value for
the next state $s_{t+1}$, over all possible actions $a^{\prime}$. This formula
allows DQN to incrementally improve its policy by learning from the immediate
outcomes of its actions, which in BreakOut are both straightforward and
tightly coupled with the action taken.

Contrastingly, on-policy methods like PPO and A2C involve estimating the
policy directly. The policy $\pi(a|s)$ represents the probability of taking
action $a$ in state $s$, and is adjusted in the direction suggested by the
policy gradient:

$\nabla_{\theta}J(\pi_{\theta})=\mathbb{E}_{\tau\sim\pi_{\theta}}\left[\sum_{t=0}^{T}\nabla_{\theta}\log\pi_{\theta}(a_{t}|s_{t})\cdot
A^{\pi_{\theta}}(s_{t},a_{t})\right]$

The advantage function $A^{\pi_{\theta}}(s_{t},a_{t})$, which measures the
relative value of an action compared to the average, is more complex to
estimate in a game like BreakOut. The reason is that certain strategies, like
”tunneling”, where the ball is directed behind the brick wall to clear
multiple bricks, require careful planning and a detailed understanding of the
game’s physics, which are not immediately obvious from the reward signal.

Estimating the value of strategies can be more challenging than directly
mapping actions to outcomes, as done in Q-learning. DQN’s value estimation
through Q-learning is more straightforward because it can directly correlate
actions like ”move the paddle under the ball” with receiving points for
breaking bricks. In contrast, PPO and A2C must learn through trial and error
which complex sequences of actions yield higher returns. This makes DQN
potentially more suited for BreakOut, where the optimal policy involves clear
and direct action-reward correlations, making the value function approach more
effective than direct policy estimation in this context.

###  4.2. Advantage of Experience Replay in DQN

The concept of experience replay is fundamental to the success of DQN. In the
BreakOut game, the mechanics often lead to repetitive scenarios where the
paddle must hit the ball in a rhythmic pattern. These recurrent interactions
between the ball and paddle could potentially lead to a form of learning that
is too specialized or optimized for the frequent but limited scenarios
encountered.

The experience replay mechanism mitigates this risk by storing a history of
experiences:

$e_{t}=(s_{t},a_{t},r_{t+1},s_{t+1})$

in a replay buffer $D_{t}$. This buffer acts as a diversified data reservoir
from which the DQN samples to update the value function.

This process ensures that the DQN does not simply memorize the most recent or
frequent patterns but instead develops a more generalized understanding of the
game by learning from a broader range of experiences.

The advantage of such an approach in BreakOut is twofold: it helps DQN avoid
local optima by not getting stuck in repetitive but suboptimal strategies, and
it allows DQN to learn from rare, high-reward episodes, such as when the ball
reaches the back of the brick wall creating a ’tunnel’, ensuring these crucial
experiences are not lost.

Continuously revisiting diverse past experiences makes DQN’s training more
robust and less prone to overfitting. This is especially beneficial in
BreakOut, where subtle differences between good and great strategies are
crucial. Experience replay enhances both learning and retention of effective
strategies, which is essential for mastering repetitive and pattern-based
environments.

###  4.3. Sensitivity to Hyperparameters Across Models

The sensitivity of an algorithm to its hyperparameters can greatly influence
its performance. Our hypothesis suggests that DQN and PPO will be less
sensitive to learning rate variations, while A2C will display greater
sensitivity.

DQN’s use of experience replay and fixed Q-targets provides a stabilizing
effect on learning updates. The experience replay randomizes the data, thus
breaking correlations in the observation sequence and smoothing changes in the
data distribution. This leads to stable gradient descent updates, allowing DQN
to handle a wider range of learning rates.

The robustness of PPO comes from its objective function, which uses a clipping
mechanism to prevent excessively large policy updates that could lead to
instability. This clipping mechanism acts as a safeguard against the potential
negative effects of choosing a suboptimal learning rate.

On the other hand, A2C’s performance is closely tied to its learning rate. As
an on-policy algorithm, A2C continuously updates its policy based on the
latest data it collects from the environment. If the learning rate is too low,
A2C’s policy may not adapt quickly enough to new information, leading to
suboptimal performance. On the contrary, a high learning rate might cause the
policy to change too fast, destabilizing the learning process. Therefore, the
ideal learning rate for A2C should balance quick data integration and
instability avoidance.

Discount factors also play a crucial role in the learning process. A high
discount factor $\gamma$ makes an algorithm more farsighted by placing more
emphasis on future rewards. This could potentially be more beneficial for DQN,
as it may enable the agent to better recognize the long-term benefits of
strategic moves like tunneling. A lower $\gamma$ could make the agent myopic,
prioritizing immediate rewards, which might suffice for simpler strategies but
fail to capture the depth of the game’s strategic possibilities.

In the case of PPO and A2C, a well-chosen discount factor helps in balancing
the trade-off between exploring new strategies and exploiting known rewarding
behaviors. PPO’s ability to maintain stable updates might be less affected by
the discount factor compared to A2C, as the latter can be more sensitive to
the choice of $\gamma$ due to its direct policy update mechanism.

In summary, we hypothesize that DQN and PPO will exhibit a higher tolerance to
hyperparameter variations, while A2C will require careful tuning of its
learning rate and discount factor to achieve optimal performance. This
sensitivity to hyperparameters is an essential consideration in the deployment
and tuning of reinforcement learning models in practice.

###  4.4. Policy Optimization Stability in PPO and A2C

The stability of policy optimization in PPO and A2C depends on their distinct
approaches to updating policies. PPO is designed to mitigate policy volatility
through a clipping mechanism in its objective function, which constrains the
extent of policy updates, fostering stability. The clipped objective in PPO is
given by:

$L^{\text{CLIP}}(\theta)=\mathbb{E}_{t}\left[\min(r_{t}(\theta)\hat{A}_{t},\text{clip}(r_{t}(\theta),1-\epsilon,1+\epsilon)\hat{A}_{t})\right]$

where $r_{t}(\theta)$ is the ratio of the new policy to the old policy
probabilities, $\hat{A}_{t}$ is the estimator of the advantage function at
time $t$, and $\epsilon$ is a hyperparameter that defines the clipping range.

Conversely, A2C updates policies after every step, lacking PPO’s clipping
barriers, which can lead to more aggressive policy changes:

$\Delta\theta=\alpha\nabla_{\theta}\log\pi_{\theta}(a_{t}|s_{t})A(s_{t},a_{t})$

The absence of a clipping mechanism in A2C allows for larger and more
disruptive policy updates, especially when the advantage $A(s_{t},a_{t})$ is
significant.

We expect PPO to exhibit more stable learning progression due to its
conservative update strategy, whereas A2C may show more fluctuations in its
performance. This variability reflects A2C’s responsiveness to immediate
environmental changes, which can result in rapid but occasionally unstable
learning trajectories.

These hypotheses serve not only as predictions to be tested against the
empirical data but also as a framework to interpret the complexities of model
behaviors. They reflect a balance between established theoretical knowledge
and the particularities of BreakOut.

##  5\. Experimental Setup

We tested the capabilities of DQN, PPO, and A2C by conducting our experiments
within the BreakOut Atari game environment, leveraging the Gymnasium framework
(Foundation, 2021).

###  5.1. Parameter Variations

  * •

Learning Rate: We experimented with learning rates at several magnitudes:
$1e-5,5e-5,1e-4,5e-4,1e-3,5e-3$.

  * •

Gamma Discount Factor: we varied the gamma discount factor between
$\gamma=0.99$ and $\gamma=0.90$.

For our experiments, training duration was quantified in terms of frames
rather than time or episodes, providing a consistent measure across all
models. Each model was trained for approximately 20 million frames. This
metric offers an objective standard of comparison, ensuring no model is
unintentionally favored by the measurement approach.

The frame-based approach aligns with the conventions of the SB3 framework and
allows for a fair assessment of each model’s learning process, as it accounts
for variations in episode lengths. For instance, more skilled agents may play
fewer but longer episodes.

An additional consideration is the potential for the game to enter into loops,
such as the ball bouncing between the wall and ceiling repeatedly without
hitting bricks. Such scenarios can artificially inflate the number of frames
without corresponding to meaningful learning or gameplay progress. This effect
is evident when certain training runs show extended plateaus in learning
curves or spikes in episode duration, which may indicate the agent has entered
a loop rather than achieving a breakthrough in strategy.

By standardizing the training duration across models via frame count, we
mitigate these factors, providing a clear, unbiased view of each model’s
learning efficiency and performance.

###  5.2. Evaluation Metrics

To assess the effectiveness of each model, we employed multiple evaluation
metrics. These metrics are critical as they capture various aspects of the
learning process and the models’ proficiency in navigating the environment.

Average Reward per Episode: Measures the mean score per episode. Higher scores
indicate better strategies and more efficient gameplay.

Episodes to Threshold: Counts the episodes needed to consistently reach reward
thresholds, indicating how fast the model learns in terms of episodes.

Time to Threshold: Measures the hours needed to reach reward thresholds,
showing learning speed.

Reward Distribution: Shows reward spread and variation, indicating performance
consistency. Narrow spread suggests stability; wide spread suggests
exploratory behaviour and instability.

Stability of Learning: Monitors performance fluctuations over time. Smooth
progression indicates steady learning; large variations suggest instability.

Frame Utilization Efficiency: Evaluates how efficiently models use allocated
frames, indicating purposeful learning and avoiding wasteful gameplay with
loops.

Each of these metrics contributes to a composite picture of model performance.
By examining these various facets, we can discern not only which model
achieved the highest scores but also understand the nuances of their learning
processes. This multifaceted evaluation is designed to dissect the models’
responses to different configurations within the standardized BreakOut
environment, setting the stage for a detailed analysis of their respective
strengths and weaknesses.

##  6\. Results

Our empirical investigation into the performance of DQN, PPO, and A2C within
the BreakOut Atari game environment yielded insightful findings, as detailed
in the subsections below. Figure 1 showcases the performance metrics,
providing a comprehensive comparison of the DQN, PPO, and A2C models across
all the configurations.

(a) DQN Performance

(b) PPO Performance

(c) A2C Performance

(d) Reward over training hours

(e) Reward over training steps for the best model

(f) Reward over training time for the best model

Figure 1. Performance metrics for DQN, PPO, and A2C models across different
settings.

###  6.1. Performance Across Learning Rates

From the data, DQN showed, in Figure 1(a), remarkable resilience across a
broad span of learning rates, consistent with our hypothesis that its
experience replay buffer would confer robustness against the variability in
learning rates.

PPO also showed resistance to changes in learning rate as it can be seen in
Figure 1(b), though its performance was more sensitive to the extremes. At the
lower end of the learning rate spectrum, PPO’s progress was gradual but
consistent. However, at higher rates, PPO’s performance was more variable,
likely due to the algorithm’s clipped objective function which, while
mitigating the risk of large destructive updates, also caps the potential
rapid advancements that can be achieved with more aggressive learning rates.

In contrast, A2C’s performance was heavily influenced by the learning rate as
shown in Figure 1(c). At lower rates, A2C’s learning curve was almost dead,
suggesting an inability to make significant policy improvements. As the
learning rate increased, A2C’s performance improved markedly, confirming our
prediction that A2C requires a careful balance in learning rate settings to
optimize its continuous policy updates.

###  6.2. Adaptability to Discount Factor Variations

The gamma ($\gamma$) discount factor influences the agent’s consideration of
future rewards. Across all models, a higher $\gamma$ value typically
correlated with a more strategic play, where long-term gains were prioritized.
DQN’s performance peaked at a moderate $\gamma$ value before declining,
suggesting a sweet spot where the agent was sufficiently foresighted without
being impeded by the overvaluation of distant future rewards.

PPO and A2C demonstrated a clearer preference for a higher $\gamma$ value.
This was particularly true for PPO, which displayed an steady increase in
performance as the $\gamma$ value rose, aligning with the algorithm’s inherent
stability and its capability to evaluate long-term strategies effectively.

###  6.3. Learning Stability, Efficiency, and Reward Optimization

In examining the learning process of DQN, PPO, and A2C models, we considered
several critical aspects: the stability and efficiency of learning, reward
optimization, and episode length within the BreakOut Atari game. DQN’s
performance stood out, demonstrating a smooth learning curve and efficient
frame utilization, highlighting its capability to rapidly assimilate and apply
successful strategies. This was mirrored in its real-time training efficiency,
where DQN consistently achieved high rewards in shorter durations, making it
an ideal candidate for scenarios demanding quick adaptation and time-efficient
learning. This can be clearly seen in Figure 1(d).

Contrastingly, PPO and A2C experienced more pronounced fluctuations in their
learning trajectories. PPO, though displaying a reasonable level of stability,
tended to engage in lengthier episodes, suggesting a propensity for a more
exploratory approach that may sacrifice swiftness for thoroughness. A2C’s
learning curve was the most variable, reflecting its sensitivity to
environmental dynamics and possibly a greater need for exploration to refine
its policy. The higher number of frames A2C required to reach performance
levels comparable to DQN implies a less efficient learning process, especially
marked when considering the model’s real-time performance, which lagged behind
the others.

Furthermore, reward optimization and episode length analysis revealed
strategic distinctions among the models. DQN’s strategy excelled in securing
high scores efficiently, as seen in Figures 1(e) and 1(f), translating
learning into performance gains with remarkable speed. In contrast, PPO and
A2C, despite their eventual improvements, needed longer episodes for similar
levels of reward, indicating potentially less efficient strategies. This
behavior underscores a strategic divergence where DQN prioritizes exploitation
of the known dynamics, while PPO and A2C seem to invest heavily in
exploration, an approach that may lead to substantial benefits in less
structured and more complex environments, but maybe not in simpler
environments like BreakOut.

The practical implications of our findings are significant. They suggest that
while DQN shines in environments requiring rapid learning and deployment, PPO
and A2C may offer advantages in scenarios where the depth of exploration and
comprehensive strategy development are key. Such insights are highly valuable
for the improved selection and application of reinforcement learning models,
emphasizing the importance of aligning the model’s strengths with the specific
demands and constraints of the task and environment.

In summary, our results substantiate the hypotheses posed in our theoretical
considerations, illustrating the nuanced interplay between model
architectures, hyperparameters, and the BreakOut game dynamics. The collected
data provides a comprehensive picture of how each model’s unique
characteristics influence its learning trajectory and overall performance in a
standardized environment. These insights prompt a reevaluation of the
preferred use cases for these models in the field of DRL.

##  7\. MAIN INSIGHTS

Below we list the main insights extracted from our study:

  * •

DQN: A Contender for Efficiency: The study’s revelations denote DQN’s
unexpected superiority in environments with clear, immediate reward
structures. DQN’s methodical approach to value estimation is well-suited to
such scenarios, allowing for a rapid and efficient development of effective
strategies. This efficiency is achieved, in part, thanks to the experience
replay buffer, which enables DQN to draw from a diverse set of past
experiences, preventing over-specialization and promoting a robust policy that
generalizes across a multitude of game states.

  * •

Model Adaptability and Hyperparameter Resilience: DQN’s performance exhibits
commendable resilience to hyperparameter fluctuations, making it a versatile
and forgiving model for practitioners. This adaptability is contrasted with
PPO and A2C, which, despite their advanced policy optimization capabilities,
show a pronounced sensitivity to hyperparameter settings. This needs a fine-
grained tuning process to navigate the trade-offs between exploration and
exploitation, especially in environments where the reward pathways are less
direct and the strategic demands are higher.

  * •

PPO and A2C for Strategic Exploration: In environments that reward deep
exploration and complex strategy development, PPO and A2C demonstrate their
power. Their policy gradient methods become advantageous in more complex state
spaces. While these models may require extended periods to converge on highly
rewarding strategies, their potential in tasks demanding a sophisticated level
of strategic planning is evident.

  * •

Towards a Contextual Model Selection Framework: The comparative performance of
DQN, PPO, and A2C accentuates the necessity for a contextual approach to model
selection in DRL. Our findings suggest that while DQN is optimal for tasks
demanding quick learning and efficient adaptation, environments characterized
by their difficulty and strategic complexity may benefit from the exploratory
strengths of PPO and A2C.

  * •

Implications for Practical Application: The implications of our findings for
the practical application of DRL are various. They serve as a guide for
practitioners in choosing a model that not only fits the immediate needs of
the task but also aligns with the long-term objectives of the learning
process. As our understanding of DRL models deepens, it becomes clear that the
decision-making framework for selecting a DRL model must be as dynamic and
diverse as the models themselves.

In conclusion, the study emphasizes the importance of carefully selecting DRL
models based on their suitability for the specific task, rather than their
perceived complexity. This strategy ensures that the chosen model is both
theoretically robust and practically effective, capable of leveraging the
unique dynamics of the environment to achieve optimal learning and
performance.

##  8\. Conclusion and Future Work

This study has detailedly evaluated DQN, PPO, and A2C within the BreakOut
Atari environment, revealing distinct capabilities and performance profiles
for each model. DQN, traditionally viewed as a simpler model, has demonstrated
its robustness and efficiency in a controlled setting, suggesting that its
utility in practical applications remains significant, especially in
environments with clear, immediate reward structures. PPO and A2C, while
typically favored for complex tasks due to their advanced policy optimization
techniques, require careful tuning and strategy development, which can be
advantageous in more intricate or less predictable environments. The findings
highlight the importance of choosing the right model based on the specific
characteristics and requirements of the task environment.

Future research should expand on the comparative analysis of DQN, PPO, and A2C
across a wider range of environments, including those with delayed rewards and
higher complexity. Further interesting studies could research the impact of
additional hyperparameters, network architectures, and reward shaping
techniques on the performance of these models.

Lastly, the exploration of DRL applications in real-world scenarios, such as
robotics, autonomous vehicles, and financial modeling, where the environment
is often unpredictable and complex, will be essential in translating these
findings into tangible benefits and advancements in the field of AI.

## References

  * (1)
  * Foundation (2021) The Farama Foundation. 2021.  Gymnasium: A fork of OpenAI Gym with additional environments and functionalities.  (2021).
  * Henderson et al. (2018) Peter Henderson, Riashat Islam, Philip Bachman, et al. 2018\.  Deep Reinforcement Learning that Matters.  _In Proceedings of the AAAI Conference on Artificial Intelligence_ (2018).
  * Mnih et al. (2016) Volodymyr Mnih, Adria Puigdomenech Badia, Mehdi Mirza, et al. 2016\.  Asynchronous Methods for Deep Reinforcement Learning.  _In Proceedings of the 33rd International Conference on International Conference on Machine Learning - Volume 48 (ICML’16). JMLR.org_ (2016), 1928–1937.
  * Mnih et al. (2013) Volodymyr Mnih, Koray Kavukcuoglu, David Silver, et al. 2013\.  Playing Atari with Deep Reinforcement Learning.  _arXiv preprint arXiv:1312.5602_ (2013).
  * Raffin et al. (2021) Antonin Raffin, Ashley Hill, Adam Gleave, et al. 2021\.  Stable Baselines3: Reliable Reinforcement Learning Implementations.  _Journal of Open Source Software_ (2021).
  * Schulman et al. (2017) John Schulman, Filip Wolski, Prafulla Dhariwal, et al. 2017\.  Proximal Policy Optimization Algorithms.  _arXiv preprint arXiv:1707.06347_ (2017).
  * Van Hasselt et al. (2016) Hado Van Hasselt, Arthur Guez, and David Silver. 2016.  Deep Reinforcement Learning with Double Q-learning.  _In Proceedings of the AAAI Conference on Artificial Intelligence_ (2016).
  * Wang et al. (2016) Ziyu Wang, Tom Schaul, Matteo Hessel, et al. 2016\.  Dueling Network Architectures for Deep Reinforcement Learning.  _In Proceedings of the 33rd International Conference on Machine Learning_ (2016).

##  Appendix A Reproducibility

To ensure the reproducibility of our results, we have made the code and data
used in this study available on GitHub. The repository contains detailed
instructions and scripts for replicating the experiments.

###  A.1. Repository Overview

The repository is organized into the following directories, each containing a
specific approach to solving BreakOut using different RL algorithms:

  * •

BreakOut_sb3_A2C: Implementation of the Advantage Actor-Critic (A2C) algorithm
using Stable Baselines 3.

  * •

BreakOut_sb3_PPO: Proximal Policy Optimization (PPO) approach using Stable
Baselines 3.

  * •

BreakOut_sb3_DQN: Implementation of DQN using Stable Baselines 3.

###  A.2. Installation

Ensure Python 3.10 is installed along with the following dependencies, which
are common across all implementations:

    pip install -r requirements.txt

###  A.3. Usage

1\. Clone and set up the repository:

    git clone https://github.com/Neilus03/DRL_comparative_study
    cd DRL_comparative_study
    pip install -r requirements.txt

2\. Navigate to the desired implementation directory, e.g., for PPO:

    cd BreakOut_sb3_PPO

3\. Configure the config.py file to adjust the model training, the wandb
account, and the saving model options.

4\. Execute the train.py to run the model or test.py if you already have a
pre-trained model to test.

###  A.4. Hardware Requirements

For reproducing the paper, it is recommended to use a GPU with at least 8 GB
of RAM to handle the computational load of training the models.


# MCS-SQL: Leveraging Multiple Prompts and Multiple-Choice Selection For Text-
to-SQL Generation

Dongjun Lee  Choongwon Park  Jaehyuk Kim  Heesoo Park

###### Abstract

Recent advancements in large language models (LLMs) have enabled in-context
learning (ICL)-based methods that significantly outperform fine-tuning
approaches for text-to-SQL tasks. However, their performance is still
considerably lower than that of human experts on benchmarks that include
complex schemas and queries, such as BIRD. This study considers the
sensitivity of LLMs to the prompts and introduces a novel approach that
leverages multiple prompts to explore a broader search space for possible
answers and effectively aggregate them. Specifically, we robustly refine the
database schema through schema linking using multiple prompts. Thereafter, we
generate various candidate SQL queries based on the refined schema and diverse
prompts. Finally, the candidate queries are filtered based on their confidence
scores, and the optimal query is obtained through a multiple-choice selection
that is presented to the LLM. When evaluated on the BIRD and Spider
benchmarks, the proposed method achieved execution accuracies of 65.5% and
89.6%, respectively, significantly outperforming previous ICL-based methods.
Moreover, we established a new SOTA performance on the BIRD in terms of both
the accuracy and efficiency of the generated queries.

Figure 1:  Overview of the proposed methodology, including three steps: schema
linking, multiple SQL generation, and selection.

##  1 Introduction

The text-to-SQL task involves translating a natural language question into SQL
and is crucial for natural language interfaces to databases (NLIDB) systems.
With the recent advancements in large language models (LLMs), in-context
learning (ICL)-based approaches for text-to-SQL (Pourreza and Rafiei, 2023a;
Gao et al., 2023; Tai et al., 2023) have demonstrated significant performance
improvements over traditional fine-tuning methods (Hui et al., 2022; Qi et
al., 2022; Li et al., 2023a, b). Notably, Pourreza and Rafiei (2023b) showed
that these methods even surpassed gold reference queries in terms of human
evaluation within the Spider benchmark. However, for the more challenging BIRD
(Li et al., 2023c) benchmark, characterized by its complex database (DB)
schemas and queries, the accuracies of ICL-based methods have not exceeded
60%, which is significantly lower than the 93.0% achieved by humans. This gap
underscores the need for further advancements in the ICL approach to serve as
an NLIDB system.

A significant limitation of LLMs across various tasks is their sensitivity to
the structure and content of prompts. Even for semantically identical prompts,
LLMs can generate drastically varying responses because of factors such as the
order of sentences (Jang and Lukasiewicz, 2023; Wang et al., 2023b), choice of
demonstration examples (Liu et al., 2022; Nori et al., 2023), and the sequence
in which these examples are presented (Lu et al., 2022). Our experiments
confirmed a similar tendency in the text-to-SQL context, where alterations in
the schema presentation (section 5.3) and the choice of few-shot examples
(section 5.4) resulted in variations in LLM outputs.

In this study, to improve the accuracy and robustness of LLMs for text-to-SQL,
we introduce a novel approach that leverages multiple prompts to generate
various candidate answers and effectively aggregates them. We utilize the
sensitivity of LLMs to prompts to explore a broader search space for answers
using different prompts. As shown in Figure 1, the SQL generation process
comprises three steps: schema linking, multiple SQL generation, and selection.
Initially, the schema-linking phase robustly selects tables and columns
relevant to the question from the DB schema using multiple prompts.
Subsequently, the generation phase employs various prompts to produce diverse
candidate SQL queries, ensuring a broader exploration of potential queries.
Finally, the selection phase filters candidate queries based on confidence
scores, and the optimal query is selected through multiple-choice selection
(MCS) that is presented to the LLM.

We evaluated our methodology using two benchmarks, BIRD (Li et al., 2023c) and
Spider (Yu et al., 2018). For BIRD, we achieved an execution accuracy (EX) of
65.5% and a valid efficiency score (VES) of 71.4%, which outperforms the
previous state-of-the-art (SOTA) ICL-based method (Wang et al., 2023a) by
+5.9% and +3.7%, respectively, setting a new SOTA performance for the BIRD. In
addition, for the Spider test set, we achieved an EX of 89.6%, which
outperforms the existing SOTA ICL-based approach (Gao et al., 2023) by 3.0%.

##  2 Related Work

##### Prompt Engineering

Prompt engineering, which is the study of designing effective prompts, is an
active research area as prompts significantly impact the performance of LLMs
across various NLP tasks. A prominent example is the chain-of-thought (CoT)
prompting (Wei et al., 2022), which employs manually crafted examples to guide
the LLM to generate intermediate reasoning steps prior to deriving the answer.
This technique has demonstrated significant performance enhancements across
various tasks. Kojima et al. (2022) further demonstrated that LLMs can explain
their reasoning steps even in the absence of human-annotated examples.

In addition to CoT, self-consistency decoding (Wang et al., 2022) has been
proposed, wherein multiple answers are sampled from the LLM before selecting
one through a majority vote. Compared with greedy decoding, this technique
facilitates the exploration of various reasoning paths and has exhibited
substantial performance gains. For more effective explorations of the
reasoning steps, variations such as tree-of-thought (Wei et al., 2022) and
graph-of-thought (Besta et al., 2023) have also been proposed.

However, because these approaches rely on a single prompt to generate the
reasoning steps, we hypothesize that they fail to mitigate the issue of LLMs’
high sensitivity to prompts, thereby exploring only a limited search space.
Several studies have shown that even when presented with semantically
identical prompts, the outputs of LLMs vary based on factors such as sentence
structure (Webson and Pavlick, 2022), sequence of sentences (Jang and
Lukasiewicz, 2023; Wang et al., 2023b; Pezeshkpour and Hruschka, 2023), choice
of few-shot examples (Liu et al., 2022; Nori et al., 2023), and the order in
which the examples are presented (Lu et al., 2022). Therefore, we use multiple
distinct prompts for a wider exploration of potential answers, thereby
mitigating the LLM’s sensitivity issue of LLMs to prompts and ensuring the
generation of more robust answers.

##### ICL for Text-to-SQL

As ICL-based approaches have shown remarkable performance in text-to-SQL
tasks, various studies have focused on creating better prompts for text-to-
SQL. Several studies have focused on applying prompting techniques such as CoT
or least-to-most (Zhou et al., 2022) for text-to-SQL generation (Pourreza and
Rafiei, 2023a; Tai et al., 2023; Wang et al., 2023a). However, these methods
rely on fixed sets of manually crafted examples, and their performance can
vary significantly depending on the selection of these examples. In this work,
instead of relying on fixed human-labeled examples, we dynamically select few-
shot examples from the training data based on the test sample. Some studies
have aimed to determine a more effective few-shot selection strategy for text-
to-SQL (Guo et al., 2023; Nan et al., 2023; Gao et al., 2023). However, unlike
these studies, which focused on determining a single optimal selection
strategy, we employ a parallel approach that employs various few-shot
selection strategies with multiple prompts and effectively aggregates them.

##  3 Methodology

As shown in Figure 1, the proposed method comprises three steps: (1) schema
linking, wherein tables and columns irrelevant to the question from the DB
schema are excluded; (2) multiple SQL generation, wherein multiple candidate
SQL queries are generated based on various prompts; and (3) selection, wherein
the most accurate SQL query is selected from among the candidates.

###  3.1 Schema Linking

Schema linking involves identifying relevant tables and columns from a DB to
convert a natural language question into an SQL query (Guo et al., 2019). The
introduction of schema linking has significantly improved the performances of
both fine-tuning-based (Lei et al., 2020; Xu et al., 2022; Qi et al., 2022; Li
et al., 2023a) and ICL-based (Dong et al., 2023; Pourreza and Rafiei, 2023a;
Wang et al., 2023a) approaches.

We perform schema linking in two steps: first, the tables related to the
natural language query are extracted (table linking). Thereafter, the
necessary columns within those tables are extracted (column linking). We
employ multiple prompts in both phases with the aim of achieving a high
recall.

####  3.1.1 Table Linking

In table linking, the DB schema and question are input into the LLM, which
extracts a list of reference tables to generate the SQL query. Inspired by
zero-shot-CoT (Kojima et al., 2022), we ask the LLM to explain why each table
is necessary, instead of just selecting a list of tables. To easily parse the
LLM’s answer, we ask it to respond in the JSON format, as described in Listing
LABEL:lst:table-linking.

⬇

### For a given DB schema and question, extract the list of tables required to
write the SQL query.

### DB schema: ...

### Question: ...

Your answer should be in the json format:

{

"reasoning": "..." # The reason for selecting each table.

"answer": [...] # List of selected tables.

}

### Your answer:

Listing 1: Prompt template for table linking. An example of the full prompt is
presented in Appendix B.1.1.

To enhance the robustness of table linking, we utilize multiple prompts.
Various studies have demonstrated that LLM outputs are significantly affected
by the sequence of input sentences (Jang and Lukasiewicz, 2023; Wang et al.,
2023b; Liu et al., 2023). Similarly, our experiments (section 5.3) revealed
that the output of schema-linking output of LLMs also depends on the sequence
in which the tables and columns are arranged in the prompts. To minimize the
influence of the table order, we randomly shuffle the order of tables,
generating $p_{t}$ distinct prompts. For each prompt, we obtain $n$ responses
from the LLM by using a high sampling temperature. The final table-linking
output is derived from the union of all responses, amounting to $p_{t}\cdot n$
table lists. We use a union operation because including unnecessary tables in
table linking does not significantly impact the subsequent SQL-generation
process; however, omitting the necessary tables prevents the generation of the
correct SQL query.

####  3.1.2 Column Linking

For column linking, we ask the LLM to extract the columns required for
converting a question into an SQL query using a prompt similar to that used in
table linking. The prompt includes only the schemas of the tables selected
during table linking, instead of the entire DB schema. Because the same column
name can exist in different tables, we instruct the LLM to provide the answer
in the [table_name].[column_name] format.

Similar to table linking, the order of the tables and columns is randomly
shuffled to generate $p_{c}$ unique prompts. Subsequently, $n$ LLM responses
are generated for each prompt, where each response represents a selected
column list. The column-linking output is the union of all $p_{c}*n$
responses.

In the subsequent SQL-generation steps, when providing the DB schema to LLM,
only the tables and columns selected through schema linking are provided
instead of the full schema.

###  3.2 Multiple SQL Generation

To address the sensitivity of the LLM to prompts, we generate various SQL
queries based on multiple, distinct prompts. Several studies have demonstrated
that the output of an LLM can differ significantly depending on the few-shot
examples provided (Liu et al., 2022; Wu et al., 2023), and even on the
sequence in which these examples are presented (Lu et al., 2022; Zhao et al.,
2021). To effectively leverage this variability, we generate multiple prompts
by varying both the selection method of the few-shot examples and the order in
which they are presented, thereby ensuring a broader exploration of potential
SQL queries.

####  3.2.1 Few-Shot Examples Selection

For each test sample, a set of few-shot examples is selected from the training
dataset. To generate multiple prompts with different examples, we use two
distinct selection strategies: one that leverages question similarity and
another that utilizes masked question similarity. In the question similarity-
based approach, we select the top-k questions from the training dataset that
have the nearest sentence embeddings to the natural language question of the
test sample.

Similarly, the masked question similarity-based approach considers the
embedding similarity of masked questions, wherein tokens specific to the DB
schema in the question are masked. This masking allows determining the
similarity of questions in terms of generating similar queries by disregarding
schema-specific content. We employ an LLM for the masking process by
presenting it with the DB schema and question, and asking it to replace the
table names, column names, and values with special tokens. The prompt for this
question masking is presented in Appendix B.2.

Through these two few-shot selection strategies, we generate $p_{q}$ different
prompts, including one derived exclusively from question similarity, another
solely from masked question similarity, and additional prompts created by
integrating examples from both strategies in various sequences.

####  3.2.2 SQL Generation

⬇

### Generate the correct SQL query for a given DB schema and question.

### Gold Examples:

- Question: ...

- Gold SQL: ...

...

### DB Schema: ...

### Sample Table Contents: ...

### Question: ...

Your answer should be in the json format:

{

"reasoning": ".." # The reasoning steps behind the generated SQL query

"sql": ".." # The generated SQL query.

}

### Your answer:

Listing 2: Prompt template for SQL generation. An example of the full prompt
is presented in Appendix B.3.

As illustrated in Listing LABEL:lst:sql-generation, our SQL-generation prompt
includes few-shot examples, a DB schema, sample table contents, and a natural
language question. The few-shot examples comprise questions and their
corresponding gold SQL pairs. To conserve the limited length of the prompt, we
exclude the schema of the target DB for each question. Regarding the DB
schema, we selectively present only the tables and columns selected during the
schema-linking process to avoid burdening the LLM with irrelevant information.
Additionally, we embed sample table contents in the CSV format within the
prompt to facilitate the LLM’s comprehension of potential values in each
column, thereby providing practical insight into the data structure of the DB.
Finally, we instruct LLM not only to produce the SQL query but also to explain
the reasoning behind its generation, thereby enhancing the model’s
interpretability and accuracy.

For each prompt, we generate $n$ responses from the LLM using a high sampling
temperature, resulting in $p_{q}\cdot n$ candidate SQL queries being
generated.

###  3.3 Selection

The selection step aims to select the most accurate query from the candidate
queries. Initially, the candidate pool is filtered based on confidence scores,
and the LLM is then tasked with selecting the most accurate query from the
refined pool.

####  3.3.1 Candidate Filtering

To select the most accurate query among the candidates, we first narrow down
the candidate pool. Queries with the same execution results are grouped
together, and only the fastest query from each group is retained.
Additionally, queries with low confidence scores are excluded from the
candidates.

In detail, all candidate queries are executed on the DB, and queries that
result in syntax errors or timeouts are removed from the candidate pool. Next,
the confidence score for each query in the candidate pool is calculated, which
is determined based on the number of queries that produce the same execution
result. Formally, for a candidate pool $C$ containing $N$ queries
$\\{q_{1},\ldots,q_{N}\\}$, the confidence of query $q_{i}$ is computed as
follows:

$\displaystyle\text{confidence}(q_{i})=\frac{1}{N}\sum_{j=1}^{N}[\text{exec}(q_{j})=\text{exec}(q_{i})]$
(1)

where $\text{exec}(q_{i})$ denotes the execution result for query $q_{i}$.

Among the queries in candidate pool $C$ with identical execution results, only
the query with the minimum execution time is selected as follows:

$\displaystyle
C^{\prime}=\bigcup_{R\in\mathcal{R}(C)}\operatorname*{arg\,min}_{q_{i}\in
C,\text{exec}(q_{i})=R}{\text{exec\\_time}(q_{i})}$ (2)

where $\mathcal{R}(C)$ represents the set of all unique execution results from
all queries in $C$, and $\text{exec\\_time}(q_{i})$ denotes the execution time
for query $q_{i}$.

Finally, all queries in $C^{\prime}$ with confidence score below threshold $T$
are excluded as follows:

$\displaystyle C^{\prime\prime}=\\{q_{i}\in
C^{\prime}\mid\text{confidence}(q_{i})\geq T\\},$ (3)

resulting in a refined candidate pool $C^{\prime\prime}$.

####  3.3.2 Multiple-Choice Selection (MCS)

Following the filtering process, we utilize the LLM to select the most
accurate query among the candidates through a multiple-choice question.

⬇

### For a given DB schema and question, select the most accurate query among
the candidate SQL queries.

### DB schema: ...

### Question: ...

### Candidate SQLs:

1. SQL1

2. SQL2

3. SQL3

Your answer should be in the json format:

{

"reasoning": ".." # The reasoning steps for selecting the correct SQL query.

"sql": ".." # The selected SQL query.

}

### Your answer:

Listing 3: Prompt template for SQL selection. An example of the full prompt is
presented in Appendix B.4.

As shown in the Listing LABEL:lst:mcq-prompt, we present a set of candidate
SQL queries to the LLM and request it to select the most accurate query for a
given DB schema and question. Candidate queries are provided in descending
order of confidence scores, considering the tendency of LLMs to favor options
that appear earlier in the multiple-choice questions (Wang et al., 2023b;
Zheng et al., 2023). The LLM is required to not only select an SQL query but
also provide the reasons for its selection. We sample $n$ responses from the
LLM and determine the final SQL query through a majority vote.

| Dev | Test
---|---|---
Model | EX | VES | EX | VES
GPT-4 (zero-shot) | 46.4 | 49.8 | 54.9 | 60.8
DIN-SQL + GPT-4 (Pourreza and Rafiei, 2023a) | 50.7 | 58.8 | 55.9 | 59.4
DAIL-SQL + GPT-4 (Gao et al., 2023) | 54.8 | 56.1 | 57.4 | 62.0
MAC-SQL + GPT-4 (Wang et al., 2023a) | 57.7 | 58.8 | 59.6 | 67.7
MCS-SQL + GPT-4 (Ours) | 63.4 | 64.8 | 65.5 | 71.4
Table 1: Execution accuracies (EX) and valid efficiency scores (VES) for the BIRD dev and test sets. Model | Dev | Test
---|---|---
GPT-4 (zero-shot) | 74.6 | -
DIN-SQL + GPT-4 (Pourreza and Rafiei, 2023a) | 82.8 | 85.3
DAIL-SQL + GPT-4 (Gao et al., 2023) | 84.4 | 86.6
MAC-SQL + GPT-4 (Wang et al., 2023a) | 86.8 | -
MCS-SQL + GPT-4 (Ours) | 89.5 | 89.6
Table 2: Execution accuracies for the Spider dev and test sets. "-" denotes
that the model did not report performance for the test set.

##  4 Experimental Setup

###  4.1 Datasets

##### Spider

Spider (Yu et al., 2018) is a large-scale, complex, cross-domain text-to-SQL
benchmark comprising 10,181 questions and 5,693 distinct queries across 200
databases, each with multiple tables. This benchmark requires the model to
adapt to an unseen DB schema because different DBs are used for training and
testing.

##### BIRD

BIRD (Li et al., 2023c) is a new large-scale, cross-domain text-to-SQL
benchmark comprising 12,751 unique question-SQL pairs across 95 large real-
world databases. Compared with Spider, BIRD comprises considerably more
complex SQL queries with various SQL keywords (LEFT JOIN, PARTITION BY, etc.)
and functions (IIF, CASE, ROUND, etc.) that are not included in Spider. In
addition, BIRD requires reasoning using external knowledge (such as synonym
knowledge and value illustrations) to generate accurate SQL queries.

###  4.2 Evaluation Metrics

##### Execution Accuracy (EX)

EX indicates whether the SQL execution result generated by the model is
identical to the gold SQL query. Because an SQL query can be written in
various forms to produce the same result, evaluation metrics based on string
matching significantly underestimate model performance.

##### Valid Efficiency Score (VES)

For the BIRD dataset, Li et al. (2023c) proposed an additional evaluation
metric called VES that measures the efficiency of a valid model-generated
query based on the execution time. A query is considered invalid and assigned
a score of zero if its execution result differs from that of the gold SQL.
Therefore, VES considers both the model accuracy and efficiency for the
generated query.

###  4.3 Implementation Details

In all of our experiments, we used the GPT-4 8K as the LLM and text-embedding-
ada-002 as the sentence embedding model, which was accessed via Azure OpenAI
API. Additionally, we employed the FAISS (Douze et al., 2024) library for the
embedding similarity search. In schema linking, we used $p_{t}=3$ prompts for
table linking and $p_{c}=3$ prompts for column linking. To generate multiple
candidate SQL queries, we use $p_{q}=5$ distinct prompts. For each GPT API
call, we used a temperature of 1.0 and generated $n$=20 responses. In both the
SQL-generation and MCS steps, we used $k$=20 question-SQL pairs as few-shot
examples. We executed all candidate SQL queries with a timeout of 180s and
filtered out queries with a confidence score lower than the threshold $T$=0.2.

Method | Simple | Moderate | Challenging | ALL
---|---|---|---|---
zero-shot | 59.0 | 40.2 | 30.6 | 50.7
\+ schema linking | 61.4 | 41.1 | 35.4 | 52.8 (+2.1)
\+ sample table contents | 64.1 | 42.6 | 38.9 | 55.2 (+2.4)
\+ few-shot examples | 67.1 | 51.6 | 41.0 | 60.0 (+4.8)
\+ MCS ($p_{q}$=1, $n$=20) | 69.3 | 52.0 | 48.6 | 62.1 (+2.1)
\+ MCS ($p_{q}$=5, $n$=20) | 70.4 | 53.1 | 51.4 |  63.4 (+1.3)
Table 3: Execution accuracies for the ablation analysis on the BIRD dev set across various difficulty levels. Method | Easy | Medium | Hard | Extra Hard | ALL
---|---|---|---|---|---
zero-shot | 79.0 | 83.4 | 66.7 | 52.4 | 74.6
\+ schema linking | 86.7 | 85.0 | 67.2 | 57.2 | 77.9 (+3.3)
\+ sample table contents | 89.9 | 88.6 | 69.0 | 59.0 | 80.9 (+3.0)
\+ few-shot examples | 94.0 | 90.4 | 86.2 | 66.3 | 86.7 (+5.8)
\+ MCS ($p_{q}$=1, $n$=20) | 93.1 | 92.6 | 88.5 | 72.2 | 88.8 (+2.1)
\+ MCS ($p_{q}$=5, $n$=20) | 94.0 | 93.5 | 88.5 | 72.9 |  89.5 (+0.7)
Table 4: Execution accuracies for the ablation analysis on the Spider dev set
across various difficulty levels.

###  4.4 Baselines

We compare the proposed MCS-SQL approach with ICL-based methods based on
GPT-4.

##### GPT-4 (Achiam et al., 2023)

uses the zero-shot prompt provided in OpenAI’s text-to-SQL
demo111https://platform.openai.com/examples/default-sql-translate.

##### DIN-SQL (Pourreza and Rafiei, 2023a)

classifies the complexity of the question and generates an SQL query by
applying different prompts based on the classification result. In each step,
it uses a prompt with fixed few-shot examples that are manually written in the
CoT style.

##### DAIL-SQL (Gao et al., 2023)

employs dynamic few-shot examples by considering the similarity of both the
questions and the queries. For additional performance improvement, self-
consistency (Wang et al., 2022) is introduced.

##### MAC-SQL (Wang et al., 2023a)

decomposes the question into sub-questions and sequentially generates SQL
queries for each sub-question using manually crafted few-shot samples.
Additionally, in case of a syntax error, it uses a prompt to correct the
generated query.

##  5 Results and Analysis

###  5.1 Main Results

##### BIRD

Table 1 presents the EX and VES of the proposed and baseline models for the
BIRD dev and test sets. The results demonstrate that the proposed approach
significantly outperforms existing ICL-based approaches in both metrics.
Specifically, the proposed method achieved an EX of 65.45% and a VES of 71.35%
on the holdout test set, surpassing the performance of the previous SOTA ICL-
based approach (Wang et al., 2023a) by significant margins of 5.86% and 3.67%,
respectively. Furthermore, the proposed method established a new SOTA
performance on the BIRD, surpassing the former SOTA method with a substantial
margin of 4.74% in EX and 3.67% in VES.

##### Spider

Table 2 presents the EX of the proposed and baseline methods for the Spider
dev and test sets. Similar to the results obtained for BIRD, the proposed
approach significantly outperforms all existing ICL-based approaches.
Specifically, on the dev set, our approach achieved an EX of 89.5, which
exceeds that of the former SOTA ICL-based approach (Wang et al., 2023a) by
+2.7%.

###  5.2 Ablation Study

We conducted an ablation study to investigate the incremental impact of each
component of the proposed approach on the EX. The ablation results for the
BIRD dev set are presented in Table 3. The addition of schema linking to the
baseline zero-shot setting resulted in a 2.1% improvement. This underscores
the importance of refining the schema prior to SQL generation and shows that
the proposed schema-linking process effectively selects relevant tables and
columns. The inclusion of the sample table contents in the prompt further
amplified this gain by +2.4%. The introduction of dynamic few-shot examples
that were selected based on masked question similarity resulted in the largest
performance improvement of +4.8%. Moreover, when we sampled multiple answers
from the LLM using the same prompt and employed the proposed MCS method, the
performance further improved by 2.1%. This demonstrates the capability of the
proposed SQL selection method in discerning and selecting the most accurate
query from a set of candidates. Finally, introducing multiple prompts led to
further enhancements of +1.3%, particularly showing significant performance
improvement on challenging queries. This improvement demonstrates that
broadening the search space using various prompts significantly boosted the
SQL-generation accuracy.

Table 4 lists the ablation results for the Spider dev set, wherein it is
evident that each component of the proposed approach contributed to
significant performance gains, similar to the results obtained for BIRD. This
consistent performance enhancement across different benchmarks confirms the
effectiveness and adaptability of the proposed approach for the text-to-SQL
task.

###  5.3 Impact of Using Multiple Prompts in Schema Linking

We conducted a comparative analysis of the following three cases to
investigate the impact of using multiple prompts in schema linking: (1) greedy
decoding with a single prompt; (2) taking the union of multiple answers
generated from a single prompt; and (3) taking the union of multiple answers
generated from multiple prompts. Table 5 lists the recall of schema linking
for each case, which was calculated based on whether the predicted list of
tables and columns included those used in the gold query.

The results demonstrate that sampling multiple responses using the same prompt
and aggregating them led to notable performance gains of +15.8% for BIRD and
+4.7% for Spider. However, leveraging multiple prompts contributed to further
significant improvements, with gains of +12.7% for BIRD and +2.7% for Spider.
These results indicate that the order of tables and columns in the prompt
affects the schema-linking results of the LLM and that the proposed multiple-
prompt approach effectively mitigates this sensitivity. This improvement was
particularly noticeable in BIRD, implying that the effectiveness of using
multiple prompts increases for larger and more complex DB schemas.

As it is impossible to generate accurate SQL queries in subsequent processes
if the necessary tables or columns are omitted in schema linking, our proposal
of using the union of various responses from multiple prompts is pivotal for
enhancing the SQL-generation performance.

Method | BIRD | Spider
---|---|---
$p_{t}=p_{c}=1$, $n$=1 | 61.3 | 91.8
$p_{t}=p_{c}=1$, $n$=20 | 77.1 | 96.5
$p_{t}=p_{c}=3$, $n$=20 (proposed) | 89.8 | 99.2
Table 5: Recall of schema linking for the BIRD and Spider dev sets under three
different settings: (1) greedy decoding with a single prompt, (2) sampling
multiple answers from the same prompt, and (3) employing multiple prompts.

###  5.4 Impacts of Different Few-shot Selection Strategies

Table 6 presents the EXs when different few-shot strategies were employed. The
performance improved significantly by selecting few-shot examples based on
question similarity instead of random selection, with an increase of 2.3% for
BIRD and 4.2% for Spider. Additionally, a further performance boost was noted
by predicating the selection on the similarity of the masked question rather
than the original question, with enhancements of 0.7% and 0.5% for BIRD and
Spider, respectively.

Method | BIRD | Spider
---|---|---
Random | 57.0 | 82.0
Question Similarity | 59.3 | 86.2
Masked Question Similarity | 60.0 | 86.7
Table 6: Execution accuracies under different few-shot examples selection
strategies for the BIRD and Spider dev sets.

###  5.5 Impact of MCS

During the SQL selection phase (section 3.3), we explored whether the proposed
MCS via LLM was more effective than a majority vote, which selects the query
with the highest confidence score. As presented in Table 7, the proposed MCS
approach outperformed the majority vote approach by +0.6% and +0.3% for the
BIRD and Spider, respectively. Notably, in the absence of confidence-based
filtering, as expressed in Eq. (3), the efficacy of the MCS method decreased
significantly. This result underscores the importance of employing confidence-
based filtering to effectively narrow down the candidate pool when using MCS.

Method | BIRD | Spider
---|---|---
Majority Vote | 62.8 | 89.2
MCS w/o confidence filtering | 62.5 | 85.4
MCS w/ confidence filtering (proposed) | 63.4 | 89.5
Table 7: Execution accuracies under different SQL selection strategies for the
BIRD and Spider dev sets.

##  6 Conclusion

This study introduces a novel method that leverages multiple prompts to
enhance the accuracy and robustness of ICL-based text-to-SQL generation.
Specifically, the proposed approach performs robust schema linking using
distinct prompts. In addition, we employ different few-shot selection
strategies to generate multiple query generation prompts, which yield various
candidate SQL queries. These candidates are subsequently filtered based on
their confidence scores, and the optimal query is selected using the LLM with
MCS. Evaluations on the BIRD and Spider benchmarks showed that the proposed
approach significantly outperforms existing ICL-based approaches and achieved
a new SOTA performance on the BIRD.

## References

  * Achiam et al. (2023) Josh Achiam, Steven Adler, Sandhini Agarwal, Lama Ahmad, Ilge Akkaya, Florencia Leoni Aleman, Diogo Almeida, Janko Altenschmidt, Sam Altman, Shyamal Anadkat, et al. 2023.  Gpt-4 technical report.  _arXiv preprint arXiv:2303.08774_.
  * Besta et al. (2023) Maciej Besta, Nils Blach, Ales Kubicek, Robert Gerstenberger, Lukas Gianinazzi, Joanna Gajda, Tomasz Lehmann, Michal Podstawski, Hubert Niewiadomski, Piotr Nyczyk, et al. 2023.  Graph of thoughts: Solving elaborate problems with large language models.  _arXiv preprint arXiv:2308.09687_.
  * Dong et al. (2023) Xuemei Dong, Chao Zhang, Yuhang Ge, Yuren Mao, Yunjun Gao, Jinshu Lin, Dongfang Lou, et al. 2023.  C3: Zero-shot text-to-sql with chatgpt.  _arXiv preprint arXiv:2307.07306_.
  * Douze et al. (2024) Matthijs Douze, Alexandr Guzhva, Chengqi Deng, Jeff Johnson, Gergely Szilvasy, Pierre-Emmanuel Mazaré, Maria Lomeli, Lucas Hosseini, and Hervé Jégou. 2024.  The faiss library.
  * Gao et al. (2023) Dawei Gao, Haibin Wang, Yaliang Li, Xiuyu Sun, Yichen Qian, Bolin Ding, and Jingren Zhou. 2023.  Text-to-sql empowered by large language models: A benchmark evaluation.  _arXiv preprint arXiv:2308.15363_.
  * Guo et al. (2023) Chunxi Guo, Zhiliang Tian, Jintao Tang, Pancheng Wang, Zhihua Wen, Kang Yang, and Ting Wang. 2023.  Prompting gpt-3.5 for text-to-sql with de-semanticization and skeleton retrieval.  In _Pacific Rim International Conference on Artificial Intelligence_ , pages 262–274. Springer.
  * Guo et al. (2019) Jiaqi Guo, Zecheng Zhan, Yan Gao, Yan Xiao, Jian-Guang Lou, Ting Liu, and Dongmei Zhang. 2019.  Towards complex text-to-SQL in cross-domain database with intermediate representation.  In _Proceedings of the 57th Annual Meeting of the Association for Computational Linguistics_ , pages 4524–4535, Florence, Italy. Association for Computational Linguistics.
  * Hui et al. (2022) Binyuan Hui, Ruiying Geng, Lihan Wang, Bowen Qin, Yanyang Li, Bowen Li, Jian Sun, and Yongbin Li. 2022.  S2sql: Injecting syntax to question-schema interaction graph encoder for text-to-sql parsers.  In _Findings of the Association for Computational Linguistics: ACL 2022_ , pages 1254–1262.
  * Jang and Lukasiewicz (2023) Myeongjun Jang and Thomas Lukasiewicz. 2023.  Consistency analysis of ChatGPT.  In _Proceedings of the 2023 Conference on Empirical Methods in Natural Language Processing_ , pages 15970–15985, Singapore. Association for Computational Linguistics.
  * Kojima et al. (2022) Takeshi Kojima, Shixiang Shane Gu, Machel Reid, Yutaka Matsuo, and Yusuke Iwasawa. 2022.  Large language models are zero-shot reasoners.  _Advances in neural information processing systems_ , 35:22199–22213.
  * Lei et al. (2020) Wenqiang Lei, Weixin Wang, Zhixin Ma, Tian Gan, Wei Lu, Min-Yen Kan, and Tat-Seng Chua. 2020.  Re-examining the role of schema linking in text-to-sql.  In _Proceedings of the 2020 Conference on Empirical Methods in Natural Language Processing (EMNLP)_ , pages 6943–6954.
  * Li et al. (2023a) Haoyang Li, Jing Zhang, Cuiping Li, and Hong Chen. 2023a.  Resdsql: Decoupling schema linking and skeleton parsing for text-to-sql.  In _Proceedings of the AAAI Conference on Artificial Intelligence_ , volume 37, pages 13067–13075.
  * Li et al. (2023b) Jinyang Li, Binyuan Hui, Reynold Cheng, Bowen Qin, Chenhao Ma, Nan Huo, Fei Huang, Wenyu Du, Luo Si, and Yongbin Li. 2023b.  Graphix-t5: Mixing pre-trained transformers with graph-aware layers for text-to-sql parsing.  _arXiv preprint arXiv:2301.07507_.
  * Li et al. (2023c) Jinyang Li, Binyuan Hui, Ge Qu, Binhua Li, Jiaxi Yang, Bowen Li, Bailin Wang, Bowen Qin, Rongyu Cao, Ruiying Geng, Nan Huo, Chenhao Ma, Kevin C. C. Chang, Fei Huang, Reynold Cheng, and Yongbin Li. 2023c.  Can llm already serve as a database interface? a big bench for large-scale database grounded text-to-sqls.
  * Liu et al. (2022) Jiachang Liu, Dinghan Shen, Yizhe Zhang, William B Dolan, Lawrence Carin, and Weizhu Chen. 2022.  What makes good in-context examples for gpt-3?  In _Proceedings of Deep Learning Inside Out (DeeLIO 2022): The 3rd Workshop on Knowledge Extraction and Integration for Deep Learning Architectures_ , pages 100–114.
  * Liu et al. (2023) Nelson F Liu, Kevin Lin, John Hewitt, Ashwin Paranjape, Michele Bevilacqua, Fabio Petroni, and Percy Liang. 2023.  Lost in the middle: How language models use long contexts.  _arXiv preprint arXiv:2307.03172_.
  * Lu et al. (2022) Yao Lu, Max Bartolo, Alastair Moore, Sebastian Riedel, and Pontus Stenetorp. 2022.  Fantastically ordered prompts and where to find them: Overcoming few-shot prompt order sensitivity.  In _Proceedings of the 60th Annual Meeting of the Association for Computational Linguistics (Volume 1: Long Papers)_ , pages 8086–8098.
  * Nan et al. (2023) Linyong Nan, Yilun Zhao, Weijin Zou, Narutatsu Ri, Jaesung Tae, Ellen Zhang, Arman Cohan, and Dragomir Radev. 2023.  Enhancing text-to-sql capabilities of large language models: A study on prompt design strategies.  In _Findings of the Association for Computational Linguistics: EMNLP 2023_ , pages 14935–14956.
  * Nori et al. (2023) Harsha Nori, Yin Tat Lee, Sheng Zhang, Dean Carignan, Richard Edgar, Nicolo Fusi, Nicholas King, Jonathan Larson, Yuanzhi Li, Weishung Liu, et al. 2023.  Can generalist foundation models outcompete special-purpose tuning? case study in medicine.  _arXiv preprint arXiv:2311.16452_.
  * Pezeshkpour and Hruschka (2023) Pouya Pezeshkpour and Estevam Hruschka. 2023.  Large language models sensitivity to the order of options in multiple-choice questions.  _arXiv preprint arXiv:2308.11483_.
  * Pourreza and Rafiei (2023a) Mohammadreza Pourreza and Davood Rafiei. 2023a.  Din-sql: Decomposed in-context learning of text-to-sql with self-correction.  _arXiv preprint arXiv:2304.11015_.
  * Pourreza and Rafiei (2023b) Mohammadreza Pourreza and Davood Rafiei. 2023b.  Evaluating cross-domain text-to-sql models and benchmarks.  In _Proceedings of the 2023 Conference on Empirical Methods in Natural Language Processing_ , pages 1601–1611.
  * Qi et al. (2022) Jiexing Qi, Jingyao Tang, Ziwei He, Xiangpeng Wan, Yu Cheng, Chenghu Zhou, Xinbing Wang, Quanshi Zhang, and Zhouhan Lin. 2022.  Rasat: Integrating relational structures into pretrained seq2seq model for text-to-sql.  In _Proceedings of the 2022 Conference on Empirical Methods in Natural Language Processing_ , pages 3215–3229.
  * Tai et al. (2023) Chang-Yu Tai, Ziru Chen, Tianshu Zhang, Xiang Deng, and Huan Sun. 2023.  Exploring chain of thought style prompting for text-to-SQL.  In _Proceedings of the 2023 Conference on Empirical Methods in Natural Language Processing_ , pages 5376–5393, Singapore. Association for Computational Linguistics.
  * Wang et al. (2023a) Bing Wang, Changyu Ren, Jian Yang, Xinnian Liang, Jiaqi Bai, Qian-Wen Zhang, Zhao Yan, and Zhoujun Li. 2023a.  Mac-sql: Multi-agent collaboration for text-to-sql.  _arXiv preprint arXiv:2312.11242_.
  * Wang et al. (2022) Xuezhi Wang, Jason Wei, Dale Schuurmans, Quoc Le, Ed Chi, Sharan Narang, Aakanksha Chowdhery, and Denny Zhou. 2022.  Self-consistency improves chain of thought reasoning in language models.  _arXiv preprint arXiv:2203.11171_.
  * Wang et al. (2023b) Yiwei Wang, Yujun Cai, Muhao Chen, Yuxuan Liang, and Bryan Hooi. 2023b.  Primacy effect of chatgpt.  In _Proceedings of the 2023 Conference on Empirical Methods in Natural Language Processing_ , pages 108–115.
  * Webson and Pavlick (2022) Albert Webson and Ellie Pavlick. 2022.  Do prompt-based models really understand the meaning of their prompts?  In _Proceedings of the 2022 Conference of the North American Chapter of the Association for Computational Linguistics: Human Language Technologies_ , pages 2300–2344.
  * Wei et al. (2022) Jason Wei, Xuezhi Wang, Dale Schuurmans, Maarten Bosma, Fei Xia, Ed Chi, Quoc V Le, Denny Zhou, et al. 2022.  Chain-of-thought prompting elicits reasoning in large language models.  _Advances in Neural Information Processing Systems_ , 35:24824–24837.
  * Wu et al. (2023) Zhiyong Wu, Yaoxiang Wang, Jiacheng Ye, and Lingpeng Kong. 2023.  Self-adaptive in-context learning: An information compression perspective for in-context example selection and ordering.  In _Proceedings of the 61st Annual Meeting of the Association for Computational Linguistics (Volume 1: Long Papers)_ , pages 1423–1436, Toronto, Canada. Association for Computational Linguistics.
  * Xu et al. (2022) Kuan Xu, Yongbo Wang, Yongliang Wang, Zihao Wang, Zujie Wen, and Yang Dong. 2022.  Sead: End-to-end text-to-sql generation with schema-aware denoising.  In _Findings of the Association for Computational Linguistics: NAACL 2022_ , pages 1845–1853.
  * Yu et al. (2018) Tao Yu, Rui Zhang, Kai Yang, Michihiro Yasunaga, Dongxu Wang, Zifan Li, James Ma, Irene Li, Qingning Yao, Shanelle Roman, et al. 2018.  Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task.  In _Proceedings of the 2018 Conference on Empirical Methods in Natural Language Processing_ , pages 3911–3921.
  * Zhao et al. (2021) Zihao Zhao, Eric Wallace, Shi Feng, Dan Klein, and Sameer Singh. 2021.  Calibrate before use: Improving few-shot performance of language models.  In _International Conference on Machine Learning_ , pages 12697–12706. PMLR.
  * Zheng et al. (2023) Chujie Zheng, Hao Zhou, Fandong Meng, Jie Zhou, and Minlie Huang. 2023.  Large language models are not robust multiple choice selectors.  _arXiv e-prints_ , pages arXiv–2309.
  * Zhou et al. (2022) Denny Zhou, Nathanael Schärli, Le Hou, Jason Wei, Nathan Scales, Xuezhi Wang, Dale Schuurmans, Claire Cui, Olivier Bousquet, Quoc Le, et al. 2022.  Least-to-most prompting enables complex reasoning in large language models.  _arXiv preprint arXiv:2205.10625_.

##  Appendix A Error Analysis

Figure 2:  Error analysis for the Spider and BIRD dev sets.

We performed an error analysis to gain a deeper understanding of the instances
in which the proposed method failed to accurately predict the SQL query. This
analysis was conducted on a random sample of 100 examples from both the Spider
and BIRD dev sets, where the execution results of the predicted query and gold
SQL differed. Failure cases were categorized into four distinct categories as
follows:

  * •

Incorrect Gold: The gold SQL query provided by human annotators in the dataset
was incorrect.

  * •

Semantically Correct: The predicted SQL query was semantically equivalent to
the gold SQL query, but the execution result differed owing to factors such as
ties in the output (Pourreza and Rafiei, 2023b), the order of columns in the
SELECT clause, or the inclusion of additional columns in the SELECT clause.

  * •

Schema Linking Error: The predicted SQL query referred to different tables or
columns than those referred to in the gold SQL query.

  * •

Other Errors: The predicted SQL contained errors other than schema linking.

Examples from each category are listed in Table 8.

The results of manually classifying the failure cases are depicted in Figure
2. The 62 and 73% failure cases in the BIRD and Spider benchmarks,
respectively, were those wherein the correct query was generated but they were
still considered failures owing to inaccuracies in the gold queries or
limitations of the method used to calculate the EX. When these cases were
excluded, the most prevalent error was schema linking, which involved
selecting incorrect tables or columns (20% for BIRD and 21% for Spider).
However, even for human experts, referencing the exact tables and columns as
the gold query is a challenging task because of inherent ambiguities. Such
ambiguities arise when multiple columns in a DB share the same semantic
meaning, or when the question does not explicitly specify the columns to be
included in the SELECT clause. Other errors, excluding schema linking,
accounted for only 6% and 18% of the errors for Spider and BIRD, respectively.
These errors encompassed cases in which the question or evidence was
misinterpreted, incorrect assumptions were made regarding the DB content, or a
syntactically correct SQL query could not be generated.

These results indicate that the EX of the proposed approach was significantly
underestimated for both datasets. Furthermore, this analysis underscores the
need for more precise gold queries and robust evaluation methodologies for
text-to-SQL tasks.

Incorrect Gold |
---|---
Question |  List out the code for drivers who have nationality in America.
Evidence |  nationality = ’America’
GOLD |  SELECT code FROM drivers WHERE Nationality = ’American’
PRED |  SELECT code FROM drivers WHERE nationality = ’America’
Question |  Which customer paid the most in 2012/8/25?
Evidence |  ’2012/8/25’ can be represented by ’2012-08-25’
GOLD |  SELECT CustomerID FROM transactions_1k WHERE Date = ’2012-08-25’ GROUP BY CustomerID ORDER BY SUM(Price) DESC LIMIT 1
PRED |  SELECT CustomerID FROM transactions_1k WHERE Date = ’2012-08-25’ GROUP BY CustomerID ORDER BY SUM(Amount * Price) DESC LIMIT 1
Semantically Correct |
Question |  What is the phone number and extension number for the school with the zip code 95203-3704? Indicate the school’s name.
GOLD |  SELECT Phone, Ext, School FROM schools WHERE Zip = ’95203-3704’
PRED |  SELECT School, Phone, Ext FROM schools WHERE Zip = ’95203-3704’
Question |  List the card names with value that cost more converted mana for the face.
Evidence |  more converted mana for the face refers to Max(faceConvertedManaCost);
GOLD |  SELECT name FROM cards ORDER BY faceConvertedManaCost LIMIT 1
PRED |  SELECT name FROM cards WHERE faceConvertedManaCost = ( SELECT MAX(faceConvertedManaCost) FROM cards )
Schema Linking Error |
Question |  Which constructor has the highest point?
GOLD |  SELECT T2.name FROM constructorStandings AS T1 INNER JOIN constructors AS T2 on T1.constructorId = T2.constructorId ORDER BY T1.points DESC LIMIT 1
PRED |  SELECT T2.name FROM constructorResults AS T1 INNER JOIN constructors AS T2 ON T1.constructorId = T2.constructorId ORDER BY T1.points DESC LIMIT 1
Question |  List all the mythic rarity print cards banned in gladiator format.
Evidence |  mythic rarity printing refers to rarity = ’mythic’; card banned refers to status = ’Banned’; in gladiator format refers to format = ’gladiator’;
GOLD |  SELECT DISTINCT T1.id FROM cards AS T1 INNER JOIN legalities AS T2 ON T1.uuid = T2.uuid WHERE T2.format = ’gladiator’ AND T2.status = ’Banned’ AND T1.rarity = ’mythic’
PRED |  SELECT DISTINCT cards.name FROM cards INNER JOIN legalities ON cards.uuid = legalities.uuid WHERE legalities.format = ’gladiator’ AND legalities.status = ’Banned’ AND cards.rarity = ’mythic’
Other Errors |
Question |  What is the lowest grade for the District Special Education Consortia School with National Center for Educational Statistics school district identification number of 613360?
Evidence |  District Special Education Consortia School refers to EdOpsCode = ’SPECON’.
GOLD |  SELECT MIN(T1.‘Low Grade‘) FROM frpm AS T1 INNER JOIN schools AS T2 ON T1.CDSCode = T2.CDSCode WHERE T2.NCESDist = 613360 AND T2.EdOpsCode = ’SPECON’
PRED |  SELECT frpm.‘Low Grade‘ FROM frpm INNER JOIN schools ON frpm.CDSCode = schools.CDSCode WHERE schools.EdOpsCode = ’SPECON’ AND schools.NCESDist = ’613360’
Question |  What type of promotion is of card ’Duress’?
Evidence |  card Duress refers to name = ’Duress’; type of promotion refers to promoTypes;
GOLD |  SELECT promoTypes FROM cards WHERE name = ’Duress’ AND promoTypes IS NOT NULL
PRED |  SELECT promoTypes FROM cards WHERE name = ’Duress’
Table 8: Examples of each error case in the BIRD dev set.

##  Appendix B Prompts

###  B.1 Prompt for Schema Linking

####  B.1.1 Prompt for Table Linking

⬇

### Given a database schema, question, and knowledge evidence, extract a list
of tables that should be referenced to convert the question into SQL.

### SQLite SQL tables, with their properties:

# molecule ( molecule_id, label )

# connected ( atom_id, atom_id2, bond_id )

# bond ( bond_id, molecule_id, bond_type )

# atom ( atom_id, molecule_id, element )

#

# atom.molecule_id = molecule.molecule_id

# bond.molecule_id = molecule.molecule_id

# connected.bond_id = bond.bond_id

# connected.atom_id2 = atom.atom_id

# connected.atom_id = atom.atom_id

### Question: Among all chemical compounds identified in the database, what
percent of compounds form a triple-bond.

### Knowledge Evidence: triple bond refers to bond_type = ’#’;

You need to not only select the required tables, but also explain in detail
why each table is needed.

Your answer should strictly follow the following json format.

{

"reasoning": "", // The reason for choosing each table.

"tables": [], // List of selected tables.

}

### Your Answer:

####  B.1.2 Prompt for Column Linking

⬇

### Given a database schema, question, and knowledge evidence, extract a list
of columns that should be referenced to convert the question into SQL.

### SQLite SQL tables, with their properties:

# molecule ( molecule_id, label )

# bond ( bond_id, molecule_id, bond_type )

#

# bond.molecule_id = molecule.molecule_id

### Question: Among all chemical compounds identified in the database, what
percent of compounds form a triple-bond.

### Knowledge Evidence: triple bond refers to bond_type = ’#’;

You need to not only select the required columns, but also explain in detail
why each column is needed.

Your answer should strictly follow the following json format.

{{

"reasoning": "", // The reason for choosing each column.

"columns": ["table_name_i.column_name_j", ...], // List of selected columns

}}

### Your Answer:

###  B.2 Prompt for Question Masking

⬇

### Given a DB schema and a question, mask the table name, column name, and
values in the question.

<example1>

### SQLite SQL tables, with their properties:

# customers ( CustomerID: integer, Segment: text, Currency: text )

# gasstations ( GasStationID: integer, ChainID: integer, Country: text,
Segment: text )

# products ( ProductID: integer, Description: text )

# transactions_1k ( TransactionID: integer, Date: date, Time: text,
CustomerID: integer, CardID: integer, GasStationID: integer, ProductID:
integer, Amount: integer, Price: real )

# yearmonth ( CustomerID: integer, Date: text, Consumption: real )

### Question: For all the people who paid more than 29.00 per unit of product
id No.5. Give their consumption status in the August of 2012.

### Masked Question: For all the [TABLE] who paid more than [VALUE] per unit
of [COLUMN] [VALUE]. Give their consumption status in the [VALUE].

</example1>

<example2>

### SQLite SQL tables, with their properties:

# customers ( CustomerID: integer, Segment: text, Currency: text )

# gasstations ( GasStationID: integer, ChainID: integer, Country: text,
Segment: text )

# products ( ProductID: integer, Description: text )

# transactions_1k ( TransactionID: integer, Date: date, Time: text,
CustomerID: integer, CardID: integer, GasStationID: integer, ProductID:
integer, Amount: integer, Price: real )

# yearmonth ( CustomerID: integer, Date: text, Consumption: real )

### Question: How much did customer 6 consume in total between August and
November 2013?

### Masked Question: How much did [TABLE] [VALUE] consume in total between
[VALUE] and [VALUE]?

</example2>

<example3>

### SQLite SQL tables, with their properties:

# drivers ( driverId: integer, driverRef: text, number: integer, code: text,
forename: text, surname: text, dob: date, nationality: text, url: text )

### Question: How many Australian drivers who were born in 1980?

### Masked Question: How many [VALUE] [TABLE] who were born in [VALUE]?

</example3>

### SQLite SQL tables, with their properties:

# molecule ( molecule_id, label )

# bond ( bond_id, molecule_id, bond_type )

#

# bond.molecule_id = molecule.molecule_id

### Question: Among all chemical compounds identified in the database, what
percent of compounds form a triple-bond.

### Knowledge Evidence: triple bond refers to bond_type = ’#’;

### Masked Question:

Listing 4: An example of a full prompt for question masking.

###  B.3 Prompt for SQL Generation

⬇

### Given a database schema, question, and knowledge evidence, generate the
correct sqlite SQL query for the question.

<examples>

# Question: Among all the customers, what is the percentage of the customer’s
nation being Germany?

# Knowledge Evidence: DIVIDE(COUNT(c_custkey when n_name = ’GERMANY’),
COUNT(c_custkey)) as percentage;

# Gold SQL: SELECT CAST(SUM(IIF(T2.n_name = ’GERMANY’, 1, 0)) AS REAL) * 100 /
COUNT(T1.c_custkey) FROM customer AS T1 INNER JOIN nation AS T2 ON
T1.c_nationkey = T2.n_nationkey

# Question: Among the schools whose donators are teachers, what is the
percentage of schools that are in Brooklyn?

# Knowledge Evidence: donors are teachers refers to is_teacher_acct = ’t’;
Brooklyn is school_city; percentage =
Divide(Count(school_city-’Brooklyn’),Count(school_city))*100

# Gold SQL: SELECT CAST(SUM(CASE WHEN T1.school_city LIKE ’Brooklyn’ THEN 1
ELSE 0 END) AS REAL) * 100 / COUNT(T1.teacher_acctid) FROM projects AS T1
INNER JOIN donations AS T2 ON T1.projectid = T2.projectid WHERE
T2.is_teacher_acct = ’t’

...

</examples>

### SQLite SQL tables, with their properties:

# molecule ( molecule_id, label )

# bond ( bond_id, molecule_id, bond_type )

#

# bond.molecule_id = molecule.molecule_id

### The type and description of each column:

# [molecule]

- molecule_id (text): unique id of molecule

- label (text): whether this molecule is carcinogenic or not

# [bond]

- bond_id (text): unique id representing bonds

- molecule_id (text): identifying the molecule in which the bond appears

- bond_type (text): type of the bond

### Sample rows of each table in csv format:

# [molecule]

molecule_id,label

TR000,+

TR001,+

TR002,-

# [bond]

bond_id,molecule_id,bond_type

TR000_1_2,TR000,-

TR000_2_3,TR000,-

TR000_2_4,TR000,-

### Question: Among all chemical compounds identified in the database, what
percent of compounds form a triple-bond.

### Knowledge Evidence: triple bond refers to bond_type = ’#’;

You need to not only create the SQL, but also provide the detailed reasoning
steps required to create the SQL. Your answer should strictly follow the
following json format:

{

"reasoning": "", // The reasoning steps for generating SQL.

"sql": "", // The final generated SQL.

}

### Your Answer:

###  B.4 Prompt for SQL Selection

⬇

### When a DB schema, a question, and a knowledge evidence are given, and up
to three SQLite queries expressing the question are given, please choose the
most accurate SQL based on the Checklist.

<examples>

# Question: Among all the customers, what is the percentage of the customer’s
nation being Germany?

# Knowledge Evidence: DIVIDE(COUNT(c_custkey when n_name = ’GERMANY’),
COUNT(c_custkey)) as percentage;

# Gold SQL: SELECT CAST(SUM(IIF(T2.n_name = ’GERMANY’, 1, 0)) AS REAL) * 100 /
COUNT(T1.c_custkey) FROM customer AS T1 INNER JOIN nation AS T2 ON
T1.c_nationkey = T2.n_nationkey

# Question: Among the schools whose donators are teachers, what is the
percentage of schools that are in Brooklyn?

# Knowledge Evidence: donors are teachers refers to is_teacher_acct = ’t’;
Brooklyn is school_city; percentage =
Divide(Count(school_city-’Brooklyn’),Count(school_city))*100

# Gold SQL: SELECT CAST(SUM(CASE WHEN T1.school_city LIKE ’Brooklyn’ THEN 1
ELSE 0 END) AS REAL) * 100 / COUNT(T1.teacher_acctid) FROM projects AS T1
INNER JOIN donations AS T2 ON T1.projectid = T2.projectid WHERE
T2.is_teacher_acct = ’t’

...

</examples>

### SQLite SQL tables, with their properties:

# molecule ( molecule_id, label )

# bond ( bond_id, molecule_id, bond_type )

#

# bond.molecule_id = molecule.molecule_id

### The type and description of each column:

# [molecule]

- molecule_id (text): unique id of molecule

- label (text): whether this molecule is carcinogenic or not

# [bond]

- bond_id (text): unique id representing bonds

- molecule_id (text): identifying the molecule in which the bond appears

- bond_type (text): type of the bond

### Sample rows of each table in csv format:

# [molecule]

molecule_id,label

TR000,+

TR001,+

TR002,-

# [bond]

bond_id,molecule_id,bond_type

TR000_1_2,TR000,-

TR000_2_3,TR000,-

TR000_2_4,TR000,-

### Question: Among all chemical compounds identified in the database, what
percent of compounds form a triple-bond.

### Knowledge Evidence: triple bond refers to bond_type = ’#’;

### Candidate SQLs:

1. SELECT CAST(COUNT(CASE WHEN bond_type = ’#’ THEN 1 ELSE NULL END) AS REAL) * 100 / COUNT(*) FROM bond

2. SELECT CAST(COUNT(DISTINCT CASE WHEN bond_type = ’#’ THEN molecule_id ELSE NULL END) AS REAL) * 100 / COUNT(DISTINCT molecule_id) FROM bond

### Checklist:

1. The SQL should accurately represent the question.

2. The SQL should accurately use the given knowledge evidence.

3. The SELECT clause should not include any additional columns that are not included in the question.

4. The order of columns in the SELECT clause must be the same as the order in the question.

5. Check if the operations are being performed correctly according to the column type.

### Instruction:

- If the first SQL satisfies all the conditions of the checklist, please choose the first SQL. If not, move on to the next SQL.

- If there’s no SQL that satisfies all the requirements on the checklist, just choose the first SQL.

- Provide a detailed step-by-step explanation following the order of the checklist when checking whether each SQL satisfies the checklist.

- Your answer should strictly follow the following json format.

{{

"reasoning": "", // The reasoning steps for choosing the best SQL.

"sql": "", // The final chosen SQL.

}}

### Your Answer: