🤖 AI

Benchmarking Text-to-Python against Text-to-SQL: The Impact of Explicit Logic and Ambiguity

This paper introduces the BIRD-Python benchmark and the Logic Completion Framework to demonstrate that Text-to-Python can achieve performance parity with Text-to-SQL once systems effectively resolve ambiguity by incorporating latent domain knowledge into the code generation process.

Original authors: Hangle Hu, Chenyu Hou, Bin Cao, Ruizhe Li

Published 2026-01-26

📖 4 min read☕ Coffee break read

CC BY 4.0

Original authors: Hangle Hu, Chenyu Hou, Bin Cao, Ruizhe Li

Original paper licensed under CC BY 4.0 (http://creativecommons.org/licenses/by/4.0/). ✨ This is an AI-generated explanation of the paper below. It is not written or endorsed by the authors. For technical accuracy, refer to the original paper. Read full disclaimer

Imagine you are trying to give instructions to two different types of assistants to get information from a messy pile of files.

The Old Way: The "Magic Box" (Text-to-SQL)
For a long time, we've used a system called Text-to-SQL. Think of this like talking to a very strict, magical librarian who only works with a specific, organized filing cabinet (a database).

How it works: You say, "Find me all the red books from 2020."
The Magic: The librarian knows the rules of the cabinet. If you don't say what to do with "missing" books, the librarian automatically hides them. If you don't say how to sort them, the librarian sorts them alphabetically by default. You don't have to explain how to find the books; you just say what you want.
The Problem: This only works if your data is already inside that specific filing cabinet. If your data is in loose papers, Excel sheets, or PDFs, the librarian can't help you.

The New Way: The "Robot Intern" (Text-to-Python)
Because real-world data is often messy and scattered, researchers wanted to use Text-to-Python. This is like hiring a smart robot intern who can read any file format and use powerful tools (like a calculator or a spreadsheet program) to do the work.

How it works: You say, "Find me all the red books from 2020."
The Reality: The robot doesn't have a magical filing cabinet. It has to be told exactly what to do. "Open the file. Look for the year column. Check if it's 2020. Check if the color is red. Oh, and what if a row has no year? Do you skip it? Do you count it? How do you sort the final list?"
The Issue: Because the robot needs every single step spelled out, it gets confused easily if you leave anything unsaid. If you don't specify how to handle missing data, the robot might crash or give a wrong answer.

The Big Experiment
The authors of this paper wanted to see: Can this Robot Intern do the same job as the Magic Librarian, but with more flexibility?

To test this, they took a famous test designed for the Librarian (called BIRD) and rewrote it for the Robot.

Cleaning the Test: They found that the original test questions had some "noise" (mistakes in the answers). They fixed these so the test was fair.
Translating the Rules: They took the "Magic" rules the Librarian followed automatically and wrote them down as explicit instructions for the Robot.

What They Found

The Gap: At first, the Robot (Python) seemed worse than the Librarian (SQL). Smaller, less smart models struggled because they couldn't figure out all the hidden steps.
The Real Culprit: However, when they looked closer, they realized the Robot wasn't "dumb." The problem was that the questions were vague. The questions assumed the Robot knew things it didn't (like "how to handle missing numbers").
The Fix (The "Logic Completion Framework"): The authors built a helper system. Before the Robot tries to write the code, this helper asks: "Wait, what do you mean by 'missing numbers'? Should we ignore them or count them as zero?" Once the Robot gets this clear answer, it performs just as well as the Librarian.

The Bottom Line
The paper concludes that Text-to-Python is just as good as Text-to-SQL, provided you stop treating the AI like a mind-reader.

SQL is like a magic box that fills in the blanks for you.
Python is like a brilliant but literal-minded assistant. It can do anything, but you must be very specific.

If you give the assistant clear, complete instructions (filling in the "logic gaps"), it can handle complex, messy data just as well as the traditional database systems. The paper proves that the limitation isn't the AI's ability to write code; it's our ability to ask clear questions.

Technical Summary: Benchmarking Text-to-Python against Text-to-SQL

Problem Statement

While Text-to-SQL is the dominant paradigm for database interaction, real-world analytics increasingly require the flexibility of general-purpose programming languages like Python to manage file-based data (e.g., CSV, Excel) and complex procedural workflows. Despite the theoretical advantages of Text-to-Python, its reliability in core data retrieval remains underexplored compared to the mature Text-to-SQL ecosystem. A critical gap exists in systematically evaluating whether Text-to-Python can effectively replace Text-to-SQL, particularly given the fundamental differences between SQL's declarative nature (relying on implicit DBMS behaviors) and Python's requirement for explicit procedural logic. This paper investigates whether the performance gap between the two paradigms stems from inherent limitations in code generation or from the sensitivity of Python to underspecified user intent and missing domain context.

Methodology

1. Benchmark Construction: BIRD-Python

To enable a fair cross-paradigm comparison, the authors constructed BIRD-Python, a benchmark derived from the BIRD dataset.

Data Transformation: Relational databases were converted into standalone file formats (CSV), and original SQL objectives were mapped to equivalent Python execution logic.
Dataset Purification: The authors identified significant annotation noise in the original BIRD dataset where valid model outputs were penalized due to reference SQL inaccuracies. They implemented a two-stage purification pipeline:
- Automated Verification: Consensus among three state-of-the-art LLMs (Qwen3-238B, DeepSeek-R1, Qwen-Max) verified 1,193 queries.
- Expert Review: Double-blind expert review corrected 259 logical inconsistencies and confirmed 82 original annotations.
SQL-to-Python Conversion: To ensure execution semantics aligned, the team reconstructed SQL logic into Python, explicitly implementing implicit DBMS behaviors (e.g., null handling, sort stability) and addressing type safety differences.
Evaluation Metric: Standard Execution Accuracy (EX) was supplemented with an LLM-based Semantic Validator ( $V_{sem}$ ). This validator determines semantic equivalence between predicted and ground truth results, mitigating false negatives caused by superficial discrepancies (e.g., column ordering, data type representation).

2. The Logic Completion Framework (LCF)

To isolate the impact of ambiguity from code generation capability, the authors proposed the Logic Completion Framework (LCF). This three-phase paradigm separates reasoning from information deficits:

Phase 1 (Logic Probing): The target model (Subject) analyzes the input (Schema, Question, External Knowledge) to identify ambiguities and generates a clarifying inquiry ( $Q_{ambiguity}$ ) without generating code.
Phase 2 (Ground Truth Injection): An Oracle model (simulating a domain expert with access to ground truth) translates the gold logic into natural language hints ( $C_{logic}$ ). This step supplements latent domain knowledge without leaking code syntax.
Phase 3 (Execution): The Subject generates the final program conditioned on the augmented context ( $S, Q, K, C_{logic}$ ).

Key Contributions

BIRD-Python Benchmark: The first dedicated benchmark for evaluating Text-to-Python in file-based data querying, enabling detailed comparative analysis with Text-to-SQL.
Paradigm Divergence Insight: The study uncovers that the primary bottleneck in flexible analytics is not the choice of paradigm (SQL vs. Python) but the need for explicit logic formulation. Python's requirement for step-by-step procedural definitions makes it significantly more sensitive to missing domain context and ambiguous user intent than SQL.
Logic Completion Framework (LCF): A practical methodology that explicitly supplements latent domain knowledge. The framework demonstrates that when ambiguity is resolved, Text-to-Python achieves performance parity with Text-to-SQL.

Experimental Results

The authors evaluated various open-source and closed-source LLMs (including Qwen, DeepSeek, and StarCoder families) on both the original and refined BIRD datasets.

Performance Gap: Smaller models exhibited a significant performance drop when switching from SQL to Python (e.g., Qwen2.5-Coder 7B dropped from 52.93% to 35.95%). This validates the hypothesis that explicit procedural logic creates a higher reasoning barrier for smaller models.
Model Scaling: Larger and reasoning-enhanced models (e.g., Qwen3-Max, DeepSeek-R1) showed smaller performance gaps. Qwen3-Max achieved 63.43% accuracy in Python, comparable to its SQL performance.
Impact of Data Quality: Models performed significantly better on the "Verified" (cleaned) dataset compared to the original, indicating that annotation noise in the original BIRD dataset had artificially suppressed scores.
Error Analysis:
- Shared Errors: Both paradigms suffered most from Filter Condition Errors (~32%), driven by misunderstanding natural language intent.
- Paradigm-Specific Errors: Python generation exhibited a much higher rate of Logic Errors (17.5% vs. 0.3% in SQL), reflecting the difficulty of explicitly defining execution steps. SQL errors were more common in Row/Column Selection.
LCF Efficacy: Applying LCF significantly improved accuracy across all models. For Qwen3-7B, accuracy rose from 53.19% to 71.19% in Python. Crucially, under LCF, the performance gap between SQL and Python generation nearly vanished (e.g., Qwen3-32B achieved 72.49% for Code vs. 72.75% for SQL), proving that the performance deficit was due to information gaps, not inherent generation limitations.

Significance and Claims

The paper claims that Text-to-Python is a viable and flexible foundation for analytical agents, provided that systems can effectively ground ambiguous natural language inputs in executable logical specifications.

The authors argue that the perceived superiority of Text-to-SQL in current benchmarks is partly an artifact of SQL's ability to abstract away procedural details, masking the model's inability to resolve ambiguity. By shifting the focus from "code generation capability" to "contextual knowledge grounding," the study suggests that the path forward for natural language interfaces lies in supplementing models with adequate domain context (via frameworks like LCF) rather than relying solely on paradigm shifts. This establishes that with precise specification, Python can match SQL's core data retrieval capabilities while offering superior flexibility for complex, file-based analytical workflows.