Building a GenAI Text-to-SQL Assistant: When Valid SQL Gives the Wrong Answer

While exploring Generative AI, I wanted to build something practical rather than just experiment with prompts.

I developed a GenAI-powered Database Query Assistant that converts natural-language questions into PostgreSQL queries and executes them against a database.

Tech Stack

• Python + Streamlit for the application

• PostgreSQL as the database

• OpenAI models for natural-language-to-SQL generation

• CLI interface for rapid testing

GitHub: https://github.com/d33pankar/advanced\_genAI

During testing, I encountered an interesting problem:

The SQL was valid. The database executed it successfully. The answer was still wrong.

This post explains how I found the issue, what was happening inside the generated SQL, and what changed when I tested the same scenario with GPT-5.6.

---

1. The Architecture

The basic Text-to-SQL workflow is:

User question
      ↓
LLM
      ↓
PostgreSQL SQL
      ↓
Database
      ↓
Result

For example:

Which customers have spent more than $5,000 in total and have at least one shipment that is not delivered? Give customer details and expenditure.

The assistant needs to understand two things:

1. Calculate each customer's total spending.

2. Check whether that customer has at least one non-delivered shipment.

That sounds straightforward.

It wasn't.

---

2. Database Schema

For this experiment, I used a simple relational schema.

Customers

customers
-----------
customer_id
company_name
contact_name
country
signup_date

Sales

sales
-----------
sale_id
customer_id
employee_id
product_name
quantity
unit_price
sale_date

The sale amount is:

quantity * unit_price

Shipping

shipping
-----------
shipment_id
sale_id
carrier
shipping_cost
delivery_status
shipped_date
delivered_date

The relationship is:

Customer
   ↓
 Sales
   ↓
Shipping

A customer can have multiple sales, and a sale can have multiple shipping records.

The shipping statuses in the test data were:

Delivered
In Transit
Pending

---

3. The First Test

I initially tested the assistant with GPT-4o-mini.

I asked:

Which customers have spent more than $5,000 in total and have at least one shipment that is not delivered? Give customer details and expenditure.

The model generated SQL that PostgreSQL accepted and executed successfully.

The result was:

Gamma Inc    $9,000

No syntax error.

No runtime error.

But the result didn't make sense.

So instead of assuming the model was correct, I went back to the database.

---

4. Checking the Ground Truth

I calculated the actual lifetime spending for each customer:

Alpha Corp    $27,000
Beta LLC       $4,500
Gamma Inc     $27,000

I then checked which customers had non-delivered shipments:

Alpha Corp    Pending
Gamma Inc     In Transit

Therefore, the expected answer was:

Alpha Corp    $27,000
Gamma Inc     $27,000

So the question became:

Where did $9,000 come from?

---

5. Looking at the Generated SQL

The generated SQL was:

SELECT
    c.customer_id,
    c.company_name,
    SUM(s.quantity * s.unit_price) AS total_spent
FROM customers c
JOIN sales s
    ON c.customer_id = s.customer_id
WHERE EXISTS (
    SELECT 1
    FROM shipping sh
    WHERE sh.sale_id = s.sale_id
      AND LOWER(sh.delivery_status) != 'delivered'
)
GROUP BY
    c.customer_id,
    c.company_name
HAVING SUM(s.quantity * s.unit_price) > 5000;

At first glance, this looks reasonable.

It uses:

• SUM()

• EXISTS

• LOWER()

• GROUP BY

• HAVING

And PostgreSQL accepts it.

But there is a subtle semantic problem.

---

6. The Scope Mismatch

Look at this condition:

sh.sale_id = s.sale_id

The EXISTS subquery is correlated to the individual sale.

That means the query is effectively doing:

For each sale:
    Does this sale have a non-delivered shipment?
        ↓
    If yes, include the sale
        ↓
    SUM those sales

But that's not what the question asked.

The question asks:

For each customer:
    Calculate ALL sales
    AND
    Check whether ANY shipment for that customer is non-delivered

Those are different operations.

---

7. What the Query Should Do

The business logic is:

Condition 1: Total spending

Customer
   ↓
ALL sales
   ↓
SUM(quantity × unit_price)

Condition 2: Shipment condition

Customer
   ↓
Sales
   ↓
Shipping
   ↓
EXISTS at least one non-delivered shipment

The two conditions should be independent.

The corrected query is:

SELECT
    c.customer_id,
    c.company_name,
    c.contact_name,
    SUM(s.quantity * s.unit_price) AS total_spent
FROM customers c
JOIN sales s
    ON c.customer_id = s.customer_id
WHERE EXISTS (
    SELECT 1
    FROM sales s2
    JOIN shipping sh
        ON sh.sale_id = s2.sale_id
    WHERE s2.customer_id = c.customer_id
      AND LOWER(sh.delivery_status) != 'delivered'
)
GROUP BY
    c.customer_id,
    c.company_name,
    c.contact_name
HAVING SUM(s.quantity * s.unit_price) > 5000;

The important change is:

s2.customer_id = c.customer_id

instead of:

sh.sale_id = s.sale_id

Now the SUM() operates over all sales belonging to the customer, while EXISTS independently checks whether the customer has at least one qualifying shipment.

---

8. Prompt Engineering Wasn't Enough

I didn't want to immediately conclude that the model was the problem.

So I strengthened the instructions given to the model.

