A cached counter on a listing page was wrong. Not wrong in an interesting way — just quietly, slightly too high. The kind of number nobody audits because nobody imagines it can drift.
It had been drifting for months. Across thirteen installations of the same application, the reconciliation query eventually found roughly 83,000 rows where the stored counter disagreed with the actual count. No error log entry. No failed query. No exception. Every write had returned success, every day, for months.
The cause is a single line of SQL that reads correctly, executes correctly, reports success correctly, and does not do what it says.
The query
The cleanup job removes flagged replies and decrements the per-topic counter. One statement for the delete, one for the counter:
UPDATE topics t
JOIN replies r ON r.topic_id = t.id
SET t.reply_count = t.reply_count - 1
WHERE r.spam = 1;
DELETE r FROM replies r WHERE r.spam = 1;
Read it out loud and it sounds right. For every spam reply, subtract one from its topic's counter. Then delete the replies.
It is not right. The UPDATE subtracts one. Total. Per topic. No matter how many replies matched.
The measurement
Three topics, nine replies, six of them flagged:
CREATE TABLE topics (
id INT PRIMARY KEY,
reply_count INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE replies (
id INT PRIMARY KEY AUTO_INCREMENT,
topic_id INT NOT NULL,
spam TINYINT NOT NULL DEFAULT 0,
KEY (topic_id)
) ENGINE=InnoDB;
INSERT INTO topics VALUES (1,5),(2,3),(3,1);
INSERT INTO replies (topic_id,spam) VALUES
(1,1),(1,1),(1,1),(1,0),(1,0),
(2,1),(2,1),(2,0),
(3,1);
Topic 1 has three flagged replies, topic 2 has two, topic 3 has one. Counters start at 5, 3, 1. After removing the flagged replies they should read 2, 1, 0.
Run the UPDATE:
id reply_count
1 4
2 2
3 0
Topic 1 lost one instead of three. Topic 2 lost one instead of two. Topic 3 is correct — and that is the whole problem, because topic 3 is what a hand test looks like.
Identical numbers on two different MariaDB major versions. This is not a version quirk and not a bug. It is the documented behavior of a multi-table UPDATE: each target row is visited at most once per statement, regardless of how many joined rows satisfy the condition. The engine is not iterating the join result and applying your expression once per row. It is iterating the target table and asking "does any joined row match?"
The answer is yes or no. Not three.
Why nobody catches it in testing
This is the part worth internalizing, because the trap is not the semantics — it's the shape of the evidence.
A constant assignment behaves perfectly. Change the SET from an expression to a literal:
UPDATE topics t
JOIN replies r ON r.topic_id = t.id
SET t.reply_count = 0
WHERE r.spam = 1;
id reply_count
1 0
2 0
3 0
Flawless. Every topic with at least one flagged reply gets zeroed. SET status = 'archived', SET flagged = 1, SET reviewed_at = NOW() — every one of these works exactly as written, because "visit the row once" and "set it to a constant" produce the same result whether you visit once or ten times.
Multi-table UPDATE ... JOIN is a pattern developers use dozens of times and it is correct in almost every one of them. It becomes wrong the moment the right-hand side of the assignment references the column being assigned. Nothing in the syntax marks that boundary.
The client reports identical success. I ran the broken query and the correct query through the same connection and read back the driver's counters:
wrong query -> affected_rows = 3 | Rows matched: 3 Changed: 3 Warnings: 0
right query -> affected_rows = 3 | Rows matched: 3 Changed: 3 Warnings: 0
Three topics matched, three topics changed, zero warnings. Both times. The broken statement and the correct statement are indistinguishable from the application's point of view. If your job logs "updated 3 topics", that log line is true and useless.
Now put the delete next to it:
DELETE -> affected_rows = 6
Six rows deleted, three decrements applied. That discrepancy is the only signal the database ever gives you, and it appears nowhere unless you deliberately compare the two numbers. Nobody compares them, because in a healthy job they are supposed to differ — you delete N replies and touch M topics, and M is naturally smaller. The number that is wrong looks exactly like the number that is right.
The drift compounds silently. Run the cleanup three times against a topic with three flagged replies:
run 1 -> reply_count = 4
run 2 -> reply_count = 3
run 3 -> reply_count = 2
One per run, forever, in the wrong direction from the truth. A nightly job doesn't produce one visible error; it produces a slow leak. By the time somebody notices the number looks odd, there is no incident to correlate it with — no deploy, no outage, no spike. Just a figure that has been wrong for a while.
The fixes that don't work
The instinct once you understand the cause is to make the UPDATE iterate the join instead of the target. You can't. I tried the three obvious routes:
JOIN + GROUP BY REJECTED (syntax error)
UPDATE DISTINCT REJECTED (syntax error)
JOIN + ORDER BY + LIMIT ACCEPTED
The first two are refused outright. The third is accepted on MariaDB and is worth a warning: it parses, it runs, it returns success, and it does not change the "once per target row" rule at all. You get a statement that looks like it addresses the problem and doesn't. (MySQL rejects ORDER BY/LIMIT on a multi-table UPDATE — so this particular false trail is engine-dependent, which is worse than if it were universal.)
There is no flag. The aggregation has to happen before the UPDATE sees the rows.
The fix
Aggregate in a derived table, join against that, and subtract the count:
UPDATE topics t
JOIN ( SELECT topic_id, COUNT(*) AS n
FROM replies
WHERE spam = 1
GROUP BY topic_id ) g
ON g.topic_id = t.id
SET t.reply_count = t.reply_count - g.n;
id reply_count
1 2
2 1
3 0
Now the target table is still visited once per row — that never changed — but the value it needs is already a single number. The join produces exactly one row per topic by construction.
If the counter is derivable at all, the sturdier option is to stop doing arithmetic on it and recompute it outright:
UPDATE topics t
SET t.reply_count = ( SELECT COUNT(*)
FROM replies r
WHERE r.topic_id = t.id
AND r.spam = 0 );
id reply_count
1 2
2 1
3 0
Same result, and with a property the incremental version can never have: it is idempotent. Run it twice, run it a hundred times, the answer is the answer. An incremental counter is a running total of every code path that ever touched it, including the ones that were wrong and the ones that crashed halfway. An absolute recount has no memory of any of that. It costs more, and on a table where the counter is read far more often than it is written, that cost buys you a number that cannot drift.
Use the derived-table form when a full recount is genuinely too expensive. Use the recount when it isn't. Do not use the plain - 1.
Finding the damage you already have
If this pattern is anywhere in your codebase, the counters are already wrong and have been for a while. The reconciliation query is cheap and worth running before you fix anything, so you know the size of the problem:
SELECT t.id,
t.reply_count AS stored,
COUNT(r.id) AS actual,
t.reply_count-COUNT(r.id) AS drift
FROM topics t
LEFT JOIN replies r ON r.topic_id = t.id
GROUP BY t.id, t.reply_count
HAVING t.reply_count-COUNT(r.id) <> 0
ORDER BY ABS(t.reply_count-COUNT(r.id)) DESC;
id stored actual drift
1 4 2 2
2 2 1 1
One detail that will bite you when you write this from memory: the expression in HAVING has to be repeated in full. Referencing the drift alias fails on MariaDB with Reference 'drift' not supported (reference to group function). Same for the ORDER BY. It is a thirty-second detour that reads like a typo when you hit it at the end of a long day.
LEFT JOIN matters too. An inner join silently drops every topic whose replies were all deleted — and those are exactly the rows most likely to be wrong.
The general shape
The defect here is not SQL trivia. It is a statement whose plain-English reading and whose actual semantics diverge, where every observable signal — return code, affected row count, warning count, error log — agrees with the reading and not with the semantics.
You cannot test your way out of that with assertions on success. The only test that catches it asserts on the value: delete three replies from one topic, then check the counter went down by three. That test takes four lines. It is the difference between finding this in an afternoon and finding it in 83,000 rows.
If a counter in your schema has no test that pins its arithmetic to a real count, assume it is wrong and go measure it. It is a five-minute query and the answer is rarely zero.
Written at Alesta WEB, from a real cleanup job that had been quietly subtracting one for months.
