The query that reported 10,863 clicks instead of 213

The site page in Isotherm is a table: one row per tracked URL, with how many people visited it and how many of them got stuck. It is the first thing you look at, and it decides whether a page is worth opening a heatmap for. On the demo site it said one page had 10,863 clicks.

That number is wrong. The real figure is 213.

The thing that should bother you is that I did not notice for a while. 10,863 is a plausible-looking number. It is large, it has the right shape, it sorts the table in a believable order. Nobody double-checks a number that is merely large. If it had said -1 or NaN or 0, I would have fixed it in ten minutes.

What the query did#

The page needed four numbers per row: sessions, clicks, rage clicks, dead clicks. Sessions live in page_sessions (one row per page/visitor). Clicks, rage and dead all live in events, discriminated by a type column — one table, three kinds of row. Both tables hang off pages by page_id. The obvious query joins both and aggregates:

SELECT
  pages.*,
  COUNT(DISTINCT page_sessions.id)                            AS sessions,
  SUM(CASE WHEN events.type = 'click' THEN 1 ELSE 0 END)      AS clicks,
  SUM(CASE WHEN events.type = 'rage'  THEN 1 ELSE 0 END)      AS rage,
  SUM(CASE WHEN events.type = 'dead'  THEN 1 ELSE 0 END)      AS dead
FROM pages
LEFT JOIN page_sessions ON page_sessions.page_id = pages.id
LEFT JOIN events        ON events.page_id        = pages.id
WHERE pages.site_id = ?
GROUP BY pages.id
ORDER BY sessions DESC;

Read the FROM clause and forget the aggregate functions for a second. Before GROUP BY runs, the join has produced one row for every pair of (session, event) belonging to the same page. Not one row per session plus one row per event. One row per combination, and the event side of that combination is every event, regardless of type — the CASE WHEN filtering happens later, on a rowset that has already been multiplied out.

The demo page has 51 sessions and 253 events (213 click, 34 dead, 6 rage). That does not join to 304 rows. It joins to 51 × 253 = 12,903 rows, because the two joins are independent of each other and SQL will happily hand you their cross product. Of those 12,903 rows, 51 × 213 = 10,863 have type = 'click', and that is what the SUM reported.

10,863 / 213 = 51. Every click was counted once per session. Rage came out as 306 and dead as 1,734, same multiplier, both equally believable.

That is the whole bug. It is a cartesian product between two sibling has-many relations, and it is the single most common way to get a wrong-but-believable number out of a relational database.

COUNT(DISTINCT) is what let it survive#

Here is the part I find genuinely nasty. The sessions column was correct. It said 51, which is the truth.

It was correct because I had written COUNT(DISTINCT page_sessions.id). Each session id appeared once per event on the page — 253 times — and DISTINCT collapsed all 253 copies back down to one. So the column a human would sanity-check — visitors, the number you have a gut feel for, the number that has to line up with what you know about your own traffic — was the one column the bug could not touch.

Meanwhile SUM(CASE WHEN ...) has no such protection. There is nothing to be distinct on: you are adding up ones, and the join gave you 51 copies of every one. COUNT(DISTINCT) was not a fix, it was camouflage. It made the query pass the one check anybody was ever going to run on it.

Drop the DISTINCT and the disguise falls off. COUNT(page_sessions.id) counts joined rows, so it would have returned 12,903 — the full row count, not 10,863, because it sees rage and dead rows too. Sessions and clicks would have printed two different absurd numbers next to each other, the row would have been obviously insane, and I would have found this on day one. The partially-correct query is worse than the fully-broken one. That is not a paradox, it is just what happens when a bug is allowed to hide behind the number people trust.

The fix#

Stop joining. Ask each question separately, scoped to the row you are already on:

$countEvents = fn (string $type) => DB::table('events')
    ->selectRaw('COUNT(*)')
    ->whereColumn('events.page_id', 'pages.id')
    ->where('events.type', $type);

