Tpcds Structural Eval

tpcds-structural-eval

Evaluating LLMs for Text-to-SQL Generation With Complex SQL Workload — Limin Ma, Ken Pu, Ying Zhu (arXiv:2407.19517, 2024)

What this evaluates

Evaluates an LLM's ability to generate structurally complex SQL queries for real-world decision-making workloads. It probes the model's capacity to handle deep nesting, multiple joins, diverse column references, and complex filtering conditions compared to simpler benchmarks.

Datasets

• TPC-DS — total ?; splits: test (-1)

Metrics

• structural_similarity (primary) — range: [0, 1]
  • Average of per-feature similarity scores (Tables, Columns, Where Predicates, Constants, Functions, Aggregation, Joins) computed between generated and gold SQL queries using fuzzy structure matching.
• distinct_column_count — range: other
  • Number of distinct columns referenced in SELECT, JOIN, or WHERE clauses.
• join_count — range: other
  • Number of JOIN operations in the SQL query.
• where_predicate_count — range: other
  • Number of basic predicates in the WHERE clause. Compound predicates are counted as multiple basic predicates.
• cte_count — range: other
  • Number of Common Table Expressions (CTE) in the query.
• function_call_count — range: other
  • Number of expressions containing scalar or aggregation function calls.
• subquery_count — range: other
  • Number of nested subqueries in the query.

Input / output format

Input: Natural language question and database schema definition.

Output: A single SQL query string.

Scoring recipe

def compute_structural_similarity(gen_sql, gold_sql):
    features = ['Tables', 'Columns', 'Where Predicates', 'Constants', 'Functions', 'Aggregation', 'Joins']
    sims = []
    for f in features:
        g = count_feature(gen_sql, f)
        gd = count_feature(gold_sql, f)
        sims.append(min(g, gd) / max(g, gd) if max(g, gd) > 0 else 1.0)
    return sum(sims) / len(sims)

Common pitfalls

• Confusing structural fidelity with execution accuracy; a query can match gold structure but fail to execute correctly.
• Failing to account for the massive scale difference in JOINs and subqueries between TPC-DS and simpler benchmarks like Spider/BIRD.
• Not normalizing complexity metrics by dataset mean when comparing cross-benchmark distributions.

Evidence (verbatim from paper)

We also count the number of distinct columns referenced in SQL queries. The number of distinct columns involved in a query is an indicator of the semantic complexity of that query. A column is included in the count whether it appears in SELECT projections, in JOIN conditions, or in WHERE predicates.

Citation

@misc{ma2024evaluatingllms,
  title={Evaluating LLMs for Text-to-SQL Generation With Complex SQL Workload},
  author={Limin Ma, Ken Pu, Ying Zhu},
  year={2024},
  note={arXiv:2407.19517}
}

• arXiv: 2407.19517