The prompt explicitly instructed it to:

• Preserve the semantic meaning of the question.

• Aggregate all sales when calculating total customer spending.

• Use case-insensitive text comparisons.

• Use EXISTS for "at least one", "any", and "has" conditions where appropriate.

• Avoid incorrect aggregation caused by one-to-many joins.

• Correlate customer-level subqueries using the customer key.

• Follow PostgreSQL GROUP BY rules.

• Generate read-only SQL only.

For example:

If the user asks for a customer's total spending,
aggregate all sales for that customer unless the user
explicitly requests a filtered total.

For a customer-level result, correlate subqueries
with the customer key, such as:

s2.customer_id = c.customer_id

Do not correlate a customer-level existence condition
to an individual sale row.

I then ran the same test again.

GPT-4o-mini still produced the incorrect semantic structure.

---

9. Testing GPT-5.6

I then tested the same question, schema and instructions with GPT-5.6.

This time, the model generated:

SELECT
    c.customer_id,
    c.company_name,
    c.contact_name,
    c.country,
    c.signup_date,
    SUM(s.quantity * s.unit_price) AS total_expenditure
FROM customers AS c
JOIN sales AS s
    ON s.customer_id = c.customer_id
WHERE EXISTS (
    SELECT 1
    FROM sales AS s2
    JOIN shipping AS sh
        ON sh.sale_id = s2.sale_id
    WHERE s2.customer_id = c.customer_id
      AND LOWER(sh.delivery_status) <> 'delivered'
)
GROUP BY
    c.customer_id,
    c.company_name,
    c.contact_name,
    c.country,
    c.signup_date
HAVING SUM(s.quantity * s.unit_price) > 5000;

This time, the result was:

Gamma Inc     $27,000
Alpha Corp    $27,000

The query correctly separated the customer-level existence condition from the sales aggregation.

---

10. What This Experiment Showed

This wasn't simply a comparison of two models.

The more important observation was the difference between syntactic correctness and semantic correctness.

Syntactic correctness

LLM
 ↓
SQL
 ↓
PostgreSQL accepts it

The query runs.

Semantic correctness

User question
 ↓
Business meaning
 ↓
SQL logic
 ↓
Expected result

The SQL must actually represent what the user asked.

GPT-4o-mini passed the first test but failed the second in this experiment.

GPT-5.6 produced the expected semantic structure for the same test.

---

11. Another Issue I Found: Case Sensitivity

During testing, I also encountered a simpler issue.

The database contained:

Delivered
Pending
In Transit

while generated SQL sometimes used:

sh.delivery_status != 'delivered'

PostgreSQL comparisons are case-sensitive, so:

'Delivered' != 'delivered'

is true.

That can cause a delivered shipment to be treated as non-delivered.

I added an explicit prompt rule:

Use case-insensitive comparisons for text values.

Use:
LOWER(column) = 'value'

or:

LOWER(column) IN ('value1', 'value2')

For known business statuses, an even more explicit condition can be preferable:

LOWER(sh.delivery_status) IN ('pending', 'in transit')

---

12. Why This Bug Was Easy to Miss

The dangerous part wasn't that the SQL crashed.

It didn't.

The application received a perfectly valid database response:

Gamma Inc    $9,000

The number also looked plausible.

That's what makes semantic failures different from syntax errors.

A syntax error is obvious:

ERROR

A semantic error can look like a legitimate business result.

---

13. Human Validation Still Matters

This experiment also changed how I'm thinking about the role of an LLM in a database application.

The model can generate the SQL.

PostgreSQL can execute it.

The application can display the result.

But that doesn't guarantee that the generated query represents the intended business logic.

In this case, manually checking the result against the underlying data and inspecting the generated SQL exposed the problem.

For business-critical applications, I would therefore want validation between:

SQL generation
        ↓
SQL execution

rather than treating successful execution as the final validation step.

---

14. What I'm Working on Next

This experiment exposed several areas I want to improve.

Schema-aware retrieval

Instead of always sending the entire database schema to the model, retrieve the relevant tables, relationships and metadata for the question.

Business-rule context

For example:

sale amount = quantity × unit_price

"total spending" = all sales belonging to the customer

These definitions can be just as important as the schema itself.

Semantic validation

Before executing SQL, inspect whether its structure matches the intent of the question.

For example:

Question:
Customer total spending + at least one non-delivered shipment

Expected:
SUM(all customer sales)
+
EXISTS(non-delivered shipment for customer)

Automated evaluation

I'm also working toward a test dataset containing:

Question
Expected business logic
Expected result
Generated SQL
Actual result

This will make it possible to compare model and prompt changes systematically instead of relying only on manual testing.

Self-correction

The longer-term goal is to allow the system to detect a semantic issue, provide structured feedback to the model, regenerate the SQL, and validate it again.

---

Conclusion

This started as a small project to learn how LLMs can translate natural language into SQL.

The most useful part of the project so far hasn't been getting SQL to execute.

It has been finding cases where the SQL executes correctly but the business logic is wrong.

That distinction becomes especially important when an AI system is producing queries against real business data.

The next phase of this project is therefore less about simply generating SQL and more about validating whether the generated SQL means what the user asked for.

---

Project

GitHub: SQL generator

I'm continuing to build and test the assistant, with the next focus areas being semantic validation, evaluation datasets, schema-aware context and self-correction.