# JOIN-01: disposable SQLite counting exercise

Keep these files in one folder: `schema-seed.sql`, `wrong.sql`,
`diagnostic.sql`, `correct.sql`, `expected.json` and `run_example.py`.
Use a local Python 3 installation with the standard-library `sqlite3`
module. No package installation, SQL service or database file is needed.

From that folder, run:

```sh
python3 run_example.py baseline
python3 run_example.py add_c7
python3 run_example.py remove_c5_c6
python3 run_example.py empty_checks
```

Each command creates a new in-memory database, loads the complete schema
and original seed, applies only that case's stated change, and runs the
three queries. It prints full rows, columns, the actual Python/SQLite
versions and the mutation. Exit 0 means all three result sets match the
handwritten expectations. Exit 1 means a comparison failed. A Python or
SQL exception also fails the command and prints its diagnostic.

`wrong.matches_hand_expected=true` means the known bad counting behavior
was reproduced, not that the bad query answers the task. The correct
query must match the separately specified entity counts.

The four `observed/*.json` files are recorded author runs, not output from
your machine. `execution.json` records their process commands, exit codes
and UTC observation times. That execution wrapper is retained privately and is
not included in reader downloads; do not publish the audit directory to supply it.
Tested author runtime: Python 3.9.6 with
SQLite 3.51.0. Your command prints your actual version; rerun and inspect
the comparisons rather than assuming your environment has that version.

`expected.json` was hand-derived and saved before execution. The baseline
comes from counting four project entities and six individual check IDs.
The three mutations are independent fresh fixtures, not cumulative edits.
Original inputs are never rewritten by the runner.

The complete tutorial is available on [the accompanying blog page](/blog/check-ai-sql-query-joined-rows/). `complete-repair-request.txt`
contains the full task, schema/seed, bad SQL, full observed bad result and
authored bad explanation. `corrected-answer.txt` and `correct.sql` are
the complete worked answer, kept separate from that repair input.

All data and the wrong explanation are original fiction. Running this
exercise does not test a model, upload records, access a private service
or establish that any large or differently shaped database is correct.