# Synthetic SQLite join-debugging pack

Run a real relational fixture and compare an overcounting query with verified expected results.

Entirely synthetic dataset. SQL outputs and two constraints verified offline; no AI or endpoint-policy test performed.

Source: https://aona.ai/resources/guides/database-fixtures-ai-debugging/
Sources checked: 2026-09-21

## The relationship that reveals the error

### Order 101

Two items share one shipping charge

Note: The naive join adds shipping twice

### Correct level

Aggregate items per order, then total per customer

Note: Expected first total: 3,900 cents

### Control case

One item on order 201

Note: Both queries return 2,700 cents

| Check | Expected fixture result | Verification |
| --- | --- | --- |
| Correct totals | 3,900 and 2,700 cents | Verified locally |
| Overcount example | 4,200 and 2,700 cents | Verified locally |
| Orphan order | Foreign-key rejection | Verified locally |
| Zero item quantity | Check-constraint rejection | Verified locally |

## Review checklist

- [ ] Keep the fixture invented
  Do not replace it with a dump, credentials or real customer records.
- [ ] Compare amounts and aggregation counts
  The first customer exposes the join bug; the second is a useful control case.
- [ ] Run only the supplied in-memory verifier
  It uses no network, rejects changed SQL and opens no existing database.

## Included example files

- README.md
- fixture.sql
- correct-query.sql
- overcounting-query.sql
- expected-correct.csv
- expected-overcounting.csv
- verify_fixture.py

## Source references

- SQLite: Foreign Key Support: https://www.sqlite.org/foreignkeys.html (2026-09-21)
- SQLite: SELECT: https://www.sqlite.org/lang_select.html (2026-09-21)
- 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 (2026-09-21)

Use the worksheet within the relevant legal, contractual and technical scope. It is not a certification or a record of an installed-product test.