SQL Query Review skill for Codex

A reviewed query returns the right rows for normal, empty, NULL and duplicate inputs, cannot be hijacked by its parameters, cannot damage more data than intended, and will still be fast at ten times today’s data. Each finding names the statement, a concrete input that breaks it, and a fix.

Workflow

1. Context. Read AGENTS.md, the dialect and version, the table definitions (\d+ table in psql, SHOW CREATE TABLE in MySQL, the ORM models) and the question the query is meant to answer. A correct query for the wrong question is still wrong.
2. Scan. For files, run the bundled heuristic scanner to get a starting list:

python3 scripts/sql_risk_scan.py db/migrations/ reports/monthly.sql

It flags UPDATE/DELETE without WHERE, DROP/TRUNCATE, NOT IN with subqueries, LIMIT without ORDER BY, leading-wildcard LIKE, functions on filtered columns, BETWEEN on timestamps, SELECT * and lock-heavy DDL. Exit code 1 means HIGH findings exist. It is regex-based: confirm every hit by reading the statement, and do not treat a clean scan as a pass. 3. Read for correctness using the passes below. 4. Prove suspicions with a small fixture or a read-only query against a dev copy. 5. Check performance with EXPLAIN on realistic data (dialect-specific depth in $postgres-query-tuning or $mysql-tuning). 6. Report in the output template. Do not run data-modifying statements to “test” them on shared databases; use a transaction you roll back, on a copy, after asking.

Correctness passes

NULLs

• col = NULL is never true; use IS NULL.
• NOT IN (subquery) returns zero rows if the subquery yields any NULL. Use NOT EXISTS.
• col <> 'x' excludes rows where col is NULL. Intended?
• COUNT(col) skips NULLs; COUNT(*) does not. AVG ignores NULLs (not zero).
• SUM over zero rows is NULL, not 0: COALESCE(SUM(x), 0).
• 'a' || NULL is NULL. Postgres concat() and MySQL CONCAT_WS skip NULLs; MySQL CONCAT returns NULL. Know which one the query uses.

Joins

• Fan-out: joining orders to line_items and to payments, then summing order totals, multiplies each total by items x payments. Aggregate each child in a subquery/CTE first, then join. Check by comparing COUNT(*) before and after each join.
• LEFT JOIN undone by WHERE: LEFT JOIN refunds r ... WHERE r.status = 'ok' drops orders with no refund. Move the condition into ON, or use r.status = 'ok' OR r.id IS NULL deliberately.
• Missing join condition or a condition on the wrong column produces a cross join; row counts explode.
• DISTINCT added to “fix duplicates” usually hides a fan-out bug. Ask why duplicates exist.
• Joining on columns with different types or collations forces casts and may mismatch ('01' = 1 in MySQL is true).

Grouping and aggregates

• Every non-aggregated SELECT column must be in GROUP BY (MySQL with ONLY_FULL_GROUP_BY off will happily return arbitrary values).
• HAVING vs WHERE: filter rows in WHERE, groups in HAVING.
• Integer division: count_a / count_b truncates to 0 in Postgres, SQL Server and SQLite. Multiply by 1.0 or cast.

Time

• BETWEEN '2026-01-01' AND '2026-01-31' on a timestamp misses everything after midnight on the 31st. Use half-open ranges: >= '2026-01-01' AND < '2026-02-01'.
• Which time zone defines “a day”? date_trunc('day', ts) uses the session zone in Postgres for timestamptz. Make it explicit (AT TIME ZONE 'Europe/Berlin').
• now() vs CURRENT_DATE vs app-supplied times in tests: non-deterministic queries need a parameter for testability.

Ordering and paging

• Without ORDER BY, row order is undefined, even if it “always” comes back sorted.
• ORDER BY created_at LIMIT 20 with duplicate timestamps pages inconsistently: add a unique tiebreaker (, id).
• OFFSET pagination skips or repeats rows when data changes between pages; keyset is stable.

Safety passes

Injection

• Any query built with string concatenation, f-strings, template literals or format() from user input is a blocker. Use bound parameters.
• Identifiers (column names for sorting, table names) cannot be parameters: map user input through an allowlist ({"newest": "created_at DESC"}), never interpolate.
• ORM escape hatches (raw(), $queryRawUnsafe, text() with f-strings, extra(), find_by_sql with interpolation) need the same scrutiny.

Blast radius

• UPDATE/DELETE: run the same WHERE as a SELECT count(*) first and state the expected count. Wrap in a transaction and check the affected row count before committing.
• Bulk changes on large tables: batch them ($data-backfills).
• DROP, TRUNCATE, DELETE without a narrow WHERE and column drops need explicit confirmation from the user and a backup or snapshot plan; never run them as part of a review.
• Migrations that lock: classify with $zero-downtime-migrations.

Concurrency

• Read-then-write sequences (SELECT balance then UPDATE balance = <app value>) lose updates under concurrency. Use an atomic UPDATE ... SET balance = balance - $1 WHERE balance >= $1, SELECT ... FOR UPDATE, or a version column ($database-transactions).
• Multi-statement changes that must succeed together need one transaction.
• “Check then insert” for uniqueness races; rely on a UNIQUE constraint and ON CONFLICT / ON DUPLICATE KEY.

Performance pass

• Predicates must be sargable: no functions or casts on the indexed column side.
• SELECT * in application queries fetches unused columns, breaks covering indexes and changes shape when the table changes.
• Correlated subqueries in SELECT execute per row; check the plan.
• IN lists with thousands of literals: use an array parameter or temp table.
• LIKE '%term%' needs trigram or full-text indexing ($full-text-search).
• Check estimated vs actual rows in the plan at production scale, not dev scale.
• For ORM code, count queries per request ($n-plus-one-queries).

Proving a correctness finding

Build the smallest dataset that breaks the query:

WITH orders(id, total) AS (VALUES (1, 100)),
     items(order_id, sku) AS (VALUES (1, 'a'), (1, 'b')),
     payments(order_id, amount) AS (VALUES (1, 60), (1, 40))
SELECT SUM(o.total)                      -- returns 400, expected 100
FROM orders o JOIN items i ON i.order_id = o.id
JOIN payments p ON p.order_id = o.id;

Include the fixture and the wrong vs expected result in the finding.

Severity

Label	Examples
blocker	Injection; unbounded UPDATE/DELETE; wrong numbers in financial or customer-facing output; migration that locks a hot table
major	NULL or fan-out bug on plausible data; missing transaction; non-sargable predicate on a large hot table
minor	Missing tiebreaker; SELECT * in app code; OFFSET at modest depth
nit	Formatting, aliasing, CTE naming

Output template

## SQL review: <file / query name>
**Dialect:** <engine version>  **Question answered:** <one sentence>
**Scanner:** <n high / n medium, confirmed: n>

### Blockers
1. `<file>:<line>` <problem>. Breaking input: <fixture or scenario>. Result: <wrong> vs <expected>. Fix: <SQL>.
### Major
### Minor
### Performance
<plan observations at realistic scale, index suggestions>
### Not checked
<parts not run, missing schema, assumptions>