Beyond Output Accuracy: An Empirical Evaluation of Failure Modes in Retrieval-Augmented Text-to-SQL

Large language models are increasingly used to translate natural-language analytics questions into SQL, with retrieval-augmented generation (RAG) providing database schema information to ground generation. Yet successful execution or even a factually correct final answer does not necessarily mean that the generated SQL faithfully represents the user’s analytical intent. Errors in schema linking, filtering, temporal interpretation, or aggregation can produce plausible outputs that are difficult to detect from the answer alone. We investigate the failure modes that remain in retrieval-augmented Text-to-SQL systems and ask whether output-level correctness can conceal errors in the underlying query logic. We evaluate a locally deployed RAG-assisted Text-to-SQL system over a structured healthcare analytics database. For each natural-language question, the system retrieves relevant schema metadata, including table definitions, column names and types, and representative filter values, and supplies this context to a locally served Llama-family open-weight model through a structured SQL-generation prompt. Generated SQL is validated and executed, with execution failures eligible for a self-correction attempt using the database error as additional context. We construct a 50-query golden set spanning simple lookups (n=15), aggregation queries (n=15), comparison queries (n=10), and deliberately ambiguous questions (n=10). Ground truth is established using independently written SQL. Each response is evaluated along four dimensions: factual correctness of the returned result, logical correctness of the generated SQL relative to user intent, hallucination mechanism, and confidence calibration. This evaluation design explicitly distinguishes obtaining the correct output from generating the correct query logic. Performance varied substantially across query types: 13/15 simple lookups, 10/15 aggregation queries, and 7/10 comparison queries produced correct results, while 6/10 ambiguous questions produced correct or appropriately hedged responses. Observed failures included schema hallucination, incorrect filter values, aggregation errors, and confident responses to underspecified questions. Most notably, we identified three cases in which the system returned a factually correct answer despite generating logically incorrect SQL. In these cases, characteristics of the underlying data happened to mask the query-generation error, meaning that evaluation based only on final-answer correctness would have classified an unreliable query as successful. These findings show that retrieval grounding does not eliminate semantically plausible failures in Text-to-SQL systems and that final-answer accuracy alone can overestimate reliability. We contribute an evaluation framework that separates output correctness from query-logic correctness and a failure taxonomy for diagnosing errors in retrieval-augmented analytics systems. The results motivate evaluation practices that inspect not only whether an LLM-generated answer is correct, but whether the generated query faithfully represents the analytical question it is intended to answer.