How well do LLMs turn natural-language questions into correct SQL — and where exactly do they break? This project benchmarks proprietary and open-weight models on Text-to-SQL generation across a graded set of query difficulty patterns, multiple database schemas, and (for the harder cases) full reasoning traces, not just final answers.
Full solution / data: 362916-SoluzioneText2SQL.xlsx
Most Text-to-SQL evaluations report a single accuracy number. That hides where models fail: is it the join logic, the aggregation, or just a missing DISTINCT? This benchmark instead grades queries against a fixed taxonomy of SQL difficulty patterns, runs the same questions across models of very different scale (from 2B open-weight to frontier proprietary), and — for the hardest queries — captures each model's full chain-of-thought so failures can be traced back to a specific reasoning misstep rather than just the wrong output.
GPT · Gemini · Codestral, tested on a library-management schema (LIBRO, AUTORE, LETTORE, PRESTITO_LIBRO, SALA, DIPENDENTE, PRENOTAZIONE_SALA) across 7 difficulty patterns:
| Pattern | Tests |
|---|---|
| 0 — Projection | Basic filtering, sorting, pattern matching |
| 1 — Join | Multi-table joins with combined filters |
| 2 — Group + aggregate | GROUP BY / HAVING with conditions |
| 3 — Equivalent rewrites | Same query forced through JOIN, IN, and INTERSECT |
| 4 — Division | "For all" logic (NOT EXISTS) |
| 5 — Set operations | EXCEPT vs NOT IN equivalence |
| 6 — CTE vs correlation | Multi-step aggregation via WITH vs correlated subqueries |
The identical 7 patterns and schema, run against Qwen 4B, Qwen 8B, and Phi-4-mini-reasoning, to see how much of the gap to proprietary models is closable at 4–8B scale.
Ten harder queries spanning four different schemas (book sales/royalties, school consultations, university scheduling, airline booking), each evaluated with:
- The model's full reasoning trace, not just the final SQL
- A structured critique of the reasoning (missing steps, wrong steps, redundant steps)
- A structured critique of the resulting SQL
- A refinement round: a follow-up correction prompt, and the model's updated reasoning + query
- Proprietary models (Part 1): ChatGPT was the most consistently reliable — clean, readable queries following the requested SQL pattern. Gemini often handled edge cases better (adding
EXISTSchecks ChatGPT missed) but was less literal about following the exact technique requested. Codestral was essential and correct on simple queries but broke down on multi-step aggregation, sometimes ignoring the requested SQL construct entirely. - Open models <8B (Part 2): Qwen 8B was the most reliable of the three — correct
GROUP BYlogic, low ambiguity. Qwen 4B did well on simple filters/joins but degraded on queries requiring specific technical constructs (INTERSECT, CTEs). Phi-4-mini-reasoning was the weakest by a wide margin, frequently inventing tables/columns that don't exist in the schema and returning non-executable output. - Overall ranking across all 6 models: GPT and Qwen 8B were the most dependable; Gemini and Qwen 4B correct but more superficial; Codestral less stable; Phi-4-mini the worst performer.
- Reasoning ≠ correctness. Several models produced reasoning that correctly identified the right approach but then implemented it incorrectly (e.g. grouping by the wrong column, or declaring an approach like
INTERSECT/EXCEPTand then not actually using it in the final query) — a mismatch between stated plan and executed SQL that a "final answer only" benchmark would miss entirely. - Ambiguity is the main error driver. Prompts with explicit technique constraints ("solve using JOIN, not IN") and division-style "for all" logic caused the most failures — models often got the logic right but violated the technique constraint, or confused related metrics (e.g. max of an average vs. max of a raw value).
Proprietary: GPT · Gemini · Codestral · Open-weight: Qwen 4B/8B · Phi-4-mini-reasoning (MLX)
Part of my AI/ML portfolio — see profile README for other projects.