Debug SQL with a synthetic database

# Synthetic SQLite debugging fixture

All rows, labels and amounts are invented. No customer records, credentials, external connection strings or API calls are included.

Question: why does adding order shipping after a join overcount the first customer?

The optional verifier uses only Python’s standard sqlite3 module and an in-memory database. Run verify_fixture.py from this extracted pack to check the unchanged SQL, both expected query outputs and two constraints. It does not open an existing database or use a network. Changed SQL is rejected by hash.

Review fixture.sql, overcounting-query.sql and correct-query.sql with the expected CSV files. If you choose to share the example with an approved AI client, share only these synthetic files and the question. Treat a suggested query as something to validate locally, not a verified production fix.

## Guide and sources

Canonical guide: https://aona.ai/resources/guides/database-fixtures-ai-debugging/
Source review: 2026-09-21
- SQLite: Foreign Key Support: https://www.sqlite.org/foreignkeys.html
- SQLite: SELECT: https://www.sqlite.org/lang_select.html
- OAIC: Use of commercially available AI products: https://www.oaic.gov.au/privacy/privacy-guidance-for-organisations-and-government-agencies/guidance-on-privacy-and-the-use-of-commercially-available-ai-products

-- Fully synthetic SQLite fixture. No real customer data or credentials.
PRAGMA foreign_keys = ON;
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  label TEXT NOT NULL CHECK (label LIKE 'SYNTHETIC_%')
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  shipping_cents INTEGER NOT NULL CHECK (shipping_cents >= 0)
);
CREATE TABLE order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0)
);
INSERT INTO customers VALUES (1, 'SYNTHETIC_ALPHA'), (2, 'SYNTHETIC_BETA');
INSERT INTO orders VALUES (101, 1, 300), (102, 1, 500), (201, 2, 200);
INSERT INTO order_items VALUES (1001, 101, 2, 1000), (1002, 101, 1, 500), (1003, 102, 3, 200), (1004, 201, 1, 2500);

-- Aggregate items per order before adding each order's shipping once.
WITH per_order AS (
  SELECT o.order_id, o.customer_id,
         o.shipping_cents + COALESCE(SUM(i.quantity * i.unit_price_cents), 0) AS total_cents
  FROM orders AS o
  LEFT JOIN order_items AS i ON i.order_id = o.order_id
  GROUP BY o.order_id, o.customer_id, o.shipping_cents
)
SELECT customer_id, SUM(total_cents) AS total_cents, COUNT(*) AS order_count
FROM per_order
GROUP BY customer_id
ORDER BY customer_id;

-- Intentionally wrong for this task: shipping is added once per joined item.
SELECT o.customer_id,
       SUM(i.quantity * i.unit_price_cents + o.shipping_cents) AS total_cents,
       COUNT(*) AS joined_row_count
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
GROUP BY o.customer_id
ORDER BY o.customer_id;

customer_id,total_cents,order_count
1,3900,2
2,2700,1

customer_id,total_cents,joined_row_count
1,4200,3
2,2700,1

# Offline, in-memory SQLite verification of the unchanged synthetic pack.
import hashlib
import json
from pathlib import Path
import sqlite3

root = Path(__file__).parent
expected_hashes = {'fixture.sql': 'ea22bd66ff6182552a4c726dbba7a2634c5e49e0726ab2b471165a1bc1736250', 'correct-query.sql': 'bce555fad1354c7b3dde4a286bfdee0fe2ea9b98b93b229c06b23112f8cbf197', 'overcounting-query.sql': 'b5b71cb2aff59ee818442585c14410c3a538181064f92a6b14fb7bf90871d97f'}
texts = {}
for name, expected in expected_hashes.items():
    text = (root / name).read_text()
    if hashlib.sha256(text.encode()).hexdigest() != expected:
        raise SystemExit("Refusing modified SQL: use the unchanged synthetic fixture.")
    texts[name] = text
connection = sqlite3.connect(":memory:")
try:
    connection.executescript(texts["fixture.sql"])
    assert connection.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    correct_rows = connection.execute(texts["correct-query.sql"]).fetchall()
    overcounting_rows = connection.execute(texts["overcounting-query.sql"]).fetchall()
    assert correct_rows == [(1, 3900, 2), (2, 2700, 1)]
    assert overcounting_rows == [(1, 4200, 3), (2, 2700, 1)]
    rejected = []
    for label, statement in [
        ("foreign_key", "INSERT INTO orders VALUES (999, 999, 0)"),
        ("positive_quantity", "INSERT INTO order_items VALUES (9999, 101, 0, 100)")
    ]:
        try:
            connection.execute(statement)
        except sqlite3.IntegrityError:
            rejected.append(label)
        else:
            raise AssertionError("Expected constraint rejection: " + label)
    print(json.dumps({"fixture": "D18 synthetic only", "sqlite_version": sqlite3.sqlite_version,
        "correct_rows": correct_rows, "overcounting_rows": overcounting_rows,
        "constraints_rejected": rejected, "database": "in-memory", "network_calls": 0}))
finally:
    connection.close()