--- title: Repair the expected-fantasy-points percentile SQL that fails the data-view validity gate type: task status: completed description: >- Three generated data-view statements for player_expected_fantasy_points_share_percentile_from_plays fail EXPLAIN with 'missing FROM-clause entry for table pg', so db/gates/check-data-view-sql-validity.mjs is red on master and yarn verify cannot pass. base_uri: user:task/league/repair-expected-fantasy-points-percentile-sql.md created_at: '2026-09-24T18:18:25.972Z' entity_id: 594313f8-bd07-42eb-9705-2a6e6bbeaffd finished_at: '2026-09-24T21:20:16.639Z' observations: - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ public_read: true started_at: '2026-09-24T19:52:11.232Z' tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-09-24T21:20:16.639Z' visibility_analyzed_at: '2026-11-06T00:18:00.967Z' --- ## Problem `db/gates/check-data-view-sql-validity.mjs` fails on master with `GATE FAIL: 3 generated statement(s) did not survive EXPLAIN`. All three are the same field and the same Postgres error: ``` INVALID 42P01 player_expected_fantasy_points_share_percentile_from_plays ... missing FROM-clause entry for table "pg" ``` The gate is a hard blocker for `yarn verify`, so every session that runs the full gate set is stopped by a defect none of them authored. ## Cause The percentile subquery selects from the materialized CTE alone: ```sql FROM t30ae4c5e3691cf23650c9310cf06f8d9 WHERE t30ae4c5e3691cf23650c9310cf06f8d9.pid = player.pid AND ... ``` but its projection references `pg.player_position` in three places — the `CASE MAX(pg.player_position)` threshold, the `PARTITION BY MAX(pg.player_position)` and the repeat of that threshold inside the partition expression. `pg` is the `player_gamelogs` alias bound INSIDE the CTE and is not in scope in the outer subquery. The CTE already groups by `pg.pid`, so the fix is likely to project the position out of the CTE as its own column and reference that column instead of the alias. Confirm against the measure contract before writing. ## Scope Three generated statements, all for `player_expected_fantasy_points_share_percentile_from_plays`, across the row-axis variants (`player`, `row_axes=[year]`, `row_axes=[year,week]`). ## Origin - `2129f44e8` (2026-09-23) added `player_expected_fantasy_points_share_percentile_from_plays` - `1b5fd3977` (2026-09-24) surfaced the share and its percentile on the three consumer surfaces The three tasks in this area — `add-population-percentile-to-data-view-measures`, `██████████████████████████`, `████████████████████████████████████████████████` — are all Completed, which is why nothing was watching this. ## Plan - [x] Reproduce: `node db/gates/check-data-view-sql-validity.mjs` and confirm the same 3 statements and the same 42P01 - [x] Read [[user:text/league/data-views/measure-contract.md]] for how a measure projects a grouping column it needs downstream - [x] Project the position column out of the materialized CTE and repoint the three `pg.player_position` references - [x] Re-run the gate to green, and confirm the three statements are PRESENT in its output rather than merely absent from the failure list - [x] Run the field's own specs and `yarn test:isolated` - [x] `yarn lint`, `yarn check:types`, `yarn verify` ## Verification The gate must report the three statements as VALID rather than stop emitting them — an omitted statement and a passing statement read the same in a failure-count check. Assert on the statement count the gate reports, not on the absence of `GATE FAIL`. ## Found by `repl-6dc5d471` (`█████████████████████████████████████`) on 2026-09-24, while running the gate set for [[█████████████████████████-████-██████-█████-████████]]. Confirmed unrelated to that work: the prop-market commits touch `libs-server/settlement-output-checks.mjs`, `test/libs-server.settlement-output-checks.spec.mjs` and the private check-parking file, none of which the data-view SQL generator reads. ## Sharper diagnosis, 2026-09-24 The cause is narrower than "a missing FROM entry", and it is NOT a stray alias. `MAX(pg.player_position)` is the SHARED, deliberate expression `cohort_subject_position` in `libs-server/data-views/measure/opportunity-atoms.mjs:205`. Its header explains why it must be that and not `p_trg.nfl_primary_position_group`: the partition key wants the position of the player BEING RANKED, one value per output row, and the target's position varies within the group. `libs-server/data-views/measure/percentile.mjs:25-40` states the precondition that makes it legal — "on a `plays_cohort` column the subject's position is already in scope without any join, the cohort expansion joins `player_gamelogs as pg`" — and sets `apply_joins` to null on that basis. **That precondition does not hold on the `year_offset_range` render path**, which is the only path that fails. There the percentile is emitted inside a CORRELATED SUBQUERY whose FROM names the materialized CTE alone (`FROM t WHERE t.pid = player.pid AND t.year BETWEEN ...`). The cohort expansion that binds `pg` is inside the CTE, so `pg` is out of scope in the subquery that references it. The three failing statements are exactly the offset-range variants; the plain `player` variant renders in cohort scope and plans fine. So the repair is NOT to re-add a join. It is to make the subject position reachable in the offset-range scope — project `player_position` out of the materialized CTE (it already groups by `pg.pid`, so the attribute is functionally dependent and available) and have the percentile reference the CTE's own column when rendering there, leaving the cohort-scope path alone. Both consumers of `cohort_subject_position` must move together, since it is one expression shared so that nothing can "resolve a player to different positions in the same statement": the percentile's PARTITION BY and `qualified_role_share_qualifier`'s floor CASE. A fix that repoints only the partition key reintroduces exactly the split that expression exists to prevent. Expect data-view goldens to need regeneration. Check for a live session on expected fantasy points before starting — this landed the same day it broke. ## The diagnosed repair is not sufficient, 2026-09-24 (`repl-ef5faa34`) **Projecting the position out of the CTE produces VALID SQL that returns a constant 0.00.** The diagnosis above is correct about the cause of the 42P01 and wrong about what fixing it yields, so the repair cannot stop there. The offset-range path emits the percentile inside a SCALAR CORRELATED SUBQUERY — `(SELECT ... FROM t WHERE t.pid = player.pid AND t.year BETWEEN ...)`. That subquery aggregates with no `GROUP BY`, so it returns exactly ONE row, and a window function over one row makes `PERCENT_RANK()` zero by definition. Making `pg` reachable there changes a loud 42P01 into a silent wrong number, which is the trade the validity gate's own header warns against. Verified rather than reasoned, on a corpus with no league tables in it — three subjects carrying distinct values 10, 20 and 30, all three reading 0: ```sql WITH t(pid, grp, v) AS (VALUES (1,'RB',10),(2,'RB',20),(3,'RB',30)), p(pid) AS (VALUES (1),(2),(3)) SELECT p.pid, (SELECT PERCENT_RANK() OVER (PARTITION BY MAX(t.grp) ORDER BY SUM(t.v)) FROM t WHERE t.pid = p.pid) AS pr FROM p ORDER BY p.pid ``` **`recombine`'s own comment states the premise that does not hold.** It says "the window has already collapsed the periods it spans, so like the season render it needs no extra partition: its rows are one per subject again". That is true of the multi-year team-play wrap, which renders inside a grouped query, and false of `select-string.mjs`'s year-offset correlated subquery, which renders one row. Both consume the same `recombine`, so one of the two consumers has always been wrong. **Blast radius is one column in one shape.** The registry holds exactly one percentile column — the two declaration sites at `player-stats-from-plays-column-definitions.mjs:1510` and `:1536` are that column's `measure_shape` and its `resolve_measure`. Only `year_offset` RANGE is affected; the plain and single-offset shapes render in cohort scope or in the period CTE, where `pg` is bound and the window spans the population. The gate tiers the column `catalog_only` — reachable only by hand-picking it in the column browser, so nothing user-facing is serving the zero today. `composite.mjs`'s per-position weights carry the same latent defect through `render_weight`, and no composite is registered, so it has never fired. ### Three candidate repairs, needing a ruling **Project and accept the zero.** Smallest — add `MAX(pg.player_position) as cohort_subject_position` to the CTE projection and repoint both consumers. Gate green, `yarn verify` unblocked today. Ships a column that reads 0.00 for every qualified player in the offset-range shape. Rejected unless the operator wants the chain closed above correctness. **Emit NULL over an offset range.** Declare a percentile measure non-recombinable and have the offset-range path answer NULL, which is this subsystem's established not-measured answer (`guarded_divide` on a zero denominator, and the sub-floor case in `render_percentile` itself). It also matches doctrine already written at `select-string.mjs:281-284`, which names RANKS among the non-additive statistics that must not be silently reduced over a multi-year window. Small, honest, ships no wrong number. Discards a number that is genuinely computable. **Lift the window to the outer select.** The correct end state. The percentile is a function of the combined value, and over an offset range that value IS the correlated subquery — so pass the subquery as `value_sql` to `render_percentile` at the OUTER level, where the query's rows are one per subject and constitute the population. Needs `recombine` split into a value-only form plus a percentile wrapper, `select-string.mjs:278` restructured, the position read through its own correlated subquery over the CTE, and goldens regenerated. Carries a real performance question: `render_percentile` repeats `value_sql` three times and the position twice, so the outer row would drive five correlated scans of a materialized CTE, and the window forces the full population before `LIMIT 500`. Measure before adopting. **Recommended: emit NULL now to unblock the chain, and carry the lift as its own task.** It is the only option that neither ships a wrong number nor puts a shared measure-contract refactor with an unmeasured performance profile on another task's critical path. ## Resolved 2026-09-24 (`repl-ef5faa34`), by lifting the window rather than projecting alone **Operator ruled the accurate option** after the finding above: lift the percentile to the outer select, not project-and-accept-the-zero and not emit NULL. League `92956de53`. `recombine` was doing two jobs — combine the accumulators AND apply the percentile — and handing the fused result to a caller that wraps it in a per-subject correlated subquery. Splitting them removes a conflation rather than adding a concept: `recombine` returns the value, and `recombine_percentile` wraps the subquery the emitter built, at the scope whose rows ARE the population. The subject position is now declared once on the MEASURE as `COHORT_SUBJECT_POSITION`, projected out of the period CTE, and read per scope — `MAX(pg.player_position)` in cohort scope, `MAX(.cohort_subject_position)` in the recombine scope. Both consumers moved together, so the partition key and the qualifier floor cannot resolve a player to two positions. **A second 42P01 sat behind the first.** The position also had to be CORRELATED: out in the outer scope the CTE is not in the FROM clause, so a bare `t.cohort_subject_position` is its own missing-FROM-entry error. The emitter now passes a `correlate` function — it alone knows the key, the year window and the source's extra predicates — and `recombine_percentile` throws rather than emitting a bare reference if that function is absent. `composite.mjs`'s per-position weights carried the identical latent defect through `render_weight` and are fixed in the same change. `declare_composite` has no callers, so it had never fired. ### Verified - **Gate green with the statements PRESENT, not merely absent** — the column reports 6 EXPLAINed statements (2 shapes x 3 row axes), and the full gate reports 4650 across 615 columns, the same count as before the change, so nothing stopped being emitted. This was the task's own stated verification. - **`yarn test:isolated`** 12,257 passing, summary line present. Two failures, one mine and fixed (a composite now projects a 5th CTE column, asserted by NAME as well as by count); the other, `prune-orphan-league-formats`, is a fixture FK teardown on `league_format_player_projection_values_history` whose constraint dates to 2026-07-29 and which no change here touches. - **A demonstrated red.** Re-fusing the percentile into `recombine` turns the new arm red with the rest of the file green, and restoring it turns it back. - **`yarn lint` 0, `yarn check:types` 0, `yarn verify` exit 0 — 20 checks passed.** ### What it did NOT fix The column now exceeds the 30-second statement timeout on execution: five correlated scans per outer row against a 3.0s one-scan baseline, and narrowing to two years does not help. Routed to [[user:task/league/serve-the-offset-range-percentile-in-one-scan.md]] with the measurements and the lateral it needs. It fails loudly rather than answering wrongly, and the column is `catalog_only`.