A join returns rows. That is all it promises. It does not promise the rows mean anything.
We learned this the expensive way. A league dashboard was reading 46 competitions. The database holds 751 for the same predictions. Nobody had changed a query; nobody had deleted a row. The join had been succeeding for months — returning thousands of rows, every one attached to the wrong match.
This is the audit, run against production: ai_predictions (47,209 rows, 47,114 graded), matches (85,580) and nami_matches (89,601). The lesson is narrower than "test your joins": row counts cannot validate a join, and the only thing that can is a semantic assertion on the payload.
The join everyone writes
ai_predictions has a column named match_id. matches has a primary key named id. The query writes itself:
SELECT m.competition_id, COUNT(*) AS n FROM ai_predictions p JOIN matches m ON m.id = p.match_id WHERE p.settled GROUP BY 1; It runs in under a second. It returns 46 rows. It is wrong.
The first warning sign is parent coverage, which nobody computes because the query already returned data:
SELECT (SELECT COUNT(*) FROM ai_predictions WHERE settled) AS settled, COUNT(*) AS joined_by_id, ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM ai_predictions WHERE settled), 2) AS pct FROM ai_predictions p JOIN matches m ON m.id = p.match_id WHERE p.settled; settled joined_by_id pct 47114 3736 7.93 92.07% of graded predictions have no parent in matches. On a foreign key that is a catastrophe. Here it produced silence, because a join returning some rows looks like one that works.
The resolved rows are worse than the missing ones
The 7.93% that resolved are not correct. They are collisions — two unrelated integer sequences that happen to overlap. Three independent checks prove it, and any one alone would have caught it.
Check 1 — the scores disagree. A prediction stores the final score it was graded against; so does the match row. If the join is real they must match.
SELECT COUNT(*) AS comparable, COUNT(*) FILTER (WHERE p.match_home_score = m.home_score AND p.match_away_score = m.away_score) AS agree, ROUND(100.0 * COUNT(*) FILTER (WHERE p.match_home_score = m.home_score AND p.match_away_score = m.away_score) / COUNT(*), 2) AS pct FROM ai_predictions p JOIN matches m ON m.id = p.match_id WHERE p.settled AND p.match_home_score IS NOT NULL AND m.home_score IS NOT NULL; comparable agree pct 3581 96 2.68 97.32% of resolved rows hang off a match with a different final score. The same check against the correct key returns 99.77% — coming up.
Check 2 — the calendar disagrees. Compare the day the prediction was written with the day the joined match kicked off:
|created_at - kickoff| rows 0 days 0 1-3 days 6 4-30 days 2,479 31-180 days 1,251 Zero same-day. Six within three days. 3,730 rows point at a match that had already been played — sometimes weeks before the prediction existed.
Check 3 — the labels disagree. Join both tables to the same 3,736 predictions and compare competition IDs: 3,736 of 3,736 disagree, 100%. And the true competitions behind those rows number just 4 — four real competitions scattered across 46 invented ones.
That is the failure mode: a broken join does not produce empty output. It produces plausible, confidently wrong output, and the error is concentrated rather than random.
Why nothing stopped it: three ID spaces, zero foreign keys
The schema holds more than one identity per match, and every one is a bare integer:
matches.id 811 .. 92,780 (dense, curated) matches.nami_id 4,336,437 .. 4,670,168 (vendor natural key) nami_matches.nami_id 4,336,437 .. 4,670,170 nami_matches.id 3 .. 25,340,015 (sparse surrogate) ai_predictions.match_id 3,974 .. 24,360,892 The ranges overlap, which is why the collision rate is 7.9% instead of 0%. The column type cannot tell the spaces apart, and neither can the planner. The constraint that would have failed loudly is missing:
SELECT conname, conrelid::regclass AS tbl, confrelid::regclass AS ref FROM pg_constraint WHERE contype = 'f' AND conrelid::regclass::text IN ('ai_predictions','matches'); matches_competition_id_fkey matches competitions matches_home_team_id_fkey matches teams matches_away_team_id_fkey matches teams matches has three outbound foreign keys. ai_predictions has none — its match_id, model_id and user_id are unconstrained integers, each a join waiting to mis-resolve.
The key that is actually correct
ai_predictions.match_id references nami_matches.id, the sparse surrogate of the raw feed. Not matches.id, not matches.nami_id. Same query, different target:
WITH joined AS ( SELECT p.id, p.created_at, n.match_time, p.match_home_score AS p_home, p.match_away_score AS p_away, n.home_score_regular AS n_home, n.away_score_regular AS n_away FROM ai_predictions p LEFT JOIN nami_matches n ON n.id = p.match_id WHERE p.settled ) SELECT COUNT(*) AS settled, COUNT(*) FILTER (WHERE match_time IS NULL) AS no_parent, COUNT(*) FILTER (WHERE created_at > match_time) AS written_after_kickoff, COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL AND (p_home <> n_home OR p_away <> n_away)) AS score_mismatch, ROUND(100.0 * COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL AND p_home = n_home AND p_away = n_away) / COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL), 2) AS score_agreement_pct FROM joined; settled no_parent written_after_kickoff score_mismatch score_agreement_pct 47114 0 81 106 99.77 100% parent coverage, zero orphans, scores agreeing on 46,680 of 46,786 comparable rows. The league dimension goes from 46 competitions to 751 and from 3,736 rows to 47,114 — a 12.6× larger evidence base, obtained by changing one identifier in an ON clause.
The residual deserves naming rather than rounding: 81 rows written after kickoff and 106 score disagreements (0.23%). Those are real defects, not join errors. A working key does not mean a clean table; it means the defects are visible at the right grain instead of laundered into wrong-parent rows.
The missing bridge
matches remains worth keeping — curated competition and team foreign keys, lottery classification, scheduling metadata. It is just reachable by four hops, not one, since matches.nami_id resolves against nami_matches.nami_id for 85,314 of 85,580 rows (99.69%):
ai_predictions.match_id -> nami_matches.id -> nami_matches.nami_id -> matches.nami_id -> matches.id The fix: make the join assert on its own payload
A foreign key on nami_matches.id prevents future orphans, and we added one. But it would not have caught this bug: match_id values inside matches.id's range are valid children of matches.id, because they happen to be. Referential integrity was never violated. Semantics were.
So the durable fix is a validated view that carries the assertion inside it:
CREATE OR REPLACE VIEW v_predictions_graded AS SELECT p.id AS prediction_id, p.model_id, p.created_at, p.prediction_direction, p.hit, p.hit_1x2, n.id AS nami_match_id, n.competition_id, n.match_time, n.home_score_regular, n.away_score_regular, (p.match_home_score = n.home_score_regular AND p.match_away_score = n.away_score_regular) AS score_consistent, (p.created_at <= n.match_time) AS written_before_kickoff FROM ai_predictions p JOIN nami_matches n ON n.id = p.match_id WHERE p.settled AND p.match_home_score IS NOT NULL AND n.home_score_regular IS NOT NULL; Consumers read the view, not the table. The two booleans then become a contract test that fails the build instead of the dashboard:
import psycopg2 DSN = "postgresql://user:pass@127.0.0.1:5433/soccerdream" SQL = """ WITH t AS ( SELECT p.created_at, p.match_home_score AS p_home, p.match_away_score AS p_away, n.match_time, n.home_score_regular AS n_home, n.away_score_regular AS n_away FROM ai_predictions p LEFT JOIN nami_matches n ON n.id = p.match_id WHERE p.settled) SELECT COUNT(*), COUNT(*) FILTER (WHERE n_home IS NULL), COUNT(*) FILTER (WHERE created_at > match_time), COUNT(*) FILTER (WHERE p_home = n_home AND p_away = n_away), COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL) FROM t """ def test_join_integrity(): with psycopg2.connect(DSN) as conn, conn.cursor() as cur: cur.execute("SET statement_timeout = '120s'") cur.execute(SQL) n, orphans, late, agree, comparable = cur.fetchone() assert round(100.0 * (n - orphans) / n, 2) >= 100.0, f"orphans={orphans}/{n}" assert round(100.0 * agree / comparable, 2) >= 99.5, "score disagreement too high" assert late <= 200, f"late-written rows={late} exceed known defect class" Against the old join target the first assertion fails by 92 points. Against the correct one it passes with 0 orphans, 99.77% agreement and 81 late rows — three numbers a reviewer can argue with.
What the wrong join cost
naive matches.id | correct nami_matches.id | |
|---|---|---|
| rows reached | 3,736 | 47,114 |
| competitions | 46 | 751 |
| score agreement | 2.68% | 99.77% |
| orphans | unmeasurable | 0 |
| rows both joinable and semantically true | 96 | 46,680 |
96 of 47,114. 0.20%. That is the end-to-end correctness of the join as written; every per-league rate built on it was a statistic about collisions.
The generalisable part is that join success rate is a vanity metric: it measures whether two integer domains intersect, which in any schema with more than one surrogate key they always will. The only metric that means something is semantic agreement on a field both sides independently observed — here the final score, plus the calendar. Two checks, a few lines of SQL, available the whole time.
For every join in your reporting layer, name the field that would have to agree if the join were real. If you cannot name one, you do not have a join. You have a Cartesian product with good manners.
Analytics and research content. Not betting advice. 18+
本内容仅供数据分析与研究参考,不构成任何决策建议。
