Bird Eval

bird-eval

Enhancing LLM Fine-tuning for Text-to-SQLs by SQL Quality Measurement — Sarker et al. (2024) (arXiv:2410.01869, 2024)

What this evaluates

Evaluates an LLM's ability to generate syntactically correct and semantically accurate SQL queries from natural language questions over large, real-world databases. It probes database schema understanding, value matching, external knowledge incorporation, and query execution efficiency.

Datasets

• BIRD — total 12751; splits: test (-1)

Metrics

• Execution Accuracy (EX) (primary) — range: [0, 1]
  • The percentage of test cases where the model's generated SQL, when executed against the target database, returns a result set that exactly matches the gold SQL's result set.
• Valid Efficiency Score (VES) — range: [0, 1]
  • A metric that evaluates the execution efficiency of the generated SQL compared to the gold SQL, typically based on execution time or resource usage, ensuring the model does not just produce correct but highly inefficient queries.

Input / output format

Input: Natural language question paired with the target database's schema and relevant content/values.

Output: A single SQL query string.

Scoring recipe

def compute_ex(predictions, golds, databases):
    correct = 0
    for pred_sql, gold_sql, db in zip(predictions, golds, databases):
        pred_res = execute_sql(pred_sql, db)
        gold_res = execute_sql(gold_sql, db)
        if sets_match(pred_res, gold_res):
            correct += 1
    return correct / len(predictions)

def compute_ves(predictions, golds, databases):
    scores = []
    for pred_sql, gold_sql, db in zip(predictions, golds, databases):
        pred_time = get_execution_time(pred_sql, db)
        gold_time = get_execution_time(gold_sql, db)
        scores.append(min(1.0, gold_time / pred_time))
    return sum(scores) / len(scores)

Common pitfalls

• Models often fail to incorporate external knowledge or specific database values required to answer the question, leading to syntactically valid but semantically incorrect SQL.
• Execution Accuracy alone does not penalize inefficient queries, which is why Valid Efficiency Score (VES) is also tracked to ensure practical usability.
• Large database schemas can exceed context window limits, causing models to truncate or misreference table/column names during generation.

Evidence (verbatim from paper)

BIRD includes over 12,751 unique question-SQL pairs and 95 large databases with a total size of 33.4 GB. ... achieving competitive performance on the BIRD benchmark in terms of Execution Accuracy (EX) and Valid Efficiency Score (VES), outperforming SOTA models like GPT-4 and T5 without additional data preprocessing or multi-agent systems.

Citation

@misc{sarker2024enhancing,
  title={Enhancing LLM Fine-tuning for Text-to-SQLs by SQL Quality Measurement},
  author={Sarker et al. (2024)},
  year={2024},
  note={arXiv:2410.01869}
}

• arXiv: 2410.01869