Check an AI SQL Query Against the Rows It Actually Joins

To check a SQL count, write down what one row represents in each source, inspect the rows produced by the join, and count the requested entities rather than assuming each joined row is one of them. Then execute the complete query on a small fixture whose results you can calculate by hand. A query that runs without an error can still answer the wrong counting question.

By CueSpec Workflow Editors · AI-assisted team editorial.

This walkthrough counts projects, check events and passed check events for each team. It includes the full data, a deliberately wrong query, its actual output, a seven-row diagnostic and a complete working correction. All inputs are original fictional teaching data. The SQL was run locally; the wrong explanation was authored for the example, not captured from an AI assistant. No model or production database was used.

An older two-table counting question illustrates the underlying difficulty: the number of joined combinations can exceed the number of objects someone wants to count. An older discussion of AI-assisted SQL contains differing individual experiences. Neither establishes a current model's accuracy or makes this an AI-only problem. The same checks apply to a query you write yourself.

Fix the question and the row meanings first

The question is: For each team, how many projects are there, how many check events occurred, and how many of those events passed? Include projects with no checks.

Two events can have the same outcome without being the same event. C1 and C2 are both pass for P1, but their event IDs differ and both must count. P4 is a West project with no check event. It must contribute one project and zero checks, rather than disappearing from the report.

This differs from asking whether an ID appears in another list. We are not normalizing identifiers, finding missing list members or allocating matched pairs. The relationship intentionally lets one project have several events.

Use the complete four-project, six-event fixture

Save the following as schema-seed.sql. It contains every original row and the key constraints, so the example does not depend on a missing database or attachment.

PRAGMA foreign_keys = ON;

CREATE TABLE projects (
    project_id TEXT PRIMARY KEY NOT NULL,
    team TEXT NOT NULL
);

CREATE TABLE checks (
    check_id TEXT PRIMARY KEY NOT NULL,
    project_id TEXT NOT NULL REFERENCES projects(project_id),
    outcome TEXT NOT NULL
);

INSERT INTO projects (project_id, team) VALUES
    ('P1', 'North'),
    ('P2', 'North'),
    ('P3', 'South'),
    ('P4', 'West');

INSERT INTO checks (check_id, project_id, outcome) VALUES
    ('C1', 'P1', 'pass'),
    ('C2', 'P1', 'pass'),
    ('C3', 'P1', 'fail'),
    ('C4', 'P2', 'pass'),
    ('C5', 'P3', 'fail'),
    ('C6', 'P3', 'pass');

North owns P1 and P2; South owns P3; West owns P4. P1 has three checks, P2 one, P3 two and P4 none. Before running a query, the project counts are therefore 2, 1 and 1. Counting the check IDs gives 4, 2 and 0 events; counting the pass events gives 3, 1 and 0.

The baseline's full manual expectation is:

The independently handwritten expected.json was saved before execution. It also contains the complete expected diagnostic and the known wrong results for all four cases. Its values were not generated by either query. For this tiny input, inspecting the source rows manually is enough to establish the expected counts before asking an assistant to help.

Run a plausible query that counts the wrong thing

Here is the entire authored counterexample, wrong.sql:

SELECT
    p.team,
    COUNT(*) AS projects,
    COUNT(*) AS checks,
    SUM(CASE WHEN c.outcome = 'pass' THEN 1 ELSE 0 END) AS passed
FROM projects AS p
LEFT JOIN checks AS c ON c.project_id = p.project_id
GROUP BY p.team
ORDER BY p.team;

It executed without a SQL exception. The full observed baseline result was:

This is the complete deliberately wrong explanation accompanying it:

DELIBERATELY WRONG — authored teaching counterexample

I joined projects to checks and counted the joined rows for both measures.
North has 4 projects, 4 checks and 3 passed checks. South has 2 projects,
2 checks and 1 passed check. West has 1 project, 1 check and 0 passed
checks. The full result therefore contains 7 projects and 7 checks,
with 4 passed checks.

The output shape looks reasonable: three teams and three integer counts. North's check count and the pass totals even agree with the task. Those partial successes do not validate the project counts or West's check count. The source has four projects and six events, while the wrong columns total seven and seven.

Inspect all seven joined rows

Before adding more aggregates, run diagnostic.sql:

SELECT p.project_id, p.team, c.check_id, c.outcome
FROM projects AS p
LEFT JOIN checks AS c ON c.project_id = p.project_id
ORDER BY p.project_id, c.check_id;

The entire observed baseline diagnostic is below. NULL is SQL null in the right-hand columns, not a string stored in a new check row.

P1 contributes three joined rows, so counting those rows does not count P1 once. P3 contributes two. P4 contributes an unmatched left row with null right-side columns: that row keeps the project visible, but it is not a check event.

SQLite's SELECT documentation describes the joined combinations and the null right-hand columns of unmatched left rows. Its aggregate definitions distinguish COUNT(*), which counts rows, from COUNT(X), which counts non-null values of X. You still have to decide which row or value corresponds to the entity in your question.

Do not solve this by deduplicating outcome. That would collapse C1 and C2 even though the question counts events, not different outcome labels. Also do not add WHERE c.outcome = 'pass' to this whole joined report: it would discard non-pass events from the check total and remove P4's null-extended row. The requested report includes all events and zero-event projects.

Aggregate events to one row per project, then count projects

The complete correction is correct.sql:

WITH per_project AS (
    SELECT
        project_id,
        COUNT(*) AS checks,
        SUM(CASE WHEN outcome = 'pass' THEN 1 ELSE 0 END) AS passed
    FROM checks
    GROUP BY project_id
)
SELECT
    p.team,
    COUNT(*) AS projects,
    SUM(COALESCE(c.checks, 0)) AS checks,
    SUM(COALESCE(c.passed, 0)) AS passed
