typescript-sqlite-count-distinct-aggregation
Done. The solution document is at ~/MAF-GAP-048-solution.md. Here's the summary:
getModelStats() in apps/server/src/db/repository.ts (~line 489) aggregated player rows, not games:
- COUNT(*) as games_played → a model in a 5-player game counts 5, not 1
- SUM(CASE WHEN p.won = 1 ...) as wins → a game with 4 town winners of the same model inflates wins to 4 (live report: 6 shown vs only 2 distinct won games; row-count 41 vs distinct-game 11)
- raw GROUP BY p.provider, p.model → openai/gpt-4o-mini and gpt-4o-mini spellings split into two rows, then merge-maxed into contradictory results
This violates the documented contract in specs/api-specs.md: "A game counts at most once per model."
c84e02c, on main)-- group by normalized model expression == normalizedModelSql('p') (MAF-GAP-036)
CASE WHEN p.model LIKE p.provider || '/%' THEN substr(p.model, length(p.provider) + 2) ELSE p.model END
SELECT p.provider, <expr> as model,
COUNT(DISTINCT p.game_id) as games_played,
COUNT(DISTINCT CASE WHEN p.won = 1 THEN p.game_id END) as wins,
...
FROM players p WHERE p.provider IS NOT NULL
GROUP BY p.provider, <expr>
ORDER BY games_played DESC
| Check | Result |
|---|---|
| 2 new MAF-GAP-048 regression tests (+4-winner game, +prefixed/plain merge) | 2/2 pass on fixed code |
Same tests against reverted buggy query (d2c4658) |
2/2 fail: expected 5 to be 2 (rows≠games), expected length 1 but got 2 (split keys) |
Full suite apps/server (after pnpm --filter @mafia/shared build) |
14/14 files, 238/238 tests pass |
tsc --noEmit |
exit 0 |
| Live DB (fixer record) | openai/gpt-4o-mini wins 6→11; row-count 41 vs distinct 11 == COUNT(DISTINCT won game_id); judge tier2 PASS, battery 757/757 |
The two pre-existing suite failures (game-engine.test.ts, agent-coordinator.test.ts) were unrelated infra issues — unresolved @mafia/shared/* subpath imports fixed by building the shared package. No schema migration needed; the separate stats-collector/models.ts paths use their own reviewed (MAF-GAP-036/039) semantics and were intentionally left untouched.
# Evidence - Problem class: typescript-sqlite-count-distinct-aggregation - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-08-16T10:30:00.441Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "SQL aggregation counted player ROWS instead of distinct games: getModelStats used COUNT(*) as games_played and SUM(CASE WHEN p.won=1 THEN 1 END) as wins grouped by raw (provider, model). A game with 4 town winners of the same model inflated wins to 4; dual model spellings (provider-prefixed vs plain) split into separate rows then merge-maxed. Fix: COUNT(DISTINCT p.game_id), COUNT(DISTINCT CASE WHEN p.won=1 THEN p.game_id END), and GROUP BY a normalized model expression (CASE WHEN model LIKE provider || '/%' THEN substr(model, length(provider)+2) ELSE model END) matching the existing normalizedModelSql helper. Live-verified: report wins dropped 41 row-count -> 11 distinct-game count == COUNT(DISTINCT won game_id) in the DB.", "environment": "node20-typescript-vitest-sqlite", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "typescript-sqlite-count-distinct-aggregation", "provider": "openrouter", "solved_at": "2026-08-16T10:30:00.441Z", "version": "mafia-ai-benchmark main c84e02c"}Done. The solution document is at ~/MAF-GAP-048-solution.md. Here's the summary:
getModelStats() in apps/server/src/db/repository.ts (~line 489) aggregated player rows, not games:
- COUNT(*) as games_played → a model in a 5-player game counts 5, not 1
- SUM(CASE WHEN p.won = 1 ...) as wins → a game with 4 town winners of the same model inflates wins to 4 (live report: 6 shown vs only 2 distinct won games; row-count 41 vs distinct-game 11)
- raw GROUP BY p.provider, p.model → openai/gpt-4o-mini and gpt-4o-mini spellings split into two rows, then merge-maxed into contradictory results
This violates the documented contract in specs/api-specs.md: "A game counts at most once per model."
c84e02c, on main)-- group by normalized model expression == normalizedModelSql('p') (MAF-GAP-036)
CASE WHEN p.model LIKE p.provider || '/%' THEN substr(p.model, length(p.provider) + 2) ELSE p.model END
SELECT p.provider, <expr> as model,
COUNT(DISTINCT p.game_id) as games_played,
COUNT(DISTINCT CASE WHEN p.won = 1 THEN p.game_id END) as wins,
...
FROM players p WHERE p.provider IS NOT NULL
GROUP BY p.provider, <expr>
ORDER BY games_played DESC
| Check | Result |
|---|---|
| 2 new MAF-GAP-048 regression tests (+4-winner game, +prefixed/plain merge) | 2/2 pass on fixed code |
Same tests against reverted buggy query (d2c4658) |
2/2 fail: expected 5 to be 2 (rows≠games), expected length 1 but got 2 (split keys) |
Full suite apps/server (after pnpm --filter @mafia/shared build) |
14/14 files, 238/238 tests pass |
tsc --noEmit |
exit 0 |
| Live DB (fixer record) | openai/gpt-4o-mini wins 6→11; row-count 41 vs distinct 11 == COUNT(DISTINCT won game_id); judge tier2 PASS, battery 757/757 |
The two pre-existing suite failures (game-engine.test.ts, agent-coordinator.test.ts) were unrelated infra issues — unresolved @mafia/shared/* subpath imports fixed by building the shared package. No schema migration needed; the separate stats-collector/models.ts paths use their own reviewed (MAF-GAP-036/039) semantics and were intentionally left untouched.
# Evidence - Problem class: typescript-sqlite-count-distinct-aggregation - Model: openrouter/deepseek/deepseek-v4-flash-0731 - Solved: 2026-08-16T10:30:00.441Z - Verification: solution produced by pi in sandbox; see signatures.json
{"description": "SQL aggregation counted player ROWS instead of distinct games: getModelStats used COUNT(*) as games_played and SUM(CASE WHEN p.won=1 THEN 1 END) as wins grouped by raw (provider, model). A game with 4 town winners of the same model inflated wins to 4; dual model spellings (provider-prefixed vs plain) split into separate rows then merge-maxed. Fix: COUNT(DISTINCT p.game_id), COUNT(DISTINCT CASE WHEN p.won=1 THEN p.game_id END), and GROUP BY a normalized model expression (CASE WHEN model LIKE provider || '/%' THEN substr(model, length(provider)+2) ELSE model END) matching the existing normalizedModelSql helper. Live-verified: report wins dropped 41 row-count -> 11 distinct-game count == COUNT(DISTINCT won game_id) in the DB.", "environment": "node20-typescript-vitest-sqlite", "language": "typescript", "model": "openrouter/deepseek/deepseek-v4-flash-0731", "problem_class": "typescript-sqlite-count-distinct-aggregation", "provider": "openrouter", "solved_at": "2026-08-16T10:30:00.441Z", "version": "mafia-ai-benchmark main c84e02c"}