Din SQL Eval

din-sql-eval

DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction — Pourreza et al. (2023) (arXiv:2304.11015, 2023)

What this evaluates

Evaluates a model's ability to generate syntactically and semantically correct SQL queries from natural language questions across diverse database schemas. It probes schema linking, handling of complex query structures (joins, nested subqueries, aggregations), and the capacity for iterative self-correction when initial generations fail.

Datasets

• Spider — total 10181; splits: train (8659), dev (1034), test (2147)

Metrics

• Execution Accuracy (EX) (primary) — range: percent
  • Compares the execution output of the predicted SQL query with that of the ground truth SQL query on database instances. A prediction is correct if the result sets match, allowing for multiple valid SQL formulations.
• Exact-Set-Match Accuracy (EM) — range: percent
  • Treats each SQL clause as a set and compares the prediction to the reference query. A query is correct only if all components match exactly; it does not consider actual values or execution results.

Input / output format

Input: Natural language question, database schema (table and column names), and few-shot demonstrations. For BIRD, prompts also include sample rows from each table and concatenated external knowledge hints.

Output: A single SQL query string.

Scoring recipe

def compute_ex(predictions, golds, db_instances):
    correct = 0
    for pred_sql, gold_sql in zip(predictions, golds):
        pred_res = execute_sql(pred_sql, db_instances)
        gold_res = execute_sql(gold_sql, db_instances)
        if set(pred_res) == set(gold_res):
            correct += 1
    return (correct / len(predictions)) * 100

Common pitfalls

• Execution accuracy (EX) permits multiple syntactically different SQL queries to be marked correct as long as they produce identical result sets on the database instances.
• Schema linking modules can introduce redundant joins or output columns when questions or schemas are ambiguous, artificially lowering accuracy if not handled carefully.
• Exact-match (EM) ignores actual data values and only checks clause structure, making it less robust than EX for evaluating functional correctness.

Evidence (verbatim from paper)

The exact-set-match accuracy (EM) treats each clause as a set and compares the prediction for each clause to its corresponding clause in the reference query. A predicted SQL query is considered correct only if all of its components match the ground truth. This metric does not take values into account. The execution accuracy (EX) compares the execution output of the predicted SQL query with that of the ground truth SQL query on some database instances. Execution accuracy provides a more precise estimate of the model's performance since there may be multiple valid SQL queries for a given question, and exact set match accuracy only evaluates the predicted SQL against one of them.

Citation

@misc{pourreza2023dinsql,
  title={DIN-SQL: Decomposed In-Context Learning of Text-to-SQL with Self-Correction},
  author={Pourreza et al. (2023)},
  year={2023},
  note={arXiv:2304.11015}
}

• arXiv: 2304.11015