LLMs write clean SQL. Code runs. Returns wrong numbers. Corrupts dashboards. Corrupts metrics. AI lacks context of data distribution, nullability, indexes. Many teams fall for common myths about software engineering and GenAI. Use systematic verification framework. Prevent silent failures.
Ten verification checks for AI-generated SQL before production merge.
Reference Schema
Example schema for checks:
/* Users table */
CREATE TABLE users (
user_id UUID PRIMARY KEY,
username VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
marketing_opt_out BOOLEAN,
created_at TIMESTAMP NOT NULL
);
/* Orders table */
CREATE TABLE orders (
order_id UUID PRIMARY KEY,
user_id UUID REFERENCES users(user_id),
order_amount NUMERIC(10, 2) NOT NULL,
discount_amount NUMERIC(10, 2) DEFAULT 0.00,
created_at TIMESTAMP NOT NULL
);
/* Payments table */
CREATE TABLE payments (
payment_id UUID PRIMARY KEY,
order_id UUID REFERENCES orders(order_id),
payment_amount NUMERIC(10, 2) NOT NULL,
payment_status VARCHAR(50) NOT NULL
);
/* Refunds table */
CREATE TABLE refunds (
refund_id UUID PRIMARY KEY,
payment_id UUID REFERENCES payments(payment_id),
refund_amount NUMERIC(10, 2) NOT NULL,
refund_status VARCHAR(50) NOT NULL
);Check 1: Join Fan-Out and Cardinality
LLMs struggle with table cardinality. Joining orders and payments directly duplicates order rows if multiple payments exist.
AI query:
/* AI-generated query containing fan-out bug */
SELECT
o.order_id,
o.order_amount,
p.payment_amount
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id;Multiple payments for one order duplicate order row. Summing order_amount inflates revenue.
Verification
Compare base table count with joined dataset count:
SELECT
(SELECT COUNT(1) FROM orders) AS base_count,
(SELECT COUNT(1)
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id) AS joined_count;If joined_count exceeds base_count on 1:1 relationship, fan-out bug exists.
Fix
Aggregate child table in CTE before join:
WITH aggregated_payments AS (
SELECT
order_id,
SUM(payment_amount) AS total_paid
FROM payments
WHERE payment_status = 'SUCCESS'
GROUP BY order_id
)
SELECT
o.order_id,
o.order_amount,
COALESCE(p.total_paid, 0) AS total_paid
FROM orders o
LEFT JOIN aggregated_payments p ON o.order_id = p.order_id;Check 2: Null Propagation and Three-Valued Logic
SQL uses three-valued logic: TRUE, FALSE, UNKNOWN (NULL). LLMs assume binary logic, omitting NULL rows.
AI query:
/* AI-generated query that misses NULL values */
SELECT user_id, email
FROM users
WHERE marketing_opt_out != TRUE;If marketing_opt_out is NULL, NULL != TRUE evaluates to UNKNOWN. Rows excluded.
Verification
Check WHERE clauses for nullable columns. Verify operators handle NULL. Watch for = NULL or != NULL syntax.
Fix
Use IS DISTINCT FROM or explicit NULL checks:
/* Option A: Using IS DISTINCT FROM */
SELECT user_id, email
FROM users
WHERE marketing_opt_out IS DISTINCT FROM TRUE;
/* Option B: Explicit NULL handling */
SELECT user_id, email
FROM users
WHERE marketing_opt_out = FALSE OR marketing_opt_out IS NULL;Check 3: Group By and Non-Aggregated Columns
Postgres requires non-aggregated SELECT columns in GROUP BY, unless functionally dependent on primary key.
AI query:
/* Invalid query generated by LLM */
SELECT
u.user_id,
u.username,
u.email,
MAX(o.created_at) AS latest_order
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id;Fails with: ERROR: column "u.username" must appear in the GROUP BY clause. Adding all columns to GROUP BY alters aggregation grain. Slows execution.
Verification
Ensure selected columns are primary keys, in GROUP BY, or aggregated.
Fix
Use DISTINCT ON for latest order per user:
SELECT DISTINCT ON (u.user_id)
u.user_id,
u.username,
u.email,
o.created_at AS latest_order
FROM users u
JOIN orders o ON u.user_id = o.user_id
ORDER BY u.user_id, o.created_at DESC;Check 4: Window Function Partitioning
LLMs omit partition keys or window frames.
AI query:
/* AI-generated query with window frame bugs */
SELECT
user_id,
created_at,
SUM(order_amount) OVER (ORDER BY created_at) AS running_total
FROM orders;Bugs:
- Missing
PARTITION BY user_idclause pools all users together. - Default frame
RANGEsums duplicate timestamps together instead of sequentially.
Verification
Check OVER clauses for PARTITION BY. Verify if query needs ROWS frame instead of RANGE.
Fix
Specify partition key and use ROWS frame:
SELECT
user_id,
created_at,
SUM(order_amount) OVER (
PARTITION BY user_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;Check 5: Non-SARGable Filters and Implicit Casts
LLMs wrap columns in functions or mix data types. Prevents index usage. Forces full table scans.
AI query:
/* Non-SARGable query */
SELECT order_id, order_amount
FROM orders
WHERE TO_CHAR(created_at, 'YYYY-MM-DD') = '2026-01-01';Applying TO_CHAR to created_at forces full table scan. Index ignored.
Implicit type casting compares string literal to timestamp:
/* Relying on implicit casting */
SELECT order_id, order_amount
FROM orders
WHERE created_at >= '2026-01-01';Postgres handles conversion. Query planner generates suboptimal plans.
Verification
Run EXPLAIN or EXPLAIN ANALYZE. Look for Seq Scan where index scan expected. Check Filter clauses for function calls on columns.
Fix
Keep column unmodified on left. Cast literal on right:
SELECT order_id, order_amount
FROM orders
WHERE created_at >= '2026-01-01 00:00:00'::timestamp
AND created_at < '2026-01-02 00:00:00'::timestamp;This is a common bottleneck when optimizing PostgreSQL query performance on large tables.
Check 6: Division by Zero
LLMs calculate ratios without checking denominators.
AI query:
/* AI-generated query vulnerable to division by zero */
SELECT
order_id,
(discount_amount / order_amount) * 100 AS discount_percentage
FROM orders;If order_amount is zero, query crashes.
Verification
Scan SELECT and WHERE clauses for division operator /. Identify potential zero values in denominator columns.
Fix
Use NULLIF to convert zero to NULL:
SELECT
order_id,
(discount_amount / NULLIF(order_amount, 0)) * 100 AS discount_percentage
FROM orders;Check 7: Implicit Type Coercion in Joins
LLMs join columns with mismatched types.
AI query:
/* AI-generated query with mismatched join types */
SELECT
u.username,
o.order_id
FROM users u
JOIN orders o ON CAST(u.user_id AS VARCHAR) = o.order_id::varchar;Type mismatch forces cast. Breaks index scan. Forces nested loop or seq scan.
Verification
Check JOIN conditions. Verify data types match exactly in schema.
Fix
Remove casts. Match schema types:
SELECT
u.username,
o.order_id
FROM users u
JOIN orders o ON u.user_id = o.user_id;For index optimization strategies, see multitenant database index tuning in PostgreSQL.
Check 8: Correlated Subqueries in SELECT
LLMs use subqueries in SELECT for row-by-row lookups.
AI query:
/* AI-generated query with correlated subquery in SELECT */
SELECT
u.user_id,
u.username,
(SELECT SUM(o.order_amount) FROM orders o WHERE o.user_id = u.user_id) AS total_spent
FROM users u;Executes subquery for every row in outer query. Causes N+1 query pattern. Degrades performance on large datasets.
Verification
Look for SELECT statements containing nested SELECT queries referencing outer table.
Fix
Rewrite using LEFT JOIN with aggregation:
SELECT
u.user_id,
u.username,
COALESCE(SUM(o.order_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;Check 9: Wildcard SELECT in Production Queries
LLMs use SELECT * in subqueries or final outputs.
AI query:
/* AI-generated query using wildcard select */
SELECT *
FROM orders o
JOIN payments p ON o.order_id = p.order_id;Retrieves unused columns. Increases network payload. Breaks if schema changes.
Verification
Check SELECT clauses for * character.
Fix
Explicitly name required columns:
SELECT
o.order_id,
o.order_amount,
p.payment_amount,
p.payment_status
FROM orders o
JOIN payments p ON o.order_id = p.order_id;Check 10: Unbounded String Aggregation
LLMs aggregate strings without limits.
AI query:
/* AI-generated query with unbounded string aggregation */
SELECT
user_id,
STRING_AGG(order_id::varchar, ',') AS order_list
FROM orders
GROUP BY user_id;Large datasets exceed memory limits. Causes query failure or high memory usage.
Verification
Check for STRING_AGG or ARRAY_AGG without limits.
Fix
Limit aggregation size or use subquery with LIMIT:
WITH limited_orders AS (
SELECT
user_id,
order_id,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) as rn
FROM orders
)
SELECT
user_id,
STRING_AGG(order_id::varchar, ',') AS order_list
FROM limited_orders
WHERE rn <= 10
GROUP BY user_id;Automating Audit
Automate checks with Python and sqlglot to parse AST before commit.
import sys
import sqlglot
from sqlglot import exp
def analyze_sql(file_path):
with open(file_path, 'r') as f:
sql_content = f.read()
expression = sqlglot.parse_one(sql_content)
issues = []
# Check for functions on columns in WHERE clause (Non-SARGable)
for where in expression.find_all(exp.Where):
for column in where.find_all(exp.Column):
parent = column.parent
if isinstance(parent, (exp.Anonymous, exp.Func)):
issues.append(f"Performance Warning: Column '{column.name}' is wrapped in a function inside the WHERE clause.")
# Check for missing frames in Window Functions
for window in expression.find_all(exp.Window):
spec = window.find(exp.WindowSpec)
if spec and spec.find(exp.Order) and not spec.find(exp.WindowFrame):
issues.append("Logic Warning: Window function uses ORDER BY but has no explicit frame clause (defaults to RANGE).")
# Check for division by zero risk
for div in expression.find_all(exp.Div):
right = div.expression
if isinstance(right, exp.Column):
issues.append(f"Safety Warning: Division by column '{right.name}' without NULLIF check.")
elif isinstance(right, exp.Literal) and right.this == '0':
issues.append("Safety Warning: Division by zero literal detected.")
# Check for wildcard SELECT
for select in expression.find_all(exp.Select):
for projection in select.expressions:
if isinstance(projection, exp.Star):
issues.append("Style Warning: Wildcard SELECT '*' detected. Explicitly name columns.")
# Check for subqueries in SELECT clause
for select in expression.find_all(exp.Select):
for projection in select.expressions:
if isinstance(projection, exp.Select):
issues.append("Performance Warning: Correlated subquery in SELECT clause. Rewrite with JOIN.")
return issues
if __name__ == "__main__":
if len(sys.argv) < 2:
print("Usage: python audit.py <path_to_sql_file>")
sys.exit(1)
found_issues = analyze_sql(sys.argv[1])
if found_issues:
print("Audit failed. Found the following issues:")
for issue in found_issues:
print(f"- {issue}")
sys.exit(1)
else:
print("Audit passed.")Verification framework ensures AI queries are correct, performant, and safe.
