Premier League Predictor: Django & MySQL — Chapter 7, Exercise 1
====================================================

TASK
Using this chapter's own worked example (a team with 4 played home
fixtures totaling 8 goals, and 4 played away fixtures totaling 6
goals), explain step by step exactly how the naive dual-annotate()
query arrives at 56 goals for instead of the real 14, tracing through
the join multiplication that causes it.

SOLUTION

The naive query annotates two Sum() expressions in a single call: one
over home_fixtures (a reverse relation from Team) and one over
away_fixtures (a different reverse relation from the same Team). When
Django compiles this into SQL, it has to JOIN the team's row against
both relations to be able to reference either one, and because there's
no shared key linking a specific home fixture to a specific away
fixture, the SQL JOIN produces every possible combination of one home
row and one away row for that team — a real cross-join.

The team has 4 played home fixtures and 4 played away fixtures, so the
join produces 4 x 4 = 16 combined rows in total, one for every
(home fixture, away fixture) pairing.

Sum('home_fixtures__home_score') is computed over this same 16-row
joined result. Each individual home fixture's home_score value doesn't
appear once per team — it appears once for every away fixture it got
paired with in the join, which is 4 times each (once per away
fixture). So the true total of 8 home goals gets counted 4 times over:
8 x 4 = 32.

The same thing happens to the away sum in the opposite direction:
Sum('away_fixtures__away_score') counts each away fixture's goal value
once per home fixture it was paired with, also 4 times each (since
there are 4 home fixtures): 6 x 4 = 24.

Adding the two inflated sums together: 32 + 24 = 56. Since the true,
correct total is simply 8 (home goals) + 6 (away goals) = 14, the
naive query overstates the real figure by exactly 4x in this case,
because both relations happen to have the same count (4 fixtures
each) — with unequal home/away fixture counts, the two sums would
each be inflated by a different factor, making the wrong total even
less predictable.

WHY THIS WORKS AS AN ANSWER
----------------------------
It traces the actual mechanism (a cross-join producing one combined
row per home/away fixture pairing) rather than just asserting the
result is wrong, works through the exact multiplication for both the
home sum and the away sum separately, and arrives at the same 56
figure the chapter reports, showing precisely where the extra 42
"goals" come from.