fix(balance): SQL aggregate misuse in getAccountsPeriodAnchor (MIN in subquery WHERE) #175

New issue

Closed

opened 2026-05-01 01:28:35 +00:00 by maximus · 0 comments

maximus commented

2026-05-01 01:28:35 +00:00

Owner

Description

Au premier chargement de /balance, SQLite remonte: error returned from database: (code: 1) misuse of aggregate function MIN().

src/services/balance.service.ts:982-1014 (getAccountsPeriodAnchor) utilise MIN(s.snapshot_date) dans le WHERE d'une sous-requete scalaire (ligne 1006). SQLite refuse l'aggregate hors GROUP BY/HAVING.

Approche (decidee 2026-04-30)

Window function ROW_NUMBER OVER PARTITION:

SELECT account_id, snapshot_date AS anchor_snapshot_date, value AS anchor_value
  FROM (
    SELECT l.account_id, s.snapshot_date, l.value,
           ROW_NUMBER() OVER (PARTITION BY l.account_id ORDER BY s.snapshot_date ASC) AS rn
      FROM balance_snapshot_lines l
      JOIN balance_snapshots s ON s.id = l.snapshot_id
      <WHERE clause range>
  )
 WHERE rn = 1

SQLite >= 3.25 requis (OK chez nous).

Fichiers concernes

src/services/balance.service.ts (lignes 982-1014) - reecriture de la requete via window function
src/services/balance.service.test.ts - test de regression
CHANGELOG.md + CHANGELOG.fr.md - section Fixed

Depends on

(rien)

Criteres d'acceptation

getAccountsPeriodAnchor utilise ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY snapshot_date ASC) et filtre WHERE rn = 1
Test vitest getAccountsPeriodAnchor returns earliest snapshot per account within range reproduit le scenario (>= 2 snapshots/compte, fenetre from/to, fenetre vide) et passe
Test vitest de regression: page /balance charge sans erreur SQLite quand >= 1 snapshot existe
npm test + npm run build + cargo check verts
Entree CHANGELOG sous [Unreleased] -> Fixed (FR + EN)

Decisions prises ce soir

Approche SQL: window function ROW_NUMBER (et non CTE ni sous-requete correlee)
Test de regression OBLIGATOIRE pour eviter recurrence

Spec source

spec-plan-bilan-anomalies-174.md

## Description Au premier chargement de `/balance`, SQLite remonte: `error returned from database: (code: 1) misuse of aggregate function MIN()`. `src/services/balance.service.ts:982-1014` (`getAccountsPeriodAnchor`) utilise `MIN(s.snapshot_date)` dans le WHERE d'une sous-requete scalaire (ligne 1006). SQLite refuse l'aggregate hors GROUP BY/HAVING. ## Approche (decidee 2026-04-30) **Window function ROW_NUMBER OVER PARTITION**: ```sql SELECT account_id, snapshot_date AS anchor_snapshot_date, value AS anchor_value FROM ( SELECT l.account_id, s.snapshot_date, l.value, ROW_NUMBER() OVER (PARTITION BY l.account_id ORDER BY s.snapshot_date ASC) AS rn FROM balance_snapshot_lines l JOIN balance_snapshots s ON s.id = l.snapshot_id <WHERE clause range> ) WHERE rn = 1 ``` SQLite >= 3.25 requis (OK chez nous). ## Fichiers concernes - `src/services/balance.service.ts` (lignes 982-1014) - reecriture de la requete via window function - `src/services/balance.service.test.ts` - test de regression - `CHANGELOG.md` + `CHANGELOG.fr.md` - section Fixed ## Depends on (rien) ## Criteres d'acceptation - [ ] `getAccountsPeriodAnchor` utilise `ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY snapshot_date ASC)` et filtre `WHERE rn = 1` - [ ] Test vitest `getAccountsPeriodAnchor returns earliest snapshot per account within range` reproduit le scenario (>= 2 snapshots/compte, fenetre `from`/`to`, fenetre vide) et passe - [ ] Test vitest de regression: page `/balance` charge sans erreur SQLite quand >= 1 snapshot existe - [ ] `npm test` + `npm run build` + `cargo check` verts - [ ] Entree CHANGELOG sous `[Unreleased]` -> `Fixed` (FR + EN) ## Decisions prises ce soir - Approche SQL: window function ROW_NUMBER (et non CTE ni sous-requete correlee) - Test de regression OBLIGATOIRE pour eviter recurrence ## Spec source spec-plan-bilan-anomalies-174.md