Treat an AI-generated SQL query as a draft and test it on a tiny SQLite database with known answers. Compare the AI result with a manually calculated expected table before using the query on real data.
Who this is forThis guide is for people who use AI to draft SQL and want a small reproducible test before running the query on important data.
Suppose the requirement is: for every customer, report the total value of PAID orders placed during August 2026. Customers with no qualifying orders must still appear with a total of 0.
An AI assistant proposes an INNER JOIN between customers and orders, followed by WHERE conditions for status and date. The query looks reasonable, but the INNER JOIN removes customers who have no matching order rows. That violates the requirement to include zero-total customers.
The following database is synthetic and was written specifically for this article. It has three customers and six orders chosen to test paid versus unpaid status, month boundaries, and a customer with no August paid order.
import sqlite3
from pathlib import Path
SOURCE_DIR = Path("outputs") / "ai_sql_demo"
DATABASE = SOURCE_DIR / "orders.sqlite"
CUSTOMERS = [
(1, "Ana"),
(2, "Ben"),
(3, "Cara"),
]
ORDERS = [
(101, 1, "2026-08-03", "PAID", 40),
(102, 1, "2026-08-20", "PAID", 60),
(103, 1, "2026-08-25", "CANCELLED", 30),
(104, 2, "2026-08-10", "PAID", 25),
(105, 2, "2026-09-01", "PAID", 50),
(106, 3, "2026-08-12", "CANCELLED", 80),
]
SOURCE_DIR.parent.mkdir(parents=True, exist_ok=True)
SOURCE_DIR.mkdir() # Stop if the synthetic source already exists.
connection = sqlite3.connect(DATABASE)
try:
connection.executescript("""
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name TEXT NOT NULL
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date TEXT NOT NULL,
status TEXT NOT NULL,
amount INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
""")
connection.executemany(
"INSERT INTO customers VALUES (?, ?)", CUSTOMERS
)
connection.executemany(
"INSERT INTO orders VALUES (?, ?, ?, ?, ?)", ORDERS
)
connection.commit()
finally:
connection.close()
python create_ai_sql_demo.pyAna has two PAID August orders: 40 and 60, so her total is 100. Her cancelled order does not count. Ben has one qualifying August order worth 25; his September order does not count. Cara has only a cancelled August order, so she must still appear with 0.
| customer_id | customer_name | Expected August paid total |
|---|---|---|
| 1 | Ana | 100 |
| 2 | Ben | 25 |
| 3 | Cara | 0 |
The expected grand total is 125. There should be exactly 3 output rows because the requirement says every customer must appear.
The AI query below filters qualifying orders in the WHERE clause after an INNER JOIN. Cara has no matching PAID August order, so she disappears entirely.
SELECT
c.customer_id,
c.customer_name,
SUM(o.amount) AS august_paid_total
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
AND o.order_date >= '2026-08-01'
AND o.order_date < '2026-09-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;The revised query starts from all customers and uses a LEFT JOIN. The order filters are placed inside the ON condition so nonmatching customers remain. COALESCE converts the resulting NULL aggregate to 0.
SELECT
c.customer_id,
c.customer_name,
COALESCE(SUM(o.amount), 0) AS august_paid_total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'PAID'
AND o.order_date >= '2026-08-01'
AND o.order_date < '2026-09-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;Save the following script as ai_sql_check.py. It runs both queries, compares their results with the hand-computed expected table, and writes a CSV review report. It does not modify the synthetic database.
import csv
import sqlite3
from pathlib import Path
DATABASE = Path("outputs") / "ai_sql_demo" / "orders.sqlite"
OUTPUT_DIR = Path("outputs") / "ai_sql_check_result"
REPORT = OUTPUT_DIR / "sql_check.csv"
AI_SQL = """
SELECT c.customer_id, c.customer_name, SUM(o.amount)
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'PAID'
AND o.order_date >= '2026-08-01'
AND o.order_date < '2026-09-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;
"""
REVISED_SQL = """
SELECT c.customer_id, c.customer_name, COALESCE(SUM(o.amount), 0)
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'PAID'
AND o.order_date >= '2026-08-01'
AND o.order_date < '2026-09-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;
"""
EXPECTED = [
(1, "Ana", 100),
(2, "Ben", 25),
(3, "Cara", 0),
]
def run_query(connection, sql):
return connection.execute(sql).fetchall()
def main() -> None:
if not DATABASE.is_file():
raise FileNotFoundError(f"Database not found: {DATABASE}")
if OUTPUT_DIR.exists():
raise FileExistsError(f"Output folder already exists: {OUTPUT_DIR}")
connection = sqlite3.connect(f"file:{DATABASE.resolve()}?mode=ro", uri=True)
try:
ai_rows = run_query(connection, AI_SQL)
revised_rows = run_query(connection, REVISED_SQL)
finally:
connection.close()
OUTPUT_DIR.parent.mkdir(parents=True, exist_ok=True)
OUTPUT_DIR.mkdir()
with REPORT.open("x", encoding="utf-8", newline="") as stream:
writer = csv.writer(stream)
writer.writerow(["query", "rows", "matches_expected"])
writer.writerow(["AI", repr(ai_rows), ai_rows == EXPECTED])
writer.writerow(["REVISED", repr(revised_rows), revised_rows == EXPECTED])
print(f"Expected rows: {len(EXPECTED)}.")
print(f"AI rows: {len(ai_rows)}; matches expected: {ai_rows == EXPECTED}.")
print(
f"Revised rows: {len(revised_rows)}; "
f"matches expected: {revised_rows == EXPECTED}."
)
print(f"Report: {REPORT.as_posix()}")
if revised_rows != EXPECTED:
raise RuntimeError("Revised SQL does not match the expected result.")
if __name__ == "__main__":
main()
python ai_sql_check.pyThe AI query should return only Ana and Ben, so it produces 2 rows and does not match the expected result. The revised query should return all 3 customers and exactly match the hand-computed table.
| Query | Rows returned | Expected grand total | Matches expected table |
|---|---|---|---|
| AI | 2 | 125 | No |
| Revised | 3 | 125 | Yes |
The AI result can still have the correct grand total of 125 even though it is wrong because Cara's required zero row is missing. This is why checking only totals is insufficient.
Expected rows: 3.
AI rows: 2; matches expected: False.
Revised rows: 3; matches expected: True.
Report: outputs/ai_sql_check_result/sql_check.csv| Common problem | What to inspect |
|---|---|
| Unexpected missing rows | Check JOIN type and whether filters in WHERE remove unmatched LEFT JOIN rows. |
| Totals too large | Look for one-to-many joins that duplicate rows before aggregation. |
| Wrong date range | Use an explicit lower bound and exclusive upper bound appropriate to the stored date format. |
| NULL instead of zero | Decide whether missing matches should stay NULL or be converted with COALESCE. |
| Correct total but wrong detail | Compare complete expected rows, not only the grand total. |
This test proves only that the revised query matches this small synthetic example. Real schemas may contain duplicate relationships, NULL values, timestamps, time zones, refunds, or additional business rules. Expand the test data whenever those conditions matter.
2026-09-20 · hand-checked example · target: Python 3.12 · standard library: csv, pathlib, sqlite3 · no execution
The explanations and examples were written for this site. See the official sources below for the related behavior and concepts.