---
title: Fix week-sort dangling CTE reference and the unmigrated legacy splits URL param
type: task
status: completed
description: >-
  Row-axis sorts emitted a hardcoded player_years_weeks.week whether or not the week axis was
  active, making three production URLs unexecutable, and the pre-rename splits URL param was never
  migrated to row_axes so 188 of 682 shared short URLs silently lost their axes. Both fixed in
  league 924dfe328; the splits half needs a frontend deploy.
base_uri: user:task/league/data-views/fix-week-sort-unreachable-and-legacy-splits-param.md
created_at: '2026-08-05T19:11:23.173Z'
entity_id: 18f55651-c709-4116-9880-b91d0b21b410
finished_at: '2026-08-05T20:05:49.248Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - follows [[user:guideline/write/write-software.md]]
  - follows [[user:guideline/write/write-javascript.md]]
  - relates [[█████████████████████-█████████-████-█████-████-█████████-██████████████]]
  - relates [[█████████████████████-████████████-██████-██-███-███████]]
started_at: '2026-08-05T19:54:03.158Z'
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-08-05T21:39:16.924Z'
visibility_analyzed_at: '2026-11-05T23:48:52.425Z'
---

# Sorting on week breaks legacy short URLs

## Problem

Three of the 100 most recent production data-view URLs fail to execute. All three sort on `week` and all three emit `order by player_years_weeks.week` while never joining `player_years_weeks`, so Postgres rejects the whole statement:

```
missing FROM-clause entry for table "player_years_weeks"
```

Found 2026-08-05 by the production-URL replay gate at league `a18ef7459`. SQL generation succeeded for all 100 URLs; these three fail at EXPLAIN, which means they fail for real users too. The failing hashes are recorded in the observations below.

This is the sort-path sibling of [[█████████████████████-█████████-████-█████-████-█████████-██████████████]] (Completed), which fixed the same unreachable-CTE shape in source joins rather than in `ORDER BY`.

## Two coupled defects

**1. The sort resolver emits a week reference without ensuring the week bridge fired.** A `sort` entry of `{column_id: 'week'}` resolves to `player_years_weeks.week` unconditionally. The `player_year` to `player_year_week` bridge only fires when the `week` row axis is active, so with no week axis the reference is unreachable. One of the three failing URLs carries no axis declaration at all, so this defect stands on its own.

**2. Legacy short URLs carrying `splits` are never migrated to `row_axes`.** Two of the three failing URLs carry `splits=["year","week"]`, the pre-rename spelling. `app/core/data-views/parse-table-state-from-url.mjs:17` reads `row_axes` only, and `libs-shared/data-views-saved-view-migration.mjs` has no `splits` rule — grep for `splits` in either file returns nothing. So a URL saved before the rename silently loses both row axes, which is what puts it into the state defect 1 then fails on.

Defect 2 is the wider one. It is not confined to sorting: any legacy short URL with `splits` renders at the wrong grain today, silently and without an error. The three EXPLAIN failures are only the subset where the lost axis also broke the SQL.

## Plan

- [x] **Measure the blast radius of the lost `splits` param**
  - 188 of 682 production data-view URLs carry a non-empty `splits`; another 121 carry `splits=[]`. Only 21 carry `row_axes`, and **zero** carry both — so the legacy spelling dominates shared short URLs and the alias is unambiguous.
  - Saved views are clean: of 186 rows in `user_data_views`, 42 carry `row_axes` and none carry `splits`. The migrator rule is defensive only; the URL path held all the live damage.

- [x] **Migrate `splits` to `row_axes` on both read paths**
  - `app/core/data-views/parse-table-state-from-url.mjs` reads `splits` when `row_axes` is empty. The schema-driven parser fabricates `[]` for an absent key, so empty and missing are indistinguishable — both defer to `splits`, which is safe given no URL carries a non-empty pair.
  - `libs-shared/data-views-saved-view-migration.mjs` gained a `splits` rule in `migrate_table_state`, mirroring the existing `subjects` → `row_grain` rule.
  - `scripts/capture-data-view-replay.mjs` applies the same alias, so the replay stays faithful to what a user gets.

- [x] **Make the sort resolver honor bridge reachability**
  - The `is_split` branch of the sort loop in `libs-server/get-data-view-results.mjs` now takes the reference from `data_view_options.year_reference` / `week_reference` and skips the sort when the axis is not in `row_axes` — the same fallback an unresolvable column sort already takes.
  - `find_sort_column` no longer returns a physical table name for an axis. The literal was unreachable without the axis and wrong at team grain, where the axes live in `team_years` / `team_years_weeks`.

- [x] **Cover it with a spec, proven red first**
  - `test/data-views.row-axis-sort-reachability.spec.mjs`, 8 tests: axis sort with no axis emits no reference and executes, axis sort with the axis orders correctly, team grain resolves to its own CTE, and three migrator cases.
  - Proven **7 of 8 red** in a control worktree at `4e14bc44d`, including the exact production error. The one passing test is the already-correct case, which confirms the fix did not move it.

## Outcome

Shipped in `924dfe328`. All three failing URLs now EXPLAIN cleanly, and the 100-URL replay is 0 failures against 3 before. Full suite 3240 passing, 0 failing; `check-data-view-sql-validity` GATE OK across 4488 statements; test-collection gate OK.

**Not yet deployed.** Both halves of the `splits` fix run client-side, so no user is helped until `yarn build && yarn deploy:dist && yarn deploy:sourcemaps` (see league CLAUDE.md). The sort fix is server-side and ships with `yarn deploy`. Deploy is human-gated and was left to the operator.

## Notes

- Surfaced by the replay gate of [[█████████████████████-█████████████-██-████████-████████]] and carried out of it — the fix is dispatcher sort resolution plus URL compat, not remaining migration scope.
- Both defects predate the identity migration's final units; neither was introduced by it.
- `db/adhoc/check-data-view-sql-validity.mjs` cannot catch this class: it sweeps columns across grains and axes but never exercises `sort`, and it reported `GATE OK` on the same tree where these three URLs failed.