$pages = Page::query()
    ->where('site_id', $site->id)
    ->select('pages.*')
    ->selectSub(
        DB::table('page_sessions')
            ->selectRaw('COUNT(*)')
            ->whereColumn('page_sessions.page_id', 'pages.id'),
        'sessions',
    )
    ->selectSub($countEvents('click'), 'clicks')
    ->selectSub($countEvents('rage'), 'rage')
    ->selectSub($countEvents('dead'), 'dead')
    ->orderByDesc('sessions')
    ->get();

whereColumn('events.page_id', 'pages.id') is the load-bearing line. It is what makes the subquery correlated — it references the outer row, so it is evaluated once per page and returns a scalar. The SQL:

SELECT
  pages.*,
  (SELECT COUNT(*) FROM page_sessions
     WHERE page_sessions.page_id = pages.id)                          AS sessions,
  (SELECT COUNT(*) FROM events
     WHERE events.page_id = pages.id AND events.type = 'click')       AS clicks,
  (SELECT COUNT(*) FROM events
     WHERE events.page_id = pages.id AND events.type = 'rage')        AS rage,
  (SELECT COUNT(*) FROM events
     WHERE events.page_id = pages.id AND events.type = 'dead')        AS dead
FROM pages
WHERE pages.site_id = ?
ORDER BY sessions DESC;

No GROUP BY, no DISTINCT, no join. Four independent counts, each one structurally incapable of seeing the other three. There is no rowset for them to multiply against. You cannot write this version and get a cartesian product, which is the property I actually wanted — not "correct today" but "cannot be wrong in that particular way."

Laravel's withCount does exactly the same thing under the hood, and the heatmap page uses it directly, because there is only one relation to count there:

$versions = PageVersion::where('page_id', $page->id)
    ->withCount(['events as clicks' => fn ($q) => $q->where('type', 'click')])
    ->orderByDesc('last_seen_at')
    ->get();

withCount was always the safe tool. I reached past it for the site page because I wanted four counts from two tables and thought a join would be tidier. It was tidier. It was also wrong by a factor of 51.

The rule#

Never join two has-many tables and then aggregate. Not "be careful when you do." Don't. If a row has many of A and many of B and you need counts of both, use a correlated subquery per count, or aggregate each table separately and join the aggregates.

The reason to state it that flatly is that the failure is silent, plausible, and scales with your data. It gets worse as a customer gets more traffic, and the error factor is the count of the other table, so it is never a fixed offset you might spot. A page with 4 sessions is off by 4x. A page with 3,000 sessions is off by 3,000x. Both look like numbers.

Where this advice is wrong#

The correlated-subquery version issues one scalar subquery per column per row. On the site page that is fine and I will defend it: a site has tens of pages, not millions, events.page_id and page_sessions.page_id are indexed, and each subquery is an index range count. Four subqueries across 40 rows is 160 index lookups, which SQLite does without noticing.

Push the same shape to 100,000 rows and you will feel it. At that point the correct move is the other fix — aggregate first, then join the aggregates:

FROM pages
LEFT JOIN (SELECT page_id, COUNT(*) AS sessions
           FROM page_sessions GROUP BY page_id) s ON s.page_id = pages.id
LEFT JOIN (SELECT page_id,
                  SUM(type = 'click') AS clicks,
                  SUM(type = 'rage')  AS rage,
                  SUM(type = 'dead')  AS dead
           FROM events GROUP BY page_id) e ON e.page_id = pages.id

Each derived table is already one row per page_id, so joining them cannot multiply anything. It is the same guarantee, bought with more SQL. I did not write it because Isotherm's events table has no rollup and grows forever (that's in the README under what isn't done), and pre-aggregating the whole table to render 40 rows would be the slower option today. When retention and rollups land, this is the query that replaces it.

The other thing worth admitting: this bug would have been caught instantly by a test that inserted 2 sessions and 3 clicks and asserted the row said 3. Isotherm has no test suite. The pipeline was verified by driving a real browser and looking at the database, which is how I caught this in the end — I counted the rows in events by hand and they did not match the dashboard. A manual pass finds the bug once. A test finds it every time someone reaches for a join because it looks tidier.