# Refining Zero-Shot Text-to-SQL Benchmarks via Prompt Strategies with Large Language Models
## Appendix A Examples of Errors and Inconsistencies in the Original BIRD Dataset
### Summary of Identified Error Types in Original BIRD Dataset
**Original Answer**  
SELECT (SUM(IIF(T2.raceId = 853, T2.fastestLapSpeed, 0)) - SUM(IIF(T2.raceId = 854, T2.fastestLapSpeed, 0))) * 100 /  
SUM(IIF(T2.raceId = 853, T2.fastestLapSpeed, 0)) FROM drivers AS T1 INNER JOIN results  
AS T2 ON T2.driverId = T1.driverId WHERE  
T1.forename = '[PERSON]' AND T1.surname = '[PERSON]'  
**Error Analysis**  
The SQL calculates the percentage increase of the sum of fastest lap speeds (highlighted in question), which may not align with the question's intent. \"The fastest lap speed\" typically refers to the single fastest lap per race, suggesting the query should compare individual lap speeds, not their totals, leading to potential inconsistency. **Question 198**  
On average how many carcinogenic molecules are single bonded? **Original Answer**  
SELECT AVG(single_bond_count) FROM (SELECT  
T3.molecule_id, COUNT(T1.bond_type) AS  
single_bond_count FROM bond AS T1 INNER  
JOIN atom AS T2 ON T1.molecule_id =  
T2.molecule_id INNER JOIN molecule AS T3  
ON T3.molecule_id = T2.molecule_id WHERE  
T1.bond_type = '-' AND T3.label = '+' GROUP  
BY T3.molecule_id) AS subquery  
**Error Analysis**  
The phrase \"on average\" creates ambiguity, and the question's target--\"how many carcinogenic molecules\"--suggests counting molecules, yet the SQL computes the average number of single bonds per carcinogenic molecule (highlighted in original answer). The intended question likely should be \"On average, how many single bonds are there in carcinogenic molecules?\" revealing a mismatch due to unclear terms. **Figure A5. Example of an ambiguously defined question from the BIRD dataset (Question 198). The question’s phrasing suggests counting single-bonded carcinogenic molecules, but the SQL calculates the average number of single bonds per molecule. This discrepancy, noted in the analysis, stems from vague terms, leading to misaligned SQL output. **  
**Question 299**  
Which toxic element can be found in the molecule TR151? **Question 302**  
Name the elements that comprise the atoms of bond TR001\(2\)4. **Question 319**  
How many elements are contained in bond_id TR001\(3\)4? **Remark**  
The purpose of all of these questions is to test whether the LLM recognizes the need for a \"DISTINCT\" when referring to \"elements\" since each element can only be counted once. Questions 268, 277, 281, 285, 290, 302, 304, 306, 307, 319, 322, and 328 all address this process, which causes redundancies. **Question 322 (Labeled as 299)**  
What is the element of toxicology for the atom with the ID of TR000_1? **Original Answer**  
SELECT T.element FROM atom AST WHERE  
T.atom_id = 'TR000_1'  
**Remark**  
The question is labeled \"challenging\" but not difficult in any way. **Question 416**  
What percentage of cards without power are in French? **Original Answer**  
SELECT CAST(SUM(CASE WHEN T2.language =  
'French' THEN 1 ELSE 0 END) AS REAL) * 100  
/ COUNT(T1.id) FROM cards AS T1 INNER JOIN  
foreign_data AS T2 ON T1.uuid = T2.uuid  
WHERE T1.power IS NULL OR T1.power LIKE  
'**Error Analysis**  
In Magic: The Gathering (MTG), a power value of '*' is not considered \"without power\" (highlighted in question); it indicates variable power. The SQL's condition (highlighted in original answer) incorrectly includes such cards, showing the dataset developers made a false domain-specific assumption. **Figure A6. Examples of redundant questions from the BIRD dataset (Questions 299, 302, 319). These questions repeatedly test the LLM’s understanding of 'element' and the need for DISTINCT to avoid duplicate counting. The remark notes that at least 12 questions focus on this single concept, leading to unbalanced data that overemphasize one reasoning aspect, reducing evaluation persuasiveness. **Question 322 (Labeled as 299)**  
What is the element of toxicology for the atom with the ID of TR000_1? **Original Answer**  
SELECT T.element FROM atom AST WHERE  
T.atom_id = 'TR000_1'  
**Remark**  
The question is labeled \"challenging\" but not difficult in any way.