FROM projects AS p
LEFT JOIN per_project AS c ON c.project_id = p.project_id
GROUP BY p.team
ORDER BY p.team;

The per_project stage reads actual event rows. Its COUNT(*) counts events, and its conditional sum counts pass events. Grouping by project_id makes at most one aggregate row for each project with checks.

The outer query joins that result to projects. Each project now contributes exactly one row to the outer aggregation, so that stage's COUNT(*) counts projects. P4 has no event aggregate; COALESCE supplies zero for its check and pass counts. No raw event is removed or merged with another event.

Here is the entire observed corrected baseline result:

Across those three rows, the result reconciles to all four source projects, all six check IDs and four pass events. The recorded baseline output includes the full diagnostic, wrong result and correct result, with column names and the actual runtime versions.

A direct join can also use an explicitly distinct project key and a non-null event key for the respective counts. That does not make “add DISTINCT” a universal repair. Adding another one-to-many relationship or changing the requested entity changes what must be checked. The preaggregation above is a clear solution to this supplied two-table question, not a benchmark or a claim of optimal SQL for every database.

Keep the repair request complete

If an assistant helped draft the wrong query, give it the actual inputs and observed output, not just the instruction “fix the count.” The complete instruction for this example is:

Repair the complete deliberately wrong query and explanation below using
only this complete fictional JOIN-01 packet. Return the entire corrected
SQLite SELECT, the complete seven-row diagnostic for the baseline and
the full three-row per-team result, followed by an explanation tracing
each repaired count to project IDs and check IDs. Do not return just a
suggestion to add DISTINCT or a single corrected result row.

Question: for each team, count projects, individual check events and passed
check events. Include projects with no checks. Use the original team,
project_id, check_id and outcome fields and the supplied keys unchanged.
One project row is one entity; one check_id is one independent event.
C1 and C2 are separate pass events. P4 belongs to West even with no events.
Output one row per team ordered North, South, West for this fixture.

Preserve the original data. Do not count a null right-side placeholder as
an event. Do not discard repeated pass labels, filter the whole result to
pass-only rows or change the question to list membership reconciliation.
Explain where each query stage has one row per event versus per project.

The bad query below was actually run locally on fictional inputs; the bad
prose is an authored counterexample, not captured model output. Do not
claim a model has been run or that your returned query has been executed.
Prepare text only. Any later execution must use a disposable local toy
copy and report the actual engine/results separately. Do not connect to a
production database, upload data, install packages or access an account.

The complete repair packet appends the entire schema and seed, full wrong query, full observed wrong three-row output and full authored wrong explanation. It is self-contained. The worked correct query and complete corrected explanation are separate, so they do not silently become evidence supplied in that input.

The corrected explanation is:

The requested project count uses project entities, while check counts use
individual check-event entities. North has 2 projects, 4 check events and
3 passed check events. South has 1 project, 2 check events and 1 passed
check event. West has 1 project and no check events, so its checks and
passed counts are both 0. The full three-row output totals 4 projects,
6 check events and 4 passed check events.

P1 occurs three times in the raw joined diagnostic because it has three
different check IDs. C1 and C2 are separate pass events, not duplicate
outcome records to discard. P3 occurs twice because it has C5 and C6.
P4 has no event: its null right-side columns do not describe a new check.
The seven joined rows are matching combinations plus the unmatched P4
row, not a seven-project or seven-check inventory.

The corrected query first aggregates actual checks to one row per project,
then joins that result to the four project rows. COUNT(*) in the outer
query now counts projects; zero event counts are supplied with COALESCE.

The baseline wrong query, diagnostic and corrected query were actually
executed locally using SQLite 3.51.0 against this fictional fixture.
All four prescribed data cases matched independently handwritten expected
rows. No model was tested, and no production database or customer data
was accessed. These observations do not establish performance at scale
or correctness for a different schema or question.

No request here was submitted to a model. The observed failure is the result of executing the authored bad SQL on the toy data; it is not a measured failure rate of an AI product.

Change the data before trusting the baseline alone

A query can happen to fit one input. These three independent changes test whether it preserves event multiplicity and zero-event projects. Each starts from the original seed in a new in-memory database.

The full corrected outputs observed for the three changed cases were:

Adding C7 increases North's check and pass counts without adding a project. Removing South's events leaves its one project present. With no events anywhere, all three teams still appear with their project counts and zero event counts.

All four cases actually ran using Python 3.9.6 and SQLite 3.51.0 in four separate local processes, each exiting with status 0. In every case, the complete diagnostic, known wrong output and corrected output matched the corresponding handwritten expectations. Matching the expected wrong output confirms that the flaw was reproduced; it does not certify the wrong query. The recorded changed-case results are add_c7, remove_c5_c6 and empty_checks.

Rerun the small package on your machine

Keep the schema, three SQL files, expected.json and run_example.py together. With a local Python 3 installation that includes the standard-library SQLite module, run these commands from that folder:

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 prints the complete rows for all three queries, the mutation, actual Python/SQLite versions and comparisons. It creates no database file and installs no package. Exit 0 means all the specified comparisons matched; exit 1 means a comparison failed. An execution error also fails the command. Your runtime version may differ from the recorded one, so read your output. The local instructions describe which files to keep and how the expectations were prepared.

These observations cover this fictional schema and four stated data cases. They do not test a remote database, additional joins, production permissions, data uploads, a model, large-data performance or business outcomes. Before using a query on a real system, restate its own keys and row meanings and verify a small authorized example with independently known results. Keep the inspected rows and the complete result beside the query you decide